Access FoxPro via OLEDB provider 1

[RichardBarth]

I have an Access application in which I need to process several FoxPro free tables (.dbf's) in multiple directories. Rather then creating multiple ODBC DNS entries, I prefer to access the tables via ADO using the oledb provider.

Can anyone help me with a sample of the code I need to create the connection and establish a recordset for these FoxPro tables? Any help is greatly appreciated.

 

[Mike Lewis]

Richard,

Did you post this same question somewhere else yesterday? If do, do you still have the replies?

I'll try to summarise my own replies:

- Your Foxpro files are in fact free tables. The FoxPro OLE DB povider and ODBC driver treat all the free tables stored in the same directory as a single database. If your tables are in multiple directories, you will need to create a separate DSN to each directory.

It makes no difference whether you create the DSN interactively, or use a connection string to do it programmatically. Also, it makes no difference whether you use ADO or some other method.

I hope this helps.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[craigber]

Huh? I don't really follow what you're saying, Mike. It definately makes a difference if you create a DSN or not. DSN is strictly an ODBC connectivity issue. If you specify a DSN, you will always connect via ODBC, even if you are using ADO.


Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[Mike Lewis]

Craig,

I didn't say that it doesn't make a difference "if you create a DSN or not". I said it doesn't make a difference how you create the DSN.

My point was that the ODBC driver will always equate the directory to the database. In Richard's earlier thread, someone (I think it was Olaf, but I'm not sure) showed some code that established the connection string programmatically. My point was that, even if he used that code, he would still need to treat each directory as a separate database.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

Hi Richard,

start from here:

Sub foxtest()

Dim i As Integer
Dim oConn As Object
Dim oRS As Object
Set oConn = CreateObject("AdoDB.Connection")
Set oRS = CreateObject("AdoDB.Recordset")

oConn.ConnectionString = "provider=vfpoledb.1;data source=d:\MyFreeTables\"
oConn.Open
Set oRS = oConn.Execute("select * from table1")

For i = 1 To oRS.Fields.Count
   MsgBox (oRS.Fields(i - 1).Name)
Next i

Set oRS = Nothing
oConn.Close
Set oConn = Nothing

End Sub

This will establish a DSNless connection to the free table directory d:\MyFreeTables, then query all data from a table called table1.dbf into a recordset and display field names of that table.

Bye, Olaf.

 

[RichardBarth]

That's super, OlafDoschke.

Do you know of any I can identify or bypass the deleted records in the table?

 

[JimWWinter]

Set oRS = oConn.Execute("select * from table1 where not deleted()")

Regards,
Jim

 

[RichardBarth]

Thanks, Jim. That's perfect. Works like a charm.

(Why didn't I think of that. I used to program in dBase and Foxpro for years. But that was over 15 years ago. How quickly we forget.)

 

[Olaf Doschke]

You may also issue once:

oConn.Execute("Set Deleted On")

And afterwards you don't need the filter "not deleted()" anymore.

You may even get in trouble with more complex selects regarding many tables when using deleted(). And you may not be able to solve the problem by passing the table name/alias in a call "not deleted(cAlias)", as you can't be sure what temporary workarea names the SQL-Engine will use during the select.

Bye, Olaf.