VFP Ole DB provider for Access?

[Danny]

I've installed the VFP Ole Db provider on my machine, but it doesn't show in
the list of drivers when creating a new odbc data source. I can import data
into an Excel file using this driver, but not in Access. Can someone point me
in the right direction?

Thanks.

Danny S.

 

[Sylvain Lafontaine]

Normal, ODBC data source (or DSN) are for ODBC and not OLEDB.

Use the same piece of code to import data into Access as you are using Excel
or use the ODBC VFP driver if you want to have linked table to FoxPro. For
more info, see:

http://support.microsoft.com/kb/824264

http://www.foxite.com/articles/read.aspx?id=32&document=accessing-your-vfp-database-in-access

 

[Danny]

Thanks for replying.

There is no code involved, as I'm just using the Access 2003 Link Table
wizard.

I have previously followed the steps in your instructions, but get an error
- I've posted help with that already. As a workaround to that error, I was
trying to connect in a different way, using a different connector. So I'm
still stuck.

Is there a file that Excel stores the connection info in, which I can either
point Access to, or use as reference for connection info?

Thanks.

Danny

 

[Sylvain Lafontaine]

How are you doing your importation with Excel? Are you using a wizard or
using some VBA code? For the missing linked table in your other post, are
you sure that you spelled it right in your query?

 

[Cindy Winegarden]

Hi Danny,

First, although they are easy to confuse, ODBC and OLE DB are different
technologies. While you can link to tables in Access via ODBC, the only way
you can access them using OLE DB is via ADO recordsets.

I assume that when you import data into Excel you are using an ODBC data
source. When you use Access' external data link dialog, you will need to
scroll down the combobox where the types of data are listed to ODBC data
sources. That will take you to the ODBC dialog where you should see the same
data source as you set up for importing the data into Excel.

If you can't get it working, post back with more details of exactly what you
did in Excel and exactly what you tried to do in Access.

--
Cindy Winegarden
(e-mail address removed)


VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx

 

[Danny]

Hi Cindy.

You answered my question via my other post, "Error connecting to VFP tables"
- namely, that I have to use code to connect to vfp tables if I'm going to
use oledb.

OK, so now I need to write some code. I'm running Access 2003, and I've
written the following code:

Function LinkToMOM()

Dim MOMdbc As ADODB.Connection

Set MOMdbc = New ADODB.Connection

MOMdbc.Open ("Provider=vfpoledb.1;Data Source = f:\momwin\momdata.dbc")

End Function

I ran this without errors, except I don't know what it did! What adodb
command(s) do I use to establish links to the tables represented in the .dbc
file?

Thanks for the help.

Danny

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tom Wickerath]

re:
the only way
you can access them using OLE DB is via ADO recordsets.


isn't 'using ADO recordsets' THE DEFAULT?

 

[Danny]

OK, ok, people... I guess I haven't explained myself clearly. I now
understand that I have to use code to access these tables. I am familiar with
working with recordsets, but this has heretofore been based on linked tables
- the connections were already made, I just have to reference the table names
in my SQL strings. But at this point in my working with these VFP tables,
there are no links yet, and I don't know how to establish those links or
reference the tables - I don't know the syntax. That's what I need help with
- connecting to the VFP database file (.dbc), and then specifying tables that
I want to use.

Thanks.

 

['69 Camaro]

Sorry. You've been attacked by our resident troll, Aaron, masquarading as
Tom. He has no intention of helping you.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Cindy Winegarden]

Hi Danny,

Hello from the beach!

What your code does is open a connection - kind of like opening a door. You
didn't select, read, update or delete any data - kind of like no one came
through the door going either way.

You can't "link" to the data in the way that you can using ODBC. You can
select data into an ADO recordset, the same way you might when selecting
data via ADO from SQL Server.

The FoxPro DBC is a database container in that it has metadata about the
tables it contains. An example is that it houses stored procedures. You
don't interact with it directly but when your connection points to it you
can then interact with the tables with traditional SQL such as

"Select LastName, FirstName From MyTable Where City = 'The Beach'"

To work with data from a particular table all you need to do is use it's
name - MyTable, where there is a MyTable.dbf present in the directory where
the DBC is.

--
Cindy Winegarden
(e-mail address removed)


VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx