querydef function not working???

Hi Experts (access 2003)
Shot in the dark....

I have the following function to set my SQL connection strings to the following instant...is the code incomplete or do i need to tell it (vba code0 that it's a new connection....totally confused...

If i am talking rubbish please step in.....and correct me...

I am trying to connect to SQL and keep on getting a ODBC connection failure error msg...

whats could be wrong....

Public Function GetQueryLinks()
On Error GoTo Err_GetQueryLinks

    Dim qdf As DAO.QueryDef
    Dim StrConn As String
    StrConn = "ODBC;Driver=SQL Server;UID=MyUID;PWD=MyPass;Server=SEEEPB4SBT45646.global.Hire.com;Port=43533;Database= Apples_Ornages;"
     For Each qdf In CurrentDb.QueryDefs
        If qdf.Connect <> "" Then

            qdf.Connect = StrConn
            Debug.Print qdf.Connect

        End If

    Set qdf = Nothing
    Exit Function

    MsgBox Err.Number & " (" & Err.Description & ") in procedure GetQueryLinks of Module basTableLinks"
    Resume Exit_GetQueryLinks

End Function

Open in new window

Who is Participating?
Gustav BrockConnect With a Mentor CIOCommented:
I doubt there is a space here:

Database= Apples_Ornages

and what is ornages?

As a side note, you can search for the type of query:

    Dim dbs         As DAO.Database
    Dim qdf         As DAO.QueryDef
    Dim wConnect    As String
    If pServer <> "" And pPort <> 0 Then
        Set dbs = CurrentDb
        wConnect = "Some connection string"
        For Each qdf In dbs.QueryDefs
            If qdf.Type = dbQSQLPassThrough Then
                qdf.Connect = wConnect
            End If
    End If

Open in new window

route217Author Commented:

sould I add ur vbq to a new sub
Gustav BrockCIOCommented:
No, it was just for inspiration.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

route217Author Commented:

is a made u0 name of the database
route217Author Commented:
still getting odb cconnection failed error message
route217Author Commented:
thanks fot it working
Gustav BrockCIOCommented:
Then check the existing connect string of an attached table or one of the queries and adjust your new connection as needed.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.