troubleshooting Question

Problem creating ODBC connection to sql 2k8 dynamically

Avatar of Jon Jaques
Jon JaquesFlag for United States of America asked on
DatabasesMicrosoft AccessMicrosoft Development
8 Comments1 Solution547 ViewsLast Modified:
Hello, I have some code in an access 2007 database which I've been using for a couple of years, at least, that creates an ODBC connection to a remote sql server. I recently upgraded that remote server to sql 2008 from sql 2000, though, and now the code doesn't work, it just gives me "CreateDSNConnection encountered an unexpected error: ODBC--call failed." I tested the code against sql 2005, and it seems to work, and I manually create the ODBC to the 2008 server, and THAT works, so, I'm thinking, it must be something in the syntax of the parameters passed to the server, that aren't working?

Any ideas, anybody, please? I've stared at it too much, and can't seem to figure out what's wrong!

Thanks in advance!!!!!

Public Function RegisterDatabaseKTONLINE()
    If CreateDSNConnection("my.server.net", "mydb", "myuser", "mypass", "mydsnname") Then
        ' MsgBox "SUCCESS! All Functions of the database should work now.", vbOKOnly, "SUCCESS!"
    Else
        MsgBox "ERROR in RegisterDatabaseKTONLINE! The database connection could not be made... Please contact your administrator." & vbCrLf & "(" & err.Number & ") " & err.Description, vbOKOnly, "ERROR!"
    End If
End Function

Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String, Optional strDSNName As String) As Boolean
    On Error GoTo CreateDSNConnection_Err

    Dim stConnect As String
    
    If Len(stUsername) = 0 Then
        stConnect = "Description=" & stDatabase & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes"
    Else
        stConnect = "Description=" & stDatabase & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "UID=" & stUsername & vbCr & "password=" & stPassword
    End If
    
    Debug.Print stConnect
    
    If IsNull(strDSNName) Or strDSNName = "" Then
        DBEngine.RegisterDatabase stDatabase, "SQL Server", True, stConnect
    Else
        DBEngine.RegisterDatabase strDSNName, "SQL Server", True, stConnect
    End If
        
    '// Add error checking.
    CreateDSNConnection = True
    Exit Function
CreateDSNConnection_Err:
    
    CreateDSNConnection = False
    Debug.Print "CreateDSNConnection encountered an unexpected error: " & err.Description
    
End Function
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros