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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.