Foxpro - VB - Access what do I need?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James Houston - 08 Apr 2005 22:19 GMT

I have an app written in VB6 that manages data exchange between 3 different
applications, each of which uses a different DB format. Briefly:

1. Data entry is done using an ancient DOS app called DataEase. DataEase
will not directly interact with any modern db, but it can export a text
file. Once the user has finished their data entry session, the data is
written to a text file.

2. My app watches for any changes in the export file and imports the data
into an Access 97 db.

3. After manipulation, the data is then appended to an application that
uses Foxpro 2.0 tables. The Foxpro tables are linked to the Access 97 db via
ODBC. Therein lies my problem.

The Foxpro app in this scheme has to be upgraded to a newer version that
uses Foxpro 6 tables. I've tried to get FP6 to talk to Access 97 via ODBC
in the past, and couldn't get it to work. Ditto using ADO. Now that I'm
being forced to upgrade, I'm getting ready to try it again. The first two
links in this chain, the DOS and Access 97 apps will remain largely
unchanged. Just to make sure I've got everything set up correctly can
someone tell me what I'll need to to read and write to a Foxpro table via
ODBC? The Foxpro tables are free tables with CDX style indexes.

Any help or advice would be greatly appriciated.

Best

Jim

Reply to this Message

Cindy Winegarden - 09 Apr 2005 19:11 GMT

Hi Jim,

Which ODBC driver were you using when you used VFP6 tables and Access? The
Jet ODBC driver won't work with tables that have any of the newer data
features; you'll need to use the VFP ODBC driver, downloadable from
http://msdn.microsoft.com/vfoxpro/downloads/updates.

From there you need to create an ODBC data source and link to the tables
using the ODBC data source. Get as far as you can, and post back if you have
more questions.

Signature

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

> 3. After manipulation, the data is then appended to an application that
> uses Foxpro 2.0 tables. The Foxpro tables are linked to the Access 97 db
[quoted text clipped - 8 lines]
> someone tell me what I'll need to to read and write to a Foxpro table via
> ODBC? The Foxpro tables are free tables with CDX style indexes.

Reply to this Message

James Houston - 15 Apr 2005 17:21 GMT

Cindy,

First, thanks for all your help so far.

I currently have Visual Foxpro driver version 6.01.8629.01 installed. I
created a DSN and used it to link to one of the Foxpro tables to an Access
97 DB. This particular table only has about 500 records. It takes the
linked table over 14 minutes to display and then Access locks up.

I've tried to install the newer driver from MS's website, but when I launch
the install program, a dialog box flashes too fast for me to read and the
program quits.

Any ideas on how to fix these problems? Or, if I can't get odbc to work,
can you give me an example of an ADO connect string that will allow me to
read and write to these tables? Thanks again.

Best,

Jim

> Hi Jim,
>
[quoted text clipped - 20 lines]
>> Foxpro table via ODBC? The Foxpro tables are free tables with CDX style
>> indexes.

Reply to this Message

Cindy Winegarden - 16 Apr 2005 03:22 GMT

Hi Jim,

> I currently have Visual Foxpro driver version 6.01.8629.01 installed. I
> created a DSN and used it to link to one of the Foxpro tables to an Access
> 97 DB. This particular table only has about 500 records. It takes the
> linked table over 14 minutes to display and then Access locks up.

I've seen reports of problems like this but haven't pursued it to
see if there's a solution.

> I've tried to install the newer driver from MS's website, but when I
> launch the install program, a dialog box flashes too fast for me to read
> and the program quits.

Newer driver? Do you perhaps mean the OLE DB data provider? That's
not a "newer" ODBC driver; OLE DB is a whole new technology. In any case,
the OLE DB data provider is downloadable from the same URL I posted before.

> can you give me an example of an ADO connect string that will allow me to
> read and write to these tables?

Public Sub Main()

'-- First create a table
Dim conn1 As ADODB.Connection
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 = _
"Create Table Test " & _
"(Field1 N(10, 4), Field2 C(10))"
cmd1.Execute
cmd1.CommandText = _
"Insert Into Test Values " & _
"(100, 'HelloWorld')"
cmd1.Execute
conn1.Close
Set conn1 = Nothing

'-- Now make some changes
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 = _
"Update Test " & _
"Set Field2 = 'Goodbye' " & _
"Where Field1 = 100"
cmd1.Execute
conn1.Close
Set conn1 = Nothing

End Sub

Reply to this Message

James Houston - 20 Apr 2005 16:11 GMT

Thanks again for your help. Another project has come up that I have to work
on first, but I'll give your example a try in a couple of days.

Thanks again

Best

Jim

> Hi Jim,
>
[quoted text clipped - 59 lines]
>
> End Sub

Reply to this Message

James Houston - 30 Apr 2005 20:44 GMT

Cindy

I finally got VB talking to Foxpro, but I'm still running into trouble when
I try to update a recordset. The following code:

With rs
.ActiveConnection = conn1
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Open "c:\cpsw\cps.dbf", , adOpenKeyset, adLockOptimistic
End With

With rs
.AddNew
!invoice = "11111"
.Update
End With

returns the error message "Insufficient base table information for updating
or refreshing". Any idea what I'm doing wrong? Or does the Foxpro OLE DB
provider not support updateable recordsets? Do I have to build an INSERT
query to add data to the Foxpro tables?

Thanks for your help.

Best,

Jim

> Hi Jim,
>
[quoted text clipped - 59 lines]
>
> End Sub

Reply to this Message

Cindy Winegarden - 09 Apr 2005 19:11 GMT

Signature

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

Reply to this Message

VB Forum / Database Access / April 2005

Foxpro - VB - Access what do I need?