-----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:[color=blue]
> 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
>[/color]
