Avatar of Jon Jaques
Jon Jaques
Flag for United States of America asked on

Problem creating ODBC connection to sql 2k8 dynamically

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

Open in new window

Microsoft AccessMicrosoft DevelopmentDatabases

Avatar of undefined
Last Comment
Jon Jaques

8/22/2022 - Mon
Rey Obrero (Capricorn1)

Jon Jaques

ASKER
Well I do see that the connection string for 2k8 has some differences from the attributes values that has been working for the odbc connection to the old server;

Hmmm, I wonder... that code, above, uses vbCR to seperate the values; I wonder if I should be using semi-colons, just like in a regular connection string?
Jacques Bourgeois (James Burger)

ODBC is an old standard that has been mainly replaced by OleDB since the end of the 1900's. Microsoft does not put a lot of emphasis on ODBC testing on their new applications, and SQL Server 2008 falls under that group.

If possible, switch your code to OleDB. Otherwise, the following link might help you get a foot into a solution: http://support.microsoft.com/kb/311836
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jon Jaques

ASKER
Yeah, I know, it's done this way for portability from system to system, and performance isn't too critical in this case.
Jon Jaques

ASKER
No other ideas, anybody? I've played around with the syntax that I'm passing through the attributes parameter, but I still can't get this to work against SQL 2k8, even though it does work against sql 2k and 2k5.
Jon Jaques

ASKER
@JamesBurger, I just did a close re-read of that kb article you linked to, and I don't think it applies to me, as I'm not using the "urgent" keyword.

Btw, you didn't used to live in Miami when you were young, did you? LOL
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Jon Jaques

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jon Jaques

ASKER
I found my own workaround.