asked on

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?.

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

donhannam

ASKER

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?.

donhannam

ASKER

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

donhannam

ASKER

Refer comments thanks