Problems with DoCmd Transferdatabase aclink 1

[jpl458]

Want to link to an SQL Server Express table using DOCmd in vba from ACCESS. I have changed some names for security, but the originales are correct-verified by me and others.

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server}; Server=POS1\SQLExpress; Database=sales; Uid=sa;Pwd=ralphy", acTable, "dbo.Transaction_Table", "Transaction_Table"

I get;

"SQL Server Error 4060
Server rejected the connection;Access to the selected database has been denied."

Been working on this off and on for weeks now. I just heard that DoCmd is unreliable. I can link to the tables using an ODbC DSN conection from the ribbon in ACCESS and it works fine. Just can't do it via vba. Plus, I can' find a connection string for linking tables with Set. The above example is supposed to DSNless.

Any help or suggestions would be appreciated.

Thanks

Jpl

 

[PHV]

Have a look here?

http://www.connectionstrings.com/

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

Been all over that sight. I can't find any mention of linked tables, and nothing about DoCmd. DoCmd transferdatabase aclink seemms to be the forgotten bit of vba.

If I have missed something on that sight, let me know.

I did get an example from another web sight that I thought would work, but it was for earlier ACCESS and Windows 2003 and SQL Server 5 and was a no go.

Thanks for the suggestion though.

jpl

 

[dhookom]

I would test the connect by creating a pass-through query with an ODBC Connection Str of:

ODBC;Driver={SQL Server}; Server=POS1\SQLExpress; Database=sales; Uid=sa;Pwd=ralphy

Set the SQL property to:

SELECT * FROM Transaction_Table

See if this returns records.

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

I tested the connection by using SQL Management Studio and established a connection the the server, and could see the tables etc. I'll give your test a shot as well

Thanks.

jpl

 

[jpl458]

I was trying your connection, but had a problem with that. I am new to connection strins so bear with me. He is what I tried

Private Sub Form_Load()
Dim myconn As New adodb.Connection

set myconn. open ODBC;Driver={SQL Server}; Server=POS1\SQLExpress; Database=sales; Uid=sa;Pwd=ralphy

This gives me a user defined error, missing something I bet.

Thanks




End Sub

 

[dhookom]

What result did you get from the pass-through query?

Hints:
1) follow suggestions/steps from those who are attempting to help you
2) the connection string would need to be inside quotes


Duane
Hook'D on Access
MS Access MVP