Creating a Link Table Manager with less DNS connection

Hi all;

I have got a link table manager on the net and I have tried to modify it to see if it can be used to create a less DSN connection because I will be shipping my application to other people as well with different networks. The original code before amendments  is listed below:


'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of 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 CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
    On Error GoTo CreateDSNConnection_Err

    Dim stConnect As String
   
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes"
    Else
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr
    End If
   
    DBEngine.RegisterDatabase "myDSN", "SQL Server", True, stConnect
       
    '// Add error checking.
    CreateDSNConnection = True
    Exit Function
CreateDSNConnection_Err:
   
    CreateDSNConnection = False
    MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description
   
End Function



Note If the RegisterDatabase method is called again, the DSN is updated.

To call the CreateDSNConnection function, add code that is similar to one of the following code examples in the AutoExec macro or in the startup form Form_Open event:
When you use the AutoExec macro, call the CreateDSNConnection function, and then pass parameters that are similar to the following from the RunCode action.
    CreateDSNConnection ("(local)", "pubs", "", "")
When you use the startup form, add code that is similar to the following to the Form_Open event.



Private Sub Form_Open(Cancel As Integer)
    If CreateDSNConnection("(local)", "pubs", "", "") Then
        '// All is okay.
    Else
        '// Not okay.
    End If
End Sub



Amended  code :

Because of  the Option Explicit used above I have the credentials not accepted and the message says VARIABLE NOT DEFINED

(1) & "SERVER=" & DAVE\SQLEXPRESS & 
(2) DATABASE=" & PZCaPremier

Due to the mentioned error above I decided to declare the variable as below:

Dim stDAVESQLEXPRESS As String ( Please note the slash (\) is not accepted here so I left out,and so I'm not sure whether this work)
Dim stPZCaPremier As String

All what I'm seeking is your second opinion because I have not yet started using the above, kindly check whether the above code can work or not!


Original code before amendments:


'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of 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 CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
    On Error GoTo CreateDSNConnection_Err

    Dim stConnect As String
   
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stDAVESQLEXPRESS& vbCr & "DATABASE=" & stPZCaPremier & vbCr & "Trusted_Connection=Yes"
    Else
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stDAVESQLEXPRESS & vbCr & "DATABASE=" & stPZCaPremier & vbCr
    End If
   
    DBEngine.RegisterDatabase "PZcaPremierFileDSN", "SQL Server", True, stConnect
       
    '// Add error checking.
    CreateDSNConnection = True
    Exit Function
CreateDSNConnection_Err:
   
    CreateDSNConnection = False
    MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description
   
End Function



Note If the RegisterDatabase method is called again, the DSN is updated.

To call the CreateDSNConnection function, add code that is similar to one of the following code examples in the AutoExec macro or in the startup form Form_Open event:
When you use the AutoExec macro, call the CreateDSNConnection function, and then pass parameters that are similar to the following from the RunCode action.
    CreateDSNConnection ("(local)", "pubs", "", "")
When you use the startup form, add code that is similar to the following to the Form_Open event.



Private Sub Form_Open(Cancel As Integer)
    If CreateDSNConnection("(local)", "pubs", "", "") Then
        '// All is okay.
    Else
        '// Not okay.
    End If
End Sub




Note This method assumes that you have already created the SQL Server linked tables in the Access database by using "myDSN" as the DSN name.



Regards

Chris