Problems with linked FoxPro tables in Access 2000

[MyndPhlyp]

(also posted over at ms.pub.access.externaldata)

I'm using Access 2000 and going after the linked 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. It doesn't matter if I check or uncheck the
"Exclusive", "Deleted" or "Fetch data in background" options in the ODBC DSN
configuration.

A typical scenario would be:

Dim conLocal as New ADODB.Connection
Dim rs As New ADODB.Recordset
Set conLocal = CurrentProject.Connection
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

And just to make matters worse, I cannot seem to trap the actual value of
..Status into a variable or use in in a comparison. For example:

If rs.Status = ADODB.adRecOK Then '.. always test true
varMyStatus = rs.Status '.. always results in varMyStatus = 0

But by using breakpoints or waiting for the code to fall apart and entering
Debug, I know the status of the supposedly deleted records is 262144
(ADODB.adRecDBDeleted) by running the mouse cursor over the rs.Status
command.

What's the trick to really deleting records in a linked FoxPro table using
ADO in Access 2000? And for bonus trivia points and a chance to go on to our
expert round, 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)?

 

[Albert D. Kallal]

The first thing I would try is can you edit/delete records in the table
view?

In other words, try deleting a few records by directly using the table (no
code).

And for bonus trivia points and a chance to go on to our
expert round, 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)?

Just like ms-access, dbaseII, III etc, and FoxPro. You need to do a pack (in
dbase) to actually delete the records and recover the disk space.

In ms-access, you also have to do a compact to get that disk space back. So,
a good portion of those pc database systems requires a periodic maintains
(pack in dBase/fox...and compact in ms-access).

However, when you delete a record in the linked Fox table, the ONLY thing
done is the deleted flag is set. In theory, you thus could open the fox
table with FoxPro..and still see those deleted records (they would have the
deleted flag set). Ms-access does work much the same...but we can't see, or
get at the "deleted" flag, and the actual data that is still there...but
un-accessible.

Anyway...back to the first question:

Can you delete records via the ms-access interface in that linked fox table?

I would try the above first..

 

[MyndPhlyp]

The first thing I would try is can you edit/delete records in the table
view?

In other words, try deleting a few records by directly using the table (no
code).

Ooo ... there's a clue. Attempting to manually delete a record in Access
from the table results in "Record not deleted. Data is read-only."

Okay, so how to I get this linked FoxPro table writeable ... er, deletable
.... uh, you mean what I know?

(Of course, this puzzles me even more because the "blank record" at the end
of the rows isn't present but I've been able to add records programmatically
to the table. Sort of a quasi-read-only, I guess)

 

[Albert D. Kallal]

I believe when you do the linking process..you are asked for a primary key.
If you don't choose a field, or some means to uniquely identify the
record..then that table is read only.

Do you have some keyid in the FoxData you can use when you link?

 

[MyndPhlyp]

I believe when you do the linking process..you are asked for a primary key.
If you don't choose a field, or some means to uniquely identify the
record..then that table is read only.

Do you have some keyid in the FoxData you can use when you link?

I actually have to select 2 columns to uniquely identify a row in both
tables I am working with. There is an odd side effect if the row is not
unique.

Yeppers, did that first time through but it doesn't seem to have stuck.

Went back, deleted the linked table, re-added it with the same 2 columns
used to identify the row, and the majik "blank" record at the end of the
list appears - I can delete manually.

But if I run my code, exit and restart Access, the tables are back to
read-only ... sometimes. This is looking like one of those intermittent
problems programmers just love to debug. <g>

What would cause a linked table to become read-only in a successive session
when it was write enabled before? (Yeah, I know it's an open-ended question,
but maybe there are only a few possibilities.)

 

[MyndPhlyp]

I believe when you do the linking process..you are asked for a primary key.
If you don't choose a field, or some means to uniquely identify the
record..then that table is read only.

Do you have some keyid in the FoxData you can use when you link?

Wonders of wonders - things are beginning to fall into place rather than
just fall apart. <g>

I went through and relinked all the tables (with unique row identifiers
selected) and it seems to be sticking. I have no idea why I lost the write
enable on any of the tables.

