ODBC Coonection failed to SQL db

Hi Experts (access 2003)

Not sure whats causing the above problem error message: ODBC Connection  to SQL Server SQLLJH7SYT89203.global.hotwire.com,13356' failed...

other qrys are fine and open produce the a vaild result....
route217JuniorAsked:
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.

Jeffrey CoachmanMIS LiasonCommented:
Please post the SQl for this "rogue" query...
0
route217JuniorAuthor Commented:
hi boag2000

I need to remove names from the qry...can u hang fire.
0
Jeffrey CoachmanMIS LiasonCommented:
...and all the other queries use the same connection?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

route217JuniorAuthor Commented:
yes. ..
0
route217JuniorAuthor Commented:
Hi Boag2000

here is my connection code i am using to define my ODBC.... (i cannot post the qry to much confidential info)


Option Compare Database

Public Function GetQueryLinks()
On Error GoTo Err_GetQueryLinks

    Dim dbs         As DAO.Database
    Dim qdf         As DAO.QueryDef
    Dim wConnect    As String
    
       Set dbs = CurrentDb
        wConnect = "ODBC;Driver=SQL Server;UID=MyUID;PWD=MyPass;Server=SQLLQW4FF43567.global.hire.com,14444;Database=Apples_Oranges;"
        
        For Each qdf In dbs.QueryDefs
            If qdf.Type = dbQSQLPassThrough Then
                qdf.Connect = wConnect
              End If
        Next
           
Exit_GetQueryLinks:
    Set qdf = Nothing
    Exit Function

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

End Function

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
<(i cannot post the qry to much confidential info)>
I don't need the results, only the SQL...

What if you did the processing on the Access side (did not use a Pass through query)?
0
route217JuniorAuthor Commented:
need to do pass through as all the data sits in sql server 2008...
0
route217JuniorAuthor Commented:
ok one question if post the sql can u delete it after viewing it..
0
route217JuniorAuthor Commented:
qry....with odbc error

