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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
route217JuniorAuthor Commented:

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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

route217JuniorAuthor Commented:

is a made u0 name of the database
route217JuniorAuthor Commented:
still getting odb cconnection failed error message
route217JuniorAuthor 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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.