VFP -> ODBC -> Access 2

[Neil Toulouse]

Hi!

Is there an SQL command that I can use to return the names of tables contained in an Access MDB?

Basically I have an Access MDB that contains about 40 tables and essentially I want to scan through them and convert them to DBF.

I am using SQL Passthru connecting to Access via ODBC.

TIA
Neil "I love work. I can sit and stare at it for hours..."

 

[rgbean]

Neil,
I don't know the answer, but you may want to check out the utility written by a (now) MS employee that will pull all the data out and put it into tables in a DBC.
It's available on the UT (

http://www.universalthread.com/)

in the Downloads area - look for MDB2DBC - Access to VFP Data Migrator by John Koziol.

He must be getting this information somehow, and the last I knew the download included source.

Rick

 

[Neil Toulouse]

Many thanks for that Rick! Seems to be what I am after, but unfortunately no source

A star for you!

Neil "I love work. I can sit and stare at it for hours..."

 

[JanBucek]

Have a look at func SQLtables(...) and SQLcolumns(..) in VFP:
...
nH=SQLconnect("&quot
IF nH > 0
SQLtables(nH, "TABLE", "tmptable&quot && cursor of tabinfo
index on TABLE_NAME tag TABLENAME
set order to tag TABLENAME
...

HTH

 

[Neil Toulouse]

Thanks Jan! "I love work. I can sit and stare at it for hours..."

 

[Neil Toulouse]

Just in case anyone is following this thread:

The MDB2DBC application has a couple of issues which is a shame! Firstly, the program gives an error if there are no records in the corresponding MDB table. This would be OK but it quits the app and doesn't carry on with the conversion.

Secondly, for some reason it is painfully slow! After removing the empty tables from the conversion it took approx 12hours to complete the conversion!

Using the SQLTables() and SQLColumns() commands suggested by Jan, I managed to build my own wizard, with the conversion taking about 5 minutes! (same computer, same data).

I am completely bemused as to why MDB2DBC took so long!

Thanks for the help guys, much appreciated!

Neil "I love work. I can sit and stare at it for hours..."

 

[rgbean]

Neil,
While I've never used this program, I feel "responsible" for have pointing you at it. Just a couple questions - was the Access data and the VFP data on a network? If yes, could you (would you) try it so both sets of data were on a local system? (I have a feeling it was simply network transfers back and forth that was slowing this so bad.)

You might want to post this observation / criticism on the UT - John frequents this forum and I believe others have taken up the application and fixed/upgraded it.

Rick

 

[jonscott8]

FWIW, the SQL you're after is

SELECT Name
FROM MSysObjects
WHERE Name Not Like 'MSys*' AND MSysObjects.Type=1 Jon Hawkins

 

[Neil Toulouse]

Rick - both data-sets were local on my machine. Don't worry about 'feeling responsible' as you pointed me in the right direction, and persuaded me to write my own 'wizard'.

The only difference between my wizard and the MDB2DBC program is that I use ODBC whereas the MDB3DBC opens an instance of Access and does whatever it does!


Jon - I tried that command as a result from a post in the Access forum but I got an error (something to do with read permissions). I used Jan's suggestion of SQLTables and SQLColumns and got the result I was looking for the wizard I then wrote.

Cheers
Neil "I love work. I can sit and stare at it for hours..."

 

[SnyAc]

Neil... I'd love to see your wizard... I'm facing a similar situation where I need to convert the tables in an Access MDB into VFP format and I can't be sure that the ODBC drivers will be available on the user's computer.

Thanks...

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

 

[baltman]

Import DB from MS Access faq1251-5002

 

[Neil Toulouse]

Hi SnyAc!

The method I employed is essentially the same as the thread that baltman has pointed you to.

I think it is safe to assume the Access ODBC driver will exist.

Hope you get it working!

Neil

I like work. It fascinates me. I can sit and look at it for hours...