Pass-through command to FoxPro database

[MyndPhlyp]

Using Access 2000, ADO (MDAC v2.8) and the Microsoft Visual FoxPro ODBC
driver (v6 something), I'd like to issue a command to the FoxPro database to
pack various tables.

Under DAO, this would be done using the Command.SQLExecute method.

Under ADO, this has been somewhat replaced by the Command.Execute method.

The problem is, no matter how I arrange the options, I can't get past Jet.

For example:

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

or

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

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 another way of doing this through Connection (or any other method)
using ADO?

 

[Albert D. Kallal]

One more thing to try:

Open the table exclusive with your connection string..and then send ONE
command to the driver:

cn.Execute ("pack mytable")

Check out:

http://www.visualbasicforum.com/archive/index.php/t-13488

I did not see what connecting string you are using..but it seems that you
can't execute "two" commands. So, you need to do each command separately
(or, you can ONLY execute one command anyway...and that should be the
pack...).

 

[rich]

Not knowing much about FoxPro, but are you attempting to flag the
records before running a DELETE (or DML)??

Rich

 

[MyndPhlyp]

Not knowing much about FoxPro, but are you attempting to flag the
records before running a DELETE (or DML)??

No. The records have already been deleted via Recordset.Delete (and
..Update). To clean things up and actually get rid of the so-called deleted
records, you have to PACK the table.

The problem I seem to be having is getting a FoxPro command to pass through
Jet. The ADO Command.Execute seems to want only SQL statements or the name
of a stored procedure.

 

[MyndPhlyp]

One more thing to try:

Open the table exclusive with your connection string..and then send ONE
command to the driver:

cn.Execute ("pack mytable")

Check out:

http://www.visualbasicforum.com/archive/index.php/t-13488

I did not see what connecting string you are using..but it seems that you
can't execute "two" commands. So, you need to do each command separately
(or, you can ONLY execute one command anyway...and that should be the
pack...).

I see a couple of things in that code.

* His SQL string ends with a semicolon
* His Connection.Execute has no parameters other than the SQL string
* He is going against the individual DBF file rather than the DBC file

I have the ODBC DSN set up to go after the DBC file that encompasses all the
tables. The connection I am using is

Dim cn As New ADODB.Connection
Set cn = CurrentProject.Connection

It's taking everything from the ODBC DSN definition. There is probably a
Properties somewhere in either the Connection or a Recordset that will allow
me to determine the path. From there, I suppose could kluge a hand tooled
connection string, but this is going to take a little while.

It might just be that Jet is going to provide too much border guard
checking.