Establish link to SQL server tables in VBA

[jpl458]

I have added the follwing code to a data entry form in ACCESS and it works fine except that each time I exit ACCESS the link to SQL Server via ODBC goes away, so upon rentry I have to go to Tool - Database Utilities - Linked Table Manager - check off the table, and the connection is reestablished. Here is the code, which was recently on this forum for another issue;

'Add SQL to string for looking up Master_ID in
'Master Accounts Table on Server
Dim StrLUMID As String
StrLUMID = "SELECT dbo_Master_Accounts.Master_ID FROM"
StrLUMID = StrLUMID + " dbo_Master_Accounts"
StrLUMID = StrLUMID + " where dbo_Master_Accounts.Master_Id='" & Me.Card_No & "'" & "'Uid=as;Pwd=Donalway'"
'StrLUMID = "Driver={Microsoft Access Driver (*.mdb)};" & "Dbq=" & Server.MapPath(".\db\db.mdb") & ";Uid=sa;Pwd=Donalway;"


'Set up select query
Dim cnndbo As ADODB.Connection
Set cnndbo = CurrentProject.Connection
Dim rsdbo As New ADODB.Recordset
rsdbo.ActiveConnection = cnndbo
rsdbo.CursorType = adOpenDynamic

'Send query to SQL Server
rsdbpen StrLUMID
'Interrogate the returned recordset to see if it contains a value.
'If not Display Message. If match, keep on truclin
If rsdbo.EOF = True And rsdbo.BOF = True Then
MsgBox "Invalid Card Number"
Me.Card_No.SetFocus
Else
End If

End Sub

I have two questions; First, How do I establish linkage to the table Account_Master in VBA, and second, how would I send a password to SQL Server. I have made multiple attempts at both and done a lot of reading, but can't get it to work.

Thanks in advance

jpl

 

[jedraw]

Here is command from Access to link/relink a table. You may wish to do some googling to adapt to your needs.


DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=User2;PWD=

http://www;LANGUAGE=us_english;"

_
& "DATABASE=pubs", acTable, "Authors", "dboAuthors

 

[jpl458]

That helps. I was taking thing too literally, transferdatabase - for transferring data, But I see that it links as well.

Thank you for your response.

jpl

 

[jpl458]

Still having problems after trolling the web and trying all sorts of things. The ODBC name is SQL_Server, the database name is marketdata, and the table name is Master_Accounts. Here is the statment;

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=SQL_Server;UID=sa;PWD=conway;LANGUAGE=us_english;" _
& "DATABASE=marketdata", acTable, "Master_Accounts"

No matter what I do I get ODBC call failed.

Lost in the code.

jpl