Problem with linked FoxPro tables in Access 2000

[MyndPhlyp]

I'm using Access 2000 and going after the tables with ADO. The FoxPro tables
are accessed through ODBC (Microsoft Visual FoxPro Driver v6.01.8630.01)
using database type "Visual FoxPro database (.DBC)" with the Null and
Deleted driver options unchecked. If I can trust the comments in the
Database Properties in Visual FoxPro, the database is vintage 3.50 SP1.

I can .AddNew/.Update to my heart's content, but I can't seem to
..Delete/.Update records.

A typical scenario would be:

Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "mytable", conLocal, adOpenDynamic, adLockPessimistic,
adCmdTableDirect
rs.Filter = "mystring = '12345'"
Do Until rs.EOF
rs.Delete
rs.Update
rs.MoveNext
Loop

Pretty straightforward stuff, but if I go back and look at the table through
either Access or FoxPro, the records are still there. (And, in FoxPro, none
of the records are flagged as deleted.)

It gets better ...

Successive runs of the same code fall apart on the .Delete because the
record is flagged for deletion (even though the ODBC driver has been
configured to not deliver deleted records and the adLockPessimistic
supposedly puts me into "immediate mode"). So I end up wrapping the .Delete
and .Update with:

If rs.Status Then
rs.Delete
rs.Update
End If
rs.MoveNext

What's the trick to really deleting records in a linked FoxPro table using
ADO in Access 2000? And when the records /*are*/ finally deleted, do I still
have to go back to FoxPro somewhere along the line and do a PACK on the
table(s)?

 

[Cindy Winegarden]

In news: (e-mail address removed),

...ODBC (Microsoft Visual FoxPro Driver
v6.01.8630.01) using database type "Visual FoxPro database (.DBC)"
with the Null and Deleted driver options unchecked.
Pretty straightforward stuff, but if I go back and look at the table
through either Access or FoxPro, the records are still there. (And,
in FoxPro, none of the records are flagged as deleted.).....

Successive runs of the same code fall apart on the .Delete because the
record is flagged for deletion (even though the ODBC driver has been
configured to not deliver deleted records ....
What's the trick to really deleting records in a linked FoxPro table
using ADO in Access 2000? And when the records /*are*/ finally
deleted, do I still have to go back to FoxPro somewhere along the
line and do a PACK on the table(s)?

Hi MyndPhlyp,

It's somewhat counterintuitive but checking (not clearing) the Deleted box
in the ODBC setup hides deleted records. Checking the box corresponds to the
VFP Set Deleted On command.

You can pack VFP tables using the method in the following KB article:
HOWTO: Pack a Table Through the Visual FoxPro ODBC Driver (VFPODBC.dll)
http://support.microsoft.com/?kbid=234756

However, there's really no need to pack tables unless you have huge numbers
of deleted records (a table size issue) or you are trying to re-use primary
keys, which of course is not recommended.

I haven't had time to digest the rest of your post but will come back to it,
and maybe someone else will have something to add.

 

[MyndPhlyp]

I looked over the KB article - it uses the old SQLExec method from DAO that
was replaced by the Execute method in ADO. (It also doesn't use
CurrentProject.Connection but rather a hardcoded connection string, which I
am trying to avoid.) I went ahead and tried using:

Dim con As New ADODB.Connection
Set con = CurrentProject.Connection
con.Execute("SET EXCLUSIVE ON;PACK MYTABLE")

It is essentially what the example code was illustrating, but I receive the
error message:

Run-Time error '-2147217900 (80040e14)':
Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.

It doesn't seem to matter whether I have the Deleted option checked or
unchecked in the ODBC DSN - after going through the process of
..Delete/.Update./MoveNext, the records are still visible in Access and in
FoxPro and manually following up the process with a PACK in FoxPro doesn't
clean up anything (indicating there were no records flagged as deleted).

The problem seems to be the way I'm doing the .Delete/.Update./MoveNext.
Either the Delete isn't deleting or the .Update isn't updating.

In news: (e-mail address removed),


Hi MyndPhlyp,

It's somewhat counterintuitive but checking (not clearing) the Deleted box
in the ODBC setup hides deleted records. Checking the box corresponds to the
VFP Set Deleted On command.

You can pack VFP tables using the method in the following KB article:
HOWTO: Pack a Table Through the Visual FoxPro ODBC Driver (VFPODBC.dll)
http://support.microsoft.com/?kbid=234756

However, there's really no need to pack tables unless you have huge numbers
of deleted records (a table size issue) or you are trying to re-use primary
keys, which of course is not recommended.

I haven't had time to digest the rest of your post but will come back to it,
and maybe someone else will have something to add.

I looked over the KB article - it uses the old SQLExec method from DAO that
was replaced by the Execute method in ADO. It also doesn't use
CurrentProject.Connection but rather a hardcoded connection string, which I
am trying to avoid. I went ahead and tried using:

Dim con As New ADODB.Connection
Set con = CurrentProject.Connection
con.Execute("SET EXCLUSIVE ON;PACK MYTABLE")

It is essentially what the example code was illustrating, but I receive the
error message:

Run-Time error '-2147217900 (80040e14)':
Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.

Yes, packing the database will be an issue eventually since I am in
development mode and trying to integrate data from one database into
another. Keys are going to be re-added several times until I get it right.
The target schema doesn't seem to mind duplicated keys. (Poor design, to be
sure.)

It doesn't seem to matter whether I have the "Deleted" or the "Fetch data in
background" or the "Exclusive" options checked or unchecked in the ODBC
DSN - after going through the process of .Delete/.Update./MoveNext, the
records are still visible in Access and in FoxPro and manually following up
the process with a PACK in FoxPro doesn't clean up anything (indicating
there were no records flagged as deleted).

A second itteration of my VBA code does seem to indicate the records are
marked as deleted - if I check the .Status on the Recordset at the current
record, it has a value = 262144 (ADODB.adRecDBDeleted).

The problem seems to be the way I'm doing the .Delete/.Update./MoveNext.
Either the Delete isn't deleting or the .Update isn't updating.

There also seems to be a problem trapping the .Status value. I've tried
several things along the line of:

Dim varStatus
varStatus = rs.Status

Whenever I check the value of varStatus, I get zero (ADODB.adRecOK). But if
I put in a breakpoint or let the code fail and go into Debug hovering the
mouse cursor over the code "rs.Status", I get the true status value of
262144. In an "If" statement, if I try to do something like:

If rs.Status = ADODB.adRecOK Then ...

It always passes as true regardless of the .Status value.

If, however, I do something like:

If rs.Status Then ...

It catches any non-zero value.

If I can get .Delete/.Update to actually rid me of the records, I can live
with the .Status. The problem with .Delete/.Update is my primary concern.

Any shove in the right direction will be appreciated.