Import of DBF files and filename macro?

[Equine Investor]

Hi,

I have a question regarding import of multiple dbf files.

These files all have different names but will always be found in the same location.

C:\Documents and Settings\computer\Desktop\RATINGS\F

I have no VBA knowledge and am wondering if I can import ALL these files into an existing Access table FORMGUIDE via a macro?

It gets a little complicated as I'd like to also be able to add the first 7 characters of the filename (the name of the dbf file) to a field KEY in the table FORMGUIDE.

Any help or examples would be greatly appreciated.

Thanks.

 

[Norie]

I'm sure this is possible.

The first thing I would do is create a macro that imports one of your dbf files into Access.

Then convert that macro into VBA code.

That should give you the code to do it for one file and that could then be adapted to deal with multilpe files in the same directory.

I imagine it would involve using the FileSearch object and some kind of loop.

 

[Equine Investor]

O.K.

This is what I have so far to import one file:

Option Compare Database

'------------------------------------------------------------
' Import
'
'------------------------------------------------------------
Function Import()
On Error GoTo Import_Err

DoCmd.TransferDatabase acImport, "dBase 5.0", "C:\Documents and Settings\computer\Desktop\F FILES\", acTable, "ASC0128F.DBF", "tblFORMGUIDE", False


Import_Exit:
Exit Function

Import_Err:
MsgBox Error$
Resume Import_Exit

End Function


A number of problems:

1. It does not import directly into the existing table, instead it creates a new table tblFORMGUIDE1, I'd like to import directly into the existing table without using append or similar.

2. I cannot get it to import ALL file in the folder.

3. I'd like to import the first 7 characters of the filename into a field (KEY).

I've already read the Knowledgebase and found the Batch Import, but this does not help as you have to manually enter the name of each file and I have over 2,500 individual dbf files.

Any ideas?

 

[Equine Investor]

I've solved this elsewhere, thankyou.

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:\My Documents\"
   
   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 [tblFORMGUIDE]" _
           & " 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."
   Exit Sub
   
ErrHandler:
   MsgBox Err.Description
End Sub