CODEWITHSUNDEEP
vbams-access
Matt
Matt

Reputation: 11

Trying to dynamically assign SQL Server name and Database

Not sure what I am doing wrong. When it passes the server name along, it's passing along txtlink3 instead of the server name. See screen shots for more details

Server Connection after running code.

Form where the server Name and database name come from

I appreciate the assistance.


Private Sub cmdlink1_Click()
On Error GoTo Err_cmdlink1_Click
DoCmd.SetWarnings False

Dim strServer As String
Dim strDatabase As String

strServer = txtlink3   'This comes from a Field in the form
strDatabase = txtlink4 'This comes from a Field in the form 


DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC; Driver={SQL Server};Server=txtlink3;Database=txtlink4;Trusted_Connection=Yes", acTable, "dbo.address", "AddressMS" ' Use for test SQLdb on matts workstation

MsgBox "Done"
DoCmd.SetWarnings True

Exit_cmdlink1_Click:
    Exit Sub

Err_cmdlink1_Click:
    MsgBox Err.Description
    Resume Exit_cmdlink1_Click

I have done something similar for access and it works fine, See code below

Private Sub cmdlink2_Click()
On Error GoTo Err_cmdlink2_Click
DoCmd.SetWarnings False

If DLookup("[license #]", "Ticket") <> DLookup("strlicense", "tblsmsettings") Or IsNull(DLookup("strlicense", "tblsmsettings")) = True Then
MsgBox "You don't have a License to use this product. Please contact the software vendor", vbOKOnly
Exit Sub 'be sure that they have a license to use this app
End If

Dim strFile As String
strFile = txtlink2
If Dir(strFile) = "" Then ' see if the file exists before dropping
MsgBox "The database specified does not exist"
Else
DoCmd.SetWarnings False

DoCmd.RunSQL "drop Table [Journal Disbursements]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Journal Disbursements", "Journal Disbursements"

DoCmd.RunSQL "drop table [Journal Receipts]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Journal Receipts", "Journal Receipts"

DoCmd.RunSQL "drop table [Journal General]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Journal General", "Journal General"

DoCmd.RunSQL "drop table [Journal Purchases]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Journal Purchases", "Journal Purchases"

DoCmd.RunSQL "drop table [Journal Sales]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Journal Sales", "Journal Sales"

DoCmd.RunSQL "drop table [Ticket]"
DoCmd.TransferDatabase acLink, "Microsoft Access", txtlink2, acTable, "Ticket", "Ticket"

MsgBox "Done"
DoCmd.SetWarnings True

End If

Exit_cmdlink2_Click:
    Exit Sub

Err_cmdlink2_Click:
    MsgBox Err.Description
    Resume Exit_cmdlink2_Click
    
End Sub

Upvotes: 0

Views: 107

Answers (1)

June7
June7

Reputation: 21370

If you want to build connection string with dynamic elements, then must concatenate variable inputs.

DoCmd.TransferDatabase acLink, "ODBC Database", _
     "ODBC; Driver={SQL Server};Server=" & strServer & ";Database=" & strDatabase & ";Trusted_Connection=Yes", _
     acTable, "dbo.address", "AddressMS"

Upvotes: 1

Related Questions