Import/Lookup data in a dbf file using an adp file linked to MSSQL

I need to get information from a dbf file and use this information in my database.

I have tried DoCmd.TransferDatabase acImport, "dBase III", strFilePath, acTable, strDBFFilename, strNewTableName, False

This works sometimes but not in all occassions.

I have tried linking these tables manually in mdb databse which works fine for all dbf files.

If there a way I can write some vba code in a adp project to read the dbf file in the same way a linked table works - perhaps by creating a connection to the dbf file?.
donhannamAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You can build an ADO connection to the .dbf file:

Dim con As ADODB.Connection
Dim rst AS ADODB.Recordset

SEt con = New ADODB.Connection
con.Open "Your connect string"
Set rst = New ADODB.Recordset
rst.OPen "SELECT * FROM SomeTable", con

Do until rst.EOF
  <do something here>
  rst.MoveNext
Loop

This assumes that you have a reference to the ADO library in your database.

See here for connect string info:
http://www.carlprothman.net/Default.aspx?tabid=81

0
 
donhannamAuthor Commented:
Thanks for this - works fine except when there is a space in the file name:-
 
I have used:-
 
      Dim con As ADODB.Connection
      Set con = New ADODB.Connection
      con.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\DBF Files\"

        Dim rst As ADODB.Recordset
        Dim strDBFSQL As String
       
        strDBFFile = "test 1.dbf"
        Set rst = New ADODB.Recordset
        strDBFSQL = "SELECT * FROM " & strDBFFile
        rst.Open strDBFSQL, con
   
        Do Until rst.EOF
            MsgBox (rst![NAME])
           rst.MoveNext
         Loop
       
  Is there a way in the select statement to get around the spaces in the file names?.
   
0
 
donhannamAuthor Commented:
I have done some further work on this and looks like also does not like long file names - more than 6 char.

I have got around this by copying the dbf file to a standard name that no one will use and then importing this and deleting the copy so can use name again.

      Dim con As ADODB.Connection
      Set con = New ADODB.Connection
      con.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\DBF Files\"

        Dim rst As ADODB.Recordset
        Dim strDBFSQL As String
       
        strNewName = "Opx987.dbf"
        strcopyFrom = strFilePath & strDBFFile
        strcopyTo = strFilePath & strNewName
           
        FileCopy strcopyFrom, strcopyTo

        Set rst = New ADODB.Recordset
        strDBFSQL = "SELECT * FROM Opx987.dbf"
        rst.Open strDBFSQL, con
   
        Do Until rst.EOF
            MsgBox (rst![NAME])
           rst.MoveNext
         Loop
       
        Kill strcopyTo
0
 
donhannamAuthor Commented:
Refer comments thanks
0
All Courses

From novice to tech pro — start learning today.