Exporting '97 table to SQL database

[TJones8]

Hi.
I have a table in an Access '97 database which needs to be exported, using code, to an SQL database.
I am tring to use the DoCmd.TransferDatabase but that is giving me a run-time error of 3146 (ODBC--call failed) - helpful as ever ;-)
Any ways - this is what i have :-

DoCmd.TransferDatabase acExport, &quot;ODBC Database&quot;, &quot;ODBC;Driver={SQL Server};Server=<<SQLServer>>;Database<<DATABASE;Uid<<USERNAME>>;Pwd=<<PASSWORD>>;&quot;, acTable, <<MY_TABLE>>, <<SQL_TABLE>>, False, False

<<MY_TABLE>> and <<SQL_TABLE>> are different names, and <<SQL_TABLE>> may exist within the SQL db - if so i want to completly overwrite it.

I know that in theory, this works nicly because i can use the same vars to link to a table on the SQL db.

TIA

Tim if at first you don't succeed, get a 10lb lump hammer

 

[thornmastr]

the following worked for me using A97 FE with SS 2000 BE. If you manage to connect to my development system, you will be able to test it there; of course I would rather you did not. Here is the exact statement that worked for me.


DoCmd.TransferDatabase acExport, &quot;ODBC Database&quot;, &quot;ODBC;DSN=DrugDsn;UID=sa;PWD=aaron;DATABASE=DrugCourt&quot;, acTable, &quot;Agent&quot;, &quot;SAgent&quot;, False, False


Now, the nasty part of this. It worked immediately the first time and then constantly and consistently failed which does make a great deal of sense. You cannot do a TransferDatabase to SQLServer if the table already exists on the database. I put in a drop statement before I did the transfer to make certain the table was not there and it consistently worked thereafter.

Hopefully this will solve your problem.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[scking]

Could you use the SQL Server Data Transformation Services Wizard (DTSWIZ.EXE)? This would not force you to rely on Access to create (push) a SQL Server database but allow SQL Server to create (pull) the Access 97 database. ----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[TJones8]

Thx for the help, what i did in the end insted is link the SQL table into my access database and run a 'DELETE *' query before inserting the data i wanted from my table.
That worked fine and seemed nice and simple

Thx

Tim if at first you don't succeed, get a 10lb lump hammer

 

[red54]

Had the same problem, but only on the .update of a row in an existing table. The code ran once and got a &quot;memory error on temp data disk&quot;. Ran again and added about 2000 more records before getting a consistent failure of 3146 etc.

The thing is that the table was viewable in datasheet view without doing anything else.

Gave up and created a new table, and started same code again updating new table. It started up and was running when I left last night.

Time flies when you don't know what you're doing...