Are there limitations in writing to a Foxpro database with Access 2007?

Question

I have a foxpro database that we are trying to write to using MS Access 2007 via a VFP ODBC driver (The driver is version 6.x)

Are there limitations in doing this? No matter what we try we are not able to write to the foxpro data tables.

We are using ODBC
It is the MS VFP driver
The Foxpro and access DBs are on the same system
The ODBC is setup for Free Table Directory
Permissions on the foxpro directory and files has been checked.

We are not getting any specific error but we do not have the option to create a new entry in Access on the FP table and we cannot run a query that inserts data from Access to the FP tables.

Any help would be great

Here's the code:

Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim strConnString As String
Set oConn = CreateObject("ADODB.Connection")
strConnString = "Data Source= C:\Program Files\Best Software\Abra Suite\Programs\Data\hrtables.dbf;User ID = ;Password = ; Provider=VFPOLEDB"
oConn.Open strConnString

Set dbs = CurrentDb
strSQL = "Select * from qryAppendClient"
Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Do While Not rsSQL.EOF
strSQL2 = "Insert into hrtables  (c1, c2, c3, c4, Code, Company, Desc, GLComp, n1, n2, n3, Rule, "
strSQL2 = strSQL2 & "Table, tccomp, ud1, ud2, ud3) values (""" & rsSQL!c1 & """, """  &       rsSQL!c2 & """, """ & rsSQL!c3
strSQL2 = strSQL2 & """, """ & rsSQL!c3 & """, """ & rsSQL!Code & """, """ & rsSQL!Company & """, """ & rsSQL!Desc & """, """
strSQL2 = strSQL2 & rsSQL!GLComp & """, " & rsSQL!n1 & ", " & rsSQL!n2 & ", " & rsSQL!n3 & ", """
strSQL2 = strSQL2 & rsSQL!Rule & """, """ & rsSQL!Table & """, """ & rsSQL!tccomp & """, """ & rsSQL!ud1 & """, """
strSQL2 = strSQL2 & rsSQL!ud2 & """, """ & rsSQL!ud3 & """)"
oConn.Execute strSQL2

Loop

oConn.Close
rsSQL.Close

Answer 1

I'm not familiar with dbOpenSnapShot and a quick check of MSDN does not show it as an option for Foxpro. If you check your cursortype after opening the recordset I suspect it will be the default type. I'm a big fan of explicitly setting properties. You might try this on your connection.

  oConn.CursorLocation = adUseClient
  oConn.Mode = adModeReadWrite

For your OpenRecordset call try adOpenStatic. I believe that will give you an updatable cursor. Also some combinations of CursorLocation, Mode, and CursorTypes are not supported. For your development I suggest checking your CursorType immediately after opening to see if you got what you expected.

  Debug.Print rsSQL.CursorType

Answer 2

My expertise is Visual FoxPro, not Access, but I have worked with Access to update VFP data and vice-versa. There are two things to consider with respect to opening up a Visual FoxPro database:

1) Version of the database (FoxPro 2.6, Visual FoxPro 6.0 and earlier, or Visual FoxPro 7.0 or later) is important. In the beginning of your post you mention the VFP 6.0 ODBC driver which does open VFP 6 database contained tables, free tables, and the older FoxPro 2.6 tables fine. In your code you are referring to the VFP OLE DB driver which opens any VFP database contained tables, free tables, and the older FoxPro 2.6 tables. The difference is ODBC vs. OLE DB.

Just make sure you are not confusing the two and their capabilities.

2) In my experience I have found the easiest way to get to VFP data is through a Linked Connection within MS Access. I have found other approaches (and don't ask me what they were since it was a long time ago) just did not work.

Rick Schummer VFP MVP