Linking tables with DSN-less connection 1

[travisbrown]

I'm trying to create an Access db that has linked tables so I can send to client as a front end for a SQL 2005 db.

I'm running into a little problem.

1. I was trying to create an AutoExec module that would load when the db was started. I'm a little unfamiliar with Access. I thought I did it right, but I created a new module - it spawns MS Visual Basic, I stuck in this code and saved as AutoExec. What's the right way to create an autoexec module?

2. Since it doesn't run automatically, when I run the code manually in MSVB Editor, it balks and prompts me for password in a modal with the PW field populated. I copy and paste the same password I use as a variable value and then it executes the rest of the function calls and creates the linked tables in Access. If I msgbox the string - it appears to be using the correct PW even as it prompts me for one.

3. When I try to then open the tables in Access, I get "ODBC -- connection to 'SQLservermyservername.com' failed"

Option Compare Database

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

Sub LinkTables()
Dim login As String
Dim server As String
Dim password As String
Dim database As String

login = "correct login"
server = "correct server"
password = "correct password"
database = "correct database"

Call AttachDSNLessTable("TBL_PRODUCTS", "TBL_PRODUCTS", server, database, login, password)
Call AttachDSNLessTable("IDX_CATEGORY", "IDX_CATEGORY", server, database, login, password)
Call AttachDSNLessTable("LKUP_CATEGORY", "LKUP_CATEGORY", server, database, login, password)
Call AttachDSNLessTable("LKUP_STATUS", "LKUP_STATUS", server, database, login, password)
Call AttachDSNLessTable("TBL_COMPANY", "LKUP_STATUS", server, database, login, password)
Call AttachDSNLessTable("TBL_CUSTOMERS", "LKUP_STATUS", server, database, login, password)

MsgBox "Tables Loaded"
End Sub

 

[travisbrown]

I meant to take the code out of the sub - of course it wouldn't run without calling the sub, but that was part of the dilemma of point #1

 

[JoeAtWork]

Create a macro called "Autoexec". Make the first (and only) action a "RunCode". In the "Function Name" text box, enter the name of the VBA function you want to call, such as "TestAX ()".

Note that it has to be a function and not a subroutine, and you have to declare it as public, so as an example the function might look like this:

Public Function TestAX() As Boolean
    MsgBox "Hello World"
End Function

 

[travisbrown]

Thanks Joe. Didn't know that events had to be a function rather than a sub. Got that bit working

The more perplexing problem is still that I declare the value for the password in the function, and for each call to create a table upon file open I get an error and prompted for the password. I paste in exactly the same password string for the credentials box that pops up and it works. Why won't it connect properly using the password as a variable?

Would it be a character issue? The password format has curly braces and exclamation marks. Could the function call escape characters differently than the modal that asks me for the connection info?

 

[travisbrown]

I guess it doesn't like curly braces in the password. I created another SQL Server login without curly braces and it works.

Is there another way to escape curly braces other than doubling them? that didn't seem to work.