ODBC -- connection to <dbasename> failed

[mlar1]

Dear all,

I am working on an old database that was formerly set up by a colleague in the US.

When I try to run a query on this database from the UK, I get the error message in the subject line: ODBC -- connection to <dbasename> failed

When someone in the US runs the same query, he gets prompted for login details, whereas I do not. The colleague in the US is able to run queries successfully.

I've figured that the problem I'm experiencing is either that the connection path to the database is incorrect/missing or that the login details specified in Control Panel > Administrative Tools > Data Sources are not set correctly.

I was wondering if anyone with more experience with database connections, and perhaps cross-border connections, could suggest the most likely thing that is going wrong and how I can rectify matters.

Many thanks,

mla

 

[1DMF]

I prefer to have mine run with DNS-Less connection where the tables are linked and accessed via IP address with user ID / Password.

This way I can simply run 'SetLinkedTables' code before I publish the DB.

That way anyone, anywhere (as long as they have VPN'd into the company network), can run the DB.

Public Function SetLinkedTables()

    Dim db As Database
    Dim Cnct As String
    Dim tdf As TableDef
    Dim sCon As Variant
    Dim a As Integer
    Dim sVar As Variant
    Dim sName As String
    
    Dim sServer As String
    Dim sPWord As String
    
    
    On Error GoTo SLT_Err

    Set db = CurrentDb

    db.TableDefs.Refresh
  
    ' Loop Table Defs
    For Each tdf In db.TableDefs
        With tdf
            'split current string to find db data
            sCon = Split(.Connect, ";")
            For a = 0 To UBound(sCon)
                If sCon(a) <> "" Then
                    sVar = Split(sCon(a), "=")
                    If sVar(0) = "DATABASE" Then
                        Cnct = sVar(1)
                        If Left(.Name, 4) = "dbo_" Then
                            sName = Right(.Name, Len(.Name) - 4)
                            sServer = "192.168.0.2"
                            sPWord = "relevant password"
                        ElseIf Left(.Name, 4) = "aff_" Then
                            sName = Right(.Name, Len(.Name) - 4)
                            sServer = "192.168.0.2"
                            sPWord = "relevant password"
                        ElseIf Left(.Name, 3) = "ar_" Then
                            sServer = "192.168.0.1"
                            sPWord = "relevant password"
                            sName = Right(.Name, Len(.Name) - 3)
                        Else
                            sName = .Name
                            sServer = "192.168.0.1"
                            sPWord = "relevant password"
                        End If
                        ' create connection to table
                        Call AttachDSNLessTable(.Name, sName, sServer, Cnct, "sa", sPWord)
                    End If
                End If
            Next
        End With
    Next
                       
    db.TableDefs.Refresh

    Set tdf = Nothing
    Set db = Nothing
    
    MsgBox "Tables Re-Linked."

SLT_Exit:
   Exit Function

SLT_Err:
   MsgBox "Error in SetLinkedTables : " & Err.Description
   Resume SLT_Exit

End Function
Public 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

It's worth noting that the code is written to deal will different DB schema prefixes, because we have two SQL servers at different locations and I use the prefix (dbo_,aff_,ar_), to work out which server/DB the table is located at so the relevant IP & user credentials can be used.

This method should only be used if you compile the DB into an MDE or similar as it has the SQL 'sa' user credentials hardcoded into it, so you must consider security before you use this type of method.





"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts