Office 2003 SP1, now VB doesn't run properly?

[Equine Investor]

Hi,

I have some VBA code that imports multiple DBF files into my database.

Here is the code:

Private Sub cmdImport_Click()
On Error GoTo ErrHandler

Dim oFSystem As Object
Dim oFolder As Object
Dim oFile As Object
Dim sFolderPath As String
Dim SQL As String
Dim i As Integer

sFolderPath = "C:\Documents and Settings\computer\Desktop\F FILES\"

Set oFSystem = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSystem.GetFolder(sFolderPath)

For Each oFile In oFolder.Files
If Right(oFile.Name, 4) = ".dbf" Then
SQL = "Insert into [tblALLRUNS]" _
& " Select """ & Left(oFile.Name, 7) & """ as [KEY],*" _
& " from " & Left(oFile.Name, Len(oFile.Name) - 4) _
& " IN """ & sFolderPath & """ ""dBASE 5.0;"""

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
i = i + 1
End If
Next

MsgBox i & " dbf files were imported successfully!"
Exit Sub

ErrHandler:
MsgBox Err.Description
End Sub

This has all been working fine, BUT I recently upgraded to Office 2003 SP1 and now the code does not import all the data within the files (missing records not fields) and skips some DBF files.

I don't know whether this is because of the new version or some other reason, bit of a coincidence that it happened just now.

Can anyone suggest anything or experienced similar - I'm in real trouble!

 

[Joe4]

I have had VB problems in upgrading, and I often found it was because I was missing necessary VB libraries.

In the VB editor, go to Tools | References and see if there are any Available References marked MISSING.

The easiest way to determine which ones you need is to locate a computer on which the database works, view the References and maked sure those same references are selected on your new computer (might be a different version number, but should be same reference, i.e. Microsoft Access 9.0 Object Library on old and Microsoft Access 10.0 Object Library on new).

 

[Equine Investor]

Thanks for the reply, the problem was with a primary key "locking out" extra records.

I appreciate your time to assist.