I also figured out why I couldn't capture rs.Status - it helps sometimes to
not be too specific about variable declarations.

Dim varStatus
varStatus = rs.Status

Works like a champ.

Oh, and error handling traps work wonders for debugging too! <G> I guess
I'll just have to go back to the old school method of programming and not
trust anything. (Like I should have ever left in the first place.)

One last thing you might be able to help with ...

.... Somewhere along the line I want to programmaticly execute a PACK
command. The DAO method SQLExec was replaced by the ADO method Execute, but
ADO's Execute doesn't like to process anything other than DELETE, INSERT,
PROCEDURE, SELECT and UPDATE. (Gee, they're standard SQL commands. Who would
ever figure?)

I need to somehow get FoxPro to execute the following:

SET EXCLUSIVE ON
PACK MYTABLE

(The two commands could be put on a single line with a semicolon delimiter.)

Is there a way to programmaticly do the DAO equivalent of:

cnLocal.Execute("SET EXCLUSIVE ON; PACK MYTABLE")

in ADO?

 

[Albert D. Kallal]

Wonders of wonders - things are beginning to fall into place rather than
just fall apart. <g>

I think the real thing is also to test editing the data manually...at least
once you got that working..then you could go back to working on the code.
Anyway..good stuff!

... Somewhere along the line I want to programmaticly execute a PACK
command. The DAO method SQLExec was replaced by the ADO method Execute, but
ADO's Execute doesn't like to process anything other than DELETE, INSERT,
PROCEDURE, SELECT and UPDATE. (Gee, they're standard SQL commands. Who would
ever figure?)
Is there a way to programmaticly do the DAO equivalent of:
cnLocal.Execute("SET EXCLUSIVE ON; PACK MYTABLE")
in ADO?

The following article does show that your above command is the right idea.
You can see that in this kb article:

http://support.microsoft.com/?kbid=234756

I don't know off the top of my head how to get the above to work in ado
Perhaps you add a pass-through pram to the above?

 

[MyndPhlyp]

The following article does show that your above command is the right idea.
You can see that in this kb article:

http://support.microsoft.com/?kbid=234756

I don't know off the top of my head how to get the above to work in ado
Perhaps you add a pass-through pram to the above?

Yeppers, that was the KB article I referred to.

I tried a few Options on the Connection.Execute with no luck.

varReturn = cn.Execute("SET EXCLUSIVE ON; PACK MYTABLE", ,
ADODB.adExecuteNoRecords + ADODB.adCmdUnknown)
varReturn = cn.Execute("SET EXCLUSIVE ON; PACK MYTABLE", ,
ADODB.adExecuteNoRecords + ADODB.adCmdText)

Both returned (from my ADO error trap):

Error number: -2147217900
Description : Invalid SQL statement; expected 'DELETE', 'INSERT',
'PROCEDURE', 'SELECT', or 'UPDATE'.
Source : Microsoft JET Database Engine
SQL State : 3000
Native Error: -533138860

Also tried:

varReturn = cn.Execute("SET EXCLUSIVE ON; PACK MYTABLE", ,
ADODB.adExecuteNoRecords + ADODB.adCmdStoredProc)

And got (from my ADO error trap):

Error number: -2147217900
Description : Expected query name after EXECUTE.
Source : Microsoft JET Database Engine
SQL State : 3732
Native Error: -230690241

Is there some other way of doing a pass-through command to the Connection?

 

[MyndPhlyp]

I believe when you do the linking process..you are asked for a primary key.
If you don't choose a field, or some means to uniquely identify the
record..then that table is read only.

Do you have some keyid in the FoxData you can use when you link?

Alan:

Thought you'd like to know I figured out how my linked FoxPro tables went
from write enabled to read only.

Part of my troubleshooting involved playing around with the ODBC DSN
settings. When I changed the "Database type" from "Visual FoxPro database
(.DBC)" to "Free Table directory" and re-opened Access, used the Link
Manager to verify the links, that's when they all got switched around to
read only. Changing the ODBC DSN back to "Visual FoxPro database (.DBC)" and
re-verifying the links does not change things back to write enabled - the
only workaround is to manually (or presumably programmatically) relink.