FoxPro database connector - Call PRG from VB with ODBC

Author

Call PRG from VB with ODBC

Ken D

2005-09-14, 8:25 pm

I am using FoxPro 6 and I have written a program to PACK tables that have
records marked for deletion. I want the PRG file to run at the startup of
foxpro, and I want to know if connecting to a foxpro database via ODBC and
ADO connection string will make foxpro run the PRG, or will I need to call
the PRG explicitly.

I am running VB 6.O, FoxPro 6.0 on Windows XP.

Cindy Winegarden

2005-09-14, 8:25 pm

Hi Ken,

You haven't said whether your data is "free" tables or part of a DBC.

Accessing data is different from "starting FoxPro." You can copy the data to
a computer where FoxPro is not installed and access the data via ODBC
without starting FoxPro or running any programs/executables.

If you're using a Database Container (DBC) you can run code automatically
when accessing data by putting it in a stored procedure and calling it by an
insert/update/delete trigger. You can't do this in VFP6 DBC's by merely
opening the database.

Later versions of FoxPro have "database events" where you can write code to
run when a table is opened but these tables and DBCs are not compaible with
ODBC - you'd need to use OLE DB.

Did you know that you can pack a table from VB6? The following VB6 code
worked for me for the file C:\TestPack.dbf.

'-- ----------------------------------------
Public Sub Main()

' First delete all the records
Set conn1 = New ADODB.Connection
conn1.Open _
"Provider=VfpOleDB.1;" & _
"Data Source=C:\;"

Set cmd1 = New ADODB.Command
cmd1.CommandType = adCmdText
cmd1.ActiveConnection = conn1
cmd1.CommandText = "Delete From TestPack"
cmd1.Execute

conn1.Close
Set conn1 = Nothing

' Now Pack the table to shrink its size
Set conn1 = New ADODB.Connection
conn1.Open _
"Provider=VfpOleDB.1;" & _
"Data Source=C:\;"

Set cmd1 = New ADODB.Command
cmd1.CommandType = adCmdText
Set cmd1.ActiveConnection = conn1
cmd1.CommandText = "Set Exclusive On"
cmd1.Execute
cmd1.CommandText = "Pack TestPack.dbf"
cmd1.Execute
conn1.Close

End Sub
'-- ----------------------------------------

Finally, why do you feel you need to pack the Fox tables, especially every
time an app is run? Unless the tables are extremely large, most Fox
developers just leave the deleted records in the tables, or at best do
housekeeping during off hours like over the weekend.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"Ken D" <KenD@discussions.microsoft.com> wrote in message
news:0A8C5290-9D68-454C-8D11- F98102318244@microso
ft.com...
>I am using FoxPro 6 and I have written a program to PACK tables that have
> records marked for deletion. I want the PRG file to run at the startup of
> foxpro, and I want to know if connecting to a foxpro database via ODBC and
> ADO connection string will make foxpro run the PRG, or will I need to call
> the PRG explicitly.
>
> I am running VB 6.O, FoxPro 6.0 on Windows XP.

Ken D

2005-09-14, 8:25 pm

Hi Cindy,

I am using a DBC for my data source. The reason I need to PACK the db is
that I am developing a standalone app that will be distributed to many users,
all with their own set of data. I will not be managing the data and I need
the records to be deleted when the user wants them deleted. I tried
executing the PACK command from VB, but it kept throwing errors. I got a
"File is in use" error and a "Syntax error or access violation" error. I
tried your code, but it said the provider was unknown. I do not know if I
have the OLE DB provider for FoxPro 6 on my computer. Is there somewhere I
can download it? I found ones for FP 8 & 9, but not for 6. Are they the
same, or is there one just for 6 somewhere?

Anyway, because VB was giving me errors, I wrote a program (PRG) that packed
the DB and hoped to have it run everytime FP started. However, with the
information you provided, it looks like that won't happen. Are there other
ways to PACK a DB either in VB or through some sort of stored procedure in FP?

Thanks bunches

Cindy Winegarden

2005-09-14, 8:25 pm

Hi Ken,

You can download the latest FoxPro and Visual FoxPro OLE DB data provider
from msdn.microsoft.com/vfoxpro/downloads/updates. It works with all
versions of FoxPro tables.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"Ken D" <KenD@discussions.microsoft.com> wrote in message
news:2F6B02BA-3AAB-4194-A56E- 4BDB0293F84D@microso
ft.com...
> Hi Cindy,
>
> I am using a DBC for my data source. The reason I need to PACK the db is
> that I am developing a standalone app that will be distributed to many
> users,
> all with their own set of data. I will not be managing the data and I
> need
> the records to be deleted when the user wants them deleted. I tried
> executing the PACK command from VB, but it kept throwing errors. I got a
> "File is in use" error and a "Syntax error or access violation" error. I
> tried your code, but it said the provider was unknown. I do not know if I
> have the OLE DB provider for FoxPro 6 on my computer. Is there somewhere
> I
> can download it? I found ones for FP 8 & 9, but not for 6. Are they the
> same, or is there one just for 6 somewhere?
>
> Anyway, because VB was giving me errors, I wrote a program (PRG) that
> packed
> the DB and hoped to have it run everytime FP started. However, with the
> information you provided, it looks like that won't happen. Are there
> other
> ways to PACK a DB either in VB or through some sort of stored procedure in
> FP?
>
> Thanks bunches

Anders

2005-09-14, 8:25 pm

Just connect exclusive and send the command 'PACK tablename'
-Anders

"Cindy Winegarden" < cindy_winegarden@msn
.com> skrev i meddelandet
news:O$DCmHYuFHA.2076@TK2MSFTNGP14.phx.gbl...
> Hi Ken,
>
> You can download the latest FoxPro and Visual FoxPro OLE DB data provider
> from msdn.microsoft.com/vfoxpro/downloads/updates. It works with all
> versions of FoxPro tables.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
>
> "Ken D" <KenD@discussions.microsoft.com> wrote in message
> news:2F6B02BA-3AAB-4194-A56E- 4BDB0293F84D@microso
ft.com...
>
>

Ken D

2005-09-15, 11:24 am

Cindy,

The OLE DB provider worked!! I called PACK <tablename> (which I had done a
hundred times before with no success), and it went straight through!! I very
much appreciate your help since I had been working on this problem for about
3 days.

Thanks again

Anders

2005-09-15, 11:24 am

There's a Help file for the ODBC driver, drvvfp.hlp, which you should find
somewhere in you Windows system folder. It lists the supported commands.
VFPODBC only supports VFP6 data formats. Any type of enhancement to dbf
tables and the DBC is only supported in VFPOLEDB.
-Anders

"Ken D" <KenD@discussions.microsoft.com> skrev i meddelandet
news:FAB4AF24-3B63-4BE8-8F9C- F5BDB3675D91@microso
ft.com...
> Cindy,
>
> The OLE DB provider worked!! I called PACK <tablename> (which I had done a
> hundred times before with no success), and it went straight through!! I
> very
> much appreciate your help since I had been working on this problem for
> about
> 3 days.
>
> Thanks again

Sponsored Links