Import Fox Pro Table Into Access using VB

[Terpsfan]

Within an Visual Basic I want to be able to import a Fox Pro .dbf into an Access database. Does anyone have a code sample on this or any suggestions? Thanks...

 

[iejerry]

Is this a one time affair? If so, I think Access can link to FoxPro tables.

 

[Terpsfan]

I know Access can link to a Fox Pro table but I have to do it within Visual Basic code. The data from the Fox Pro table has to be imported into an Access table. It has to be done on a daily basis.

 

[challer]

I have a VB program where I read from a FoxPro table and insert the data into a SQL Server db.

First, you need to create a foxpro data source on the machine running the app. That's what the <DataSourceName> is in the strConString variable. I made my datasource look in the root directory, that allows me to open any FoxPro table on that machine.

' Set a connection string
strConString = &quot;Provider=MSDASQL.1;Persist Security Info=False;Data Source=<DataSourceName>&quot;

' Create a recordset from the FoxPro data
Private Function OpenDBFile(strDBName As String) As ADODB.Recordset

Dim rs As New ADODB.Recordset

stbStatusBar.Panels(1).Text = &quot;Opening &quot; & strDBName

' cn is global in the module
If cn.State <> adStateOpen Then cn.Open strConString

rs.CursorLocation = adUseClient
rs.Open strDBName, cn, adOpenForwardOnly, adLockReadOnly, adCmdTable

Set rs.ActiveConnection = Nothing

cn.Close
Set OpenDBFile = rs

stbStatusBar.Panels(1).Text = &quot;&quot;

End Function

Call the above function with the path and name of the FoxPro table and it returns a recordset.

You can create another connection object to your Access data similar to above method to update you data.

Hope this helps,
Clay Haller