SELECT lnkContact_MI_Part1.case_created_dateonly, IIf(IsNull(tblRefBrand!strBrand),"Undefined",tblRefBrand!strBrand) AS Brand, IIf([lnkContact_MI_Part2]![Created_By_02]="Dan Houston","TIS",IIf([lnkContact_MI_Part2]![Created_By_02]="Servicing","Servicing",IIf([lnkContact_MI_Part2]![Created_By_02]="Apples","Oranges",IIf([lnkContact_MI_Part2]![Created_By_01]="Bank" Or [lnkContact_MI_Part2]![Created_By_01]="N&W" Or [lnkContact_MI_Part2]![Created_By_01]="N&W1" Or [lnkContact_MI_Part2]![Created_By_01]="Choc" Or [lnkContact_MI_Part2]![Created_By_01]="Hire" Or [lnkContact_MI_Part2]![Created_By_01]="S","Bank","Other")))) AS FunctionLogged, IIf(IsNull(lnkContact_MI_Part1!init_datetime_closed) And (lnkContact_MI_Part1!init_bus_days_os_bank<=1),"Non-R",IIf(Not IsNull(lnkContact_MI_Part1!init_datetime_closed) And (lnkContact_MI_Part1!init_bus_days_bank_to_close<=1),"Non-R","R")) AS Reportable, IIf([lnkContact_MI_Part1]![PAF_Outcome]="Other","Other",IIf([lnkContact_MI_Part1]![PAF_Outcome]="Advice","Assist",IIf([lnkContact_MI_Part1]![PAF_Outcome]="No PAF","Frontline",IIf([lnkContact_MI_Part1]![PAF_Outcome]="Advice and Escalate" Or "Escalation" Or "Escalate as Customer Rejects Full Response" Or "Referred","PAF Accept")))) AS FPOCMetric, Count(lnkContact_MI_Part1.contact_id) AS CountOfcontact_id
FROM ((lnkContact_MI_Part1 INNER JOIN lnkContact_MI_Part2 ON lnkContact_MI_Part1.contact_id = lnkContact_MI_Part2.contact_id) INNER JOIN lnkContact_MI_Part3 ON lnkContact_MI_Part2.contact_id = lnkContact_MI_Part3.contact_id) LEFT JOIN tblRefBrand ON lnkContact_MI_Part2.Brand_01 = tblRefBrand.strResolveBrand
WHERE (((lnkContact_MI_Part1.valid_case_ind)=1)) OR (((lnkContact_MI_Part3.multifaceted_case_ind)=1))
GROUP BY lnkContact_MI_Part1.case_created_dateonly, IIf(IsNull(tblRefBrand!strBrand),"Undefined",tblRefBrand!strBrand), IIf([lnkContact_MI_Part2]![Created_By_02]="n","TIS",IIf([lnkContact_MI_Part2]![Created_By_02]="Servicing","Servicing",IIf([lnkContact_MI_Part2]![Created_By_02]="Choc","Cho",IIf([lnkContact_MI_Part2]![Created_By_01]="Bank" Or [lnkContact_MI_Part2]![Created_By_01]="Northern" Or [lnkContact_MI_Part2]![Created_By_01]="N&W" Or [lnkContact_MI_Part2]![Created_By_01]="Apples" Or [lnkContact_MI_Part2]![Created_By_01]="B" Or [lnkContact_MI_Part2]![Created_By_01]="S","Bank","Other")))), IIf(IsNull(lnkContact_MI_Part1!init_datetime_closed) And (lnkContact_MI_Part1!init_bus_days_os_bank<=1),"Non-R",IIf(Not IsNull(lnkContact_MI_Part1!init_datetime_closed) And (lnkContact_MI_Part1!init_bus_days_bank_to_close<=1),"Non-R","Reportable")), IIf([lnkContact_MI_Part1]![PAF_Outcome]="Other","Other",IIf([lnkContact_MI_Part1]![PAF_Outcome]="Advice","PAF Assist",IIf([lnkContact_MI_Part1]![PAF_Outcome]="No PAF","Resolved at Frontline",IIf([lnkContact_MI_Part1]![PAF_Outcome]="Advice and Escalate" Or "Escalation" Or "Escalate as Customer Rejects Full Response" Or "Referred to Specialist Areas Escalation","PAF Accept"))))
HAVING (((lnkContact_MI_Part1.case_created_dateonly) Between "#01/10/2012#" And Date()-Weekday(Date())+IIf(Weekday(Date())<=1,0,7)-"6") AND ((IIf([lnkContact_MI_Part2]![Created_By_02]="Dan Houston","TIS",IIf([lnkContact_MI_Part2]![Created_By_02]="Servicing","Servicing",IIf([lnkContact_MI_Part2]![Created_By_02]="Choc","Cho",IIf([lnkContact_MI_Part2]![Created_By_01]="Bank" Or [lnkContact_MI_Part2]![Created_By_01]="N&W" Or [lnkContact_MI_Part2]![Created_By_01]="NW" Or [lnkContact_MI_Part2]![Created_By_01]="Apples" Or [lnkContact_MI_Part2]![Created_By_01]="LTSB" Or [lnkContact_MI_Part2]![Created_By_01]="S","Community Bank","Other")))))<>"Other") AND ((IIf([lnkContact_MI_Part1]![PAF_Outcome]="Other","Other",IIf([lnkContact_MI_Part1]![PAF_Outcome]="Advice","PAF Assist",IIf([lnkContact_MI_Part1]![PAF_Outcome]="No PAF","Resolved at Frontline",IIf([lnkContact_MI_Part1]![PAF_Outcome]="Advice and Escalate" Or "Escalation" Or "Escalate as Customer Rejects Full Response" Or "Referred to Specialist Areas Escalation","PAF Accept")))))<>"Other")) OR (((lnkContact_MI_Part1.case_created_dateonly) Between "#01/10/2012#" And Date()-Weekday(Date())+IIf(Weekday(Date())<=1,0,7)-"6") AND ((IIf([lnkContact_MI_Part2]![Created_By_02]="Dan Houston","TIS",IIf([lnkContact_MI_Part2]![Created_By_02]="Servicing","Servicing",IIf([lnkContact_MI_Part2]![Created_By_02]="Choc","Cho",IIf([lnkContact_MI_Part2]![Created_By_01]="Community Bank" Or [lnkContact_MI_Part2]![Created_By_01]="N&W" Or [lnkContact_MI_Part2]![Created_By_01]="NW" Or [lnkContact_MI_Part2]![Created_By_01]="Apples" Or [lnkContact_MI_Part2]![Created_By_01]="LTSB" Or [lnkContact_MI_Part2]![Created_By_01]="BoS","Community Bank","Other")))))<>"Other") AND ((IIf([lnkContact_MI_Part1]![PAF_Outcome]="Other","Other",IIf([lnkContact_MI_Part1]![PAF_Outcome]="Advice","PAF Assist",IIf([lnkContact_MI_Part1]![PAF_Outcome]="No PAF","Resolved at Frontline",IIf([lnkContact_MI_Part1]![PAF_Outcome]="Advice and Escalate" Or "Escalation" Or "Escalate as Customer Rejects Full Response" Or "Referred to Specialist Areas Escalation","PAF Accept")))))<>"Other"));

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
<ok one question if post the sql can u delete it after viewing it.. >
Yes, but then the question becomes valueless for any other experts trying to help.

The SQL only contains you object names so no real "data is exposed...

<need to do pass through as all the data sits in sql server 2008... >
Then why is Access even  needed here?, ...just run this on the SQL server... then use it as a "view" in SQL Server that you can link to in Access.

...You could also try linking the SQL tables into Access then create this query in Access...

...However in viewing your SQL, my guess is that it is just too complex for Access.

So again, just try running this query in SQL Server directly. If it does not work in SQL Server, it probably will not work in Access...

JeffCoachman
0

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:
jeffcoachman

thanks for the feedback....funnily

other select qry work.fine...I can read the linked tables fine...only one odbc connection....its just this qry...

was my odbc vna code fine??
0
Jeffrey CoachmanMIS LiasonCommented:
So again, I think it is just a matter of the complexity.
Access "says" in can support SQL statements of up to 64,000 characters (yours is about 5,000)
But I suspect that is for a basic query.
Your query contains more IIF statements than I have ever seen in a query.
So again, this my be more a matter of "complexity, ...rather than the number of characters...

Did you try running this form SQL server directly?
0
route217JuniorAuthor Commented:
hi jeffcoachman

it works fine directly from sql....2008
0
Jeffrey CoachmanMIS LiasonCommented:
Then my suspicion is that it is the complexity of the query that is causing this...
0
Jeffrey CoachmanMIS LiasonCommented:
OK, but unless my post you accepted actually helped you in getting the issue resolved, you were under no obligation to accept it...

So what was the final resolution here?
0
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.