docmd.transferdatabase access 2003 to sql 2005

[Chris Kemp]

I have a sql 2000 database which i have attached to the sql 2005 studio.
Then I created a new ODBC connection file which works fine (I know because
I have attached to files in the sql database from Access successfully by
hand). I replicate it below:
[ODBC]
DRIVER=SQL Server
UID=Administrator
Trusted_Connection=Yes
DATABASE=experimentdatasql
WSID=COMPUTER
APP=Microsoft Office 2003
SERVER=COMPUTER\COMPUTER


I am having trouble constructing a connectstring which wil allow me to
progrmmatically link to the database using a trusted connection. That same
connectstring I am using in passthrough queries without success. I am using
the following connectstring:
"ODBC;DRIVER=SQL Server;SERVER=COMPUTER\COMPUTER;Database =
experimentdatasql;Trusted_Connection = Yes;"

When I start to look through the tables I want to link, I am getting an
error message which says that the jet engine could not find the object
<first tablename>

Here's the transferdatabase command I am using:

DoCmd.TransferDatabase acLink, "ODBC Database", ConnectString, acTable,
rst.Fields(0), rst.Fields(0), False

This whole process worked fine with a different connectstring before I
attached the database to sql 2005 visual studio.

Anybody have a clue what's wrong????

Thanks

 

[Douglas J. Steele]

Try putting braces around the Driver:

"ODBC;DRIVER={SQL
Server};SERVER=COMPUTER\COMPUTER;Database=experimentdatasql;Trusted_Connection=Yes;"

Also, you've got some spaces around some of the = signs. Try removing them.

 

[Chris Kemp]

The braces did it. Thanks a lot. I guess sql 2005 is a lot more
persnickity than sql 2000.

Try putting braces around the Driver:

"ODBC;DRIVER={SQL
Server};SERVER=COMPUTER\COMPUTER;Database=experimentdatasql;Trusted_Connection=Yes;"

Also, you've got some spaces around some of the = signs. Try removing
them.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a sql 2000 database which i have attached to the sql 2005 studio.
Then I created a new ODBC connection file which works fine (I know
because I have attached to files in the sql database from Access
successfully by hand). I replicate it below:
[ODBC]
DRIVER=SQL Server
UID=Administrator
Trusted_Connection=Yes
DATABASE=experimentdatasql
WSID=COMPUTER
APP=Microsoft Office 2003
SERVER=COMPUTER\COMPUTER


I am having trouble constructing a connectstring which wil allow me to
progrmmatically link to the database using a trusted connection. That
same connectstring I am using in passthrough queries without success. I
am using the following connectstring:
"ODBC;DRIVER=SQL Server;SERVER=COMPUTER\COMPUTER;Database =
experimentdatasql;Trusted_Connection = Yes;"

When I start to look through the tables I want to link, I am getting an
error message which says that the jet engine could not find the object
<first tablename>

Here's the transferdatabase command I am using:

DoCmd.TransferDatabase acLink, "ODBC Database", ConnectString,
acTable, rst.Fields(0), rst.Fields(0), False

This whole process worked fine with a different connectstring before I
attached the database to sql 2005 visual studio.

Anybody have a clue what's wrong????

Thanks