Importing .dbf files (Foxpro) to SQL via Access client

[JoeBobHankey]

Background:

- I'm running MSDE 2000 (not client tools, stored procedure capability,
etc). This may change, but not in the first part of development.

- My Access file is an Access 2002 project (.adp project client
connecting directly to MSDE SQL database - no .mdb involved or local
file tables beyond the .dbfs to be imported).

- Using ODBC to connect to .dbf data sources without a problem (ODBC is
working).

- Using ODBC to connect to MSDE SQL remote server without a problem.
Connections test fine, manual import from within Access works perfectly
(ODBC is working here as well).

Goal:

- To be able to import .dbf files programmatically into MSDE SQL tables
with Access vba. I stress programatically, as the updates will take
place nightly on a scheduled basis (as they currently do in a current
non-sql database system).
Problem:

Whenever I try to use the DoCmd.TransferDatabase function within Access
2002, I receive:

"Run-time error '3011':

The Microsoft Jet database engine could not find the object
'tblMySQLtableName'. Make sure the object exists and that you spell
its name and the path name correctly."

The code that I'm using is as follows:

DoCmd.TransferDatabase acImport, "ODBC", _
"ODBC;DSN=myDSN;SourceD*B=h:\myPath\;SourceType=DB F", _
acTable, "sourcetable.dbf", "tblMySQLtableName"

Linking to external tables isn't allowed by Access as the database is
currently set up.

Additionally, if I have the table, tblMySQLtableName, already created
within the SQL database, the error message indicates that the
tblMySQLtableName1 object could not be found. To me, this indicates
that the source and destination are both recognized and the
TransferDatabase function is attempting (properly so) to create a new
table rather than overwriting or appending to an existing table, but it
stalls on the creation of the new table.

Question:

Does anyone have suggestions (read: example code) of what might be a
good way to either overcome the above problem or perhaps a better way
of accomplishing the same goal, keeping in mind that stored procedures
and most of the DTS functionality isn't available in MSDE?

Thanks in advance,

JBH

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

TransferDatabase only works on JET .mdb files, not .adp files. JET is
the acronym for Joint Engine Technology. This means it can read various
db Engines; .dbf files (XBase type) being one of those. This means you
will have to use JET to read the .dbf files. For ADO you'd use the

Microsoft.Jet.OLEDB.4.0

Provider in the connection string. Perhaps you could open a recordset
on the .dbf file (using JET's OleDB driver) & open a recordset on the
MSDE table(s) (using SQL's OleDB driver) & copy the contents into the
MSDE table(s).

Stored procedures are available in MSDEs. When connected to the MSDE db
look in the Queries tab of the Access (.adp) database window. Click the
New button and double click "Design Stored Procedure." If you cannot
create a stored procedure you may not have permission to create on the
connected db. You have to be a part of the db_ddladmin role or a
sysadmin.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQj+X+IechKqOuFEgEQJNDACfSEHaZpiEHXw+b7Z4tV2X1T AkmXwAoPnj
cEhqHSs05+3xLovT5YtAC8wb
=ZPtK
-----END PGP SIGNATURE-----

JoeBobHankey wrote:

Background:

- I'm running MSDE 2000 (not client tools, stored procedure capability,
etc). This may change, but not in the first part of development.

- My Access file is an Access 2002 project (.adp project client
connecting directly to MSDE SQL database - no .mdb involved or local
file tables beyond the .dbfs to be imported).

- Using ODBC to connect to .dbf data sources without a problem (ODBC is
working).

- Using ODBC to connect to MSDE SQL remote server without a problem.
Connections test fine, manual import from within Access works perfectly
(ODBC is working here as well).

Goal:

- To be able to import .dbf files programmatically into MSDE SQL tables
with Access vba. I stress programatically, as the updates will take
place nightly on a scheduled basis (as they currently do in a current
non-sql database system).
Problem:

Whenever I try to use the DoCmd.TransferDatabase function within Access
2002, I receive:

"Run-time error '3011':

The Microsoft Jet database engine could not find the object
'tblMySQLtableName'. Make sure the object exists and that you spell
its name and the path name correctly."

The code that I'm using is as follows:

DoCmd.TransferDatabase acImport, "ODBC", _
"ODBC;DSN=myDSN;SourceD*B=h:\myPath\;SourceType=DB F", _
acTable, "sourcetable.dbf", "tblMySQLtableName"

Linking to external tables isn't allowed by Access as the database is
currently set up.

Additionally, if I have the table, tblMySQLtableName, already created
within the SQL database, the error message indicates that the
tblMySQLtableName1 object could not be found. To me, this indicates
that the source and destination are both recognized and the
TransferDatabase function is attempting (properly so) to create a new
table rather than overwriting or appending to an existing table, but it
stalls on the creation of the new table.

Question:

Does anyone have suggestions (read: example code) of what might be a
good way to either overcome the above problem or perhaps a better way
of accomplishing the same goal, keeping in mind that stored procedures
and most of the DTS functionality isn't available in MSDE?

Thanks in advance,

JBH