Converting an Access mdb to dbc

[Craig]

I would like some suggestions on how to convert an MDB into a DBC. Is there a
simple method to do this, or is it necessary to do it by hand?

Thanks
Craig

[RGBean]

Craig,
Go to the Universal Thread at
http://www.universalthread.com/wconnect/wc.dll?2,54,33,10047 and download
Convert. It has both MDB2DBC and DBC2MDB.

Rick

Post by Craig
I would like some suggestions on how to convert an MDB into a DBC. Is there a
simple method to do this, or is it necessary to do it by hand?
Thanks
Craig

[Anders]

No big deal

OPEN DATABASE x
lnConn=SQLCONNECT() && get an ODBC connection to the MDB
SQLTABLES(lnConn, 'TABLE', 'mdbtables')
SCAN
TEXT TO lcSQL TEXTMERGE NOSHOW
SELECT * FROM "<<table_name>>"
ENDTEXT
lcTable = CHRTRAN(mdbtables.table_name,' ','_')
SQLEXEC(lnConn, lcSQL, 'sqlresult')
COPY TO (lcTABLE) DATABASE x
ENDSCAN

That is roughly what it takes to create a VFP copy of an entire MDB
programmatically.
You may run into some problems with table names with characters that VFP
doesn't support in table names. Add them to the CHRTRAN list.
And add a path to your data folder.
You may also run into the Access programming habit of setting text columns
like Lastname to 255 characters which is one too many for a Character field
in VFP, resulting in a Memo field.
You can corret that afterwards.
SQLTABLES can also get the VIEW names in the MDB.

-Anders

Post by Craig
I would like some suggestions on how to convert an MDB into a DBC. Is there a
simple method to do this, or is it necessary to do it by hand?
Thanks
Craig

[Craig]

Anders, that is essentially what the application that Rick sent me to does.
So thanks for the response.

Craig

Post by Anders
No big deal
OPEN DATABASE x
lnConn=SQLCONNECT() && get an ODBC connection to the MDB
SQLTABLES(lnConn, 'TABLE', 'mdbtables')
SCAN
TEXT TO lcSQL TEXTMERGE NOSHOW
SELECT * FROM "<<table_name>>"
ENDTEXT
lcTable = CHRTRAN(mdbtables.table_name,' ','_')
SQLEXEC(lnConn, lcSQL, 'sqlresult')
COPY TO (lcTABLE) DATABASE x
ENDSCAN
That is roughly what it takes to create a VFP copy of an entire MDB
programmatically.
You may run into some problems with table names with characters that VFP
doesn't support in table names. Add them to the CHRTRAN list.
And add a path to your data folder.
You may also run into the Access programming habit of setting text columns
like Lastname to 255 characters which is one too many for a Character field
in VFP, resulting in a Memo field.
You can corret that afterwards.
SQLTABLES can also get the VIEW names in the MDB.
-Anders

Post by Craig
I would like some suggestions on how to convert an MDB into a DBC. Is

there a

Post by Craig
simple method to do this, or is it necessary to do it by hand?
Thanks
Craig

[Craig]

Hello Rick,
Thanks for the information on this. I just tried it, and I get the following
error:

+++++
Error while converting data. Import aborted. 10 Syntax error.
OK
+++

I'll try to debug it, but if you have any ideas on what the issue is, please
let me know.

by the way, I'm using Access 2003 (but the mdb is Access 2000) and I have
VFP 9.0 with the CTP loaded.

Again thanks

Post by Craig
I would like some suggestions on how to convert an MDB into a DBC. Is there a
simple method to do this, or is it necessary to do it by hand?
Thanks
Craig

[Craig]

Ok, figured it out. Once I got the source to the program, there we some
errors on importing the data when long directory names with spaces were used,
and also Access allows a wider variety of characters to be used in field
names of tables than does FoxPro. Once I fixed those, everything worked fine.

Thanks
Craig

Post by Craig
Hello Rick,
Thanks for the information on this. I just tried it, and I get the following
+++++
Error while converting data. Import aborted. 10 Syntax error.
OK
+++
I'll try to debug it, but if you have any ideas on what the issue is, please
let me know.
by the way, I'm using Access 2003 (but the mdb is Access 2000) and I have
VFP 9.0 with the CTP loaded.
Again thanks

Post by Craig
I would like some suggestions on how to convert an MDB into a DBC. Is there a
simple method to do this, or is it necessary to do it by hand?
Thanks
Craig