ODBC call failed message with pass through query (1 Viewer)

pbuethe

Returning User
Joined
Apr 9, 2002
Messages
199
In the following code I am getting the error “ODBC – call failed” error 3146, on the line
Set rstCaseInfoFiltered = qdfCurr.OpenRecordset

Code:
Function basPreFill(strSample As String, strFacility As String, strCaseID As String)
Dim strWSFormName As String, strRationaleName As String, strSQL  As String
Dim AcroApp As Acrobat.CAcroApp
Dim theWSForm As Acrobat.CAcroPDDoc, theRationale As Acrobat.CAcroPDDoc
Dim rstCaseInfoFiltered As DAO.Recordset
Dim jso As Object
Dim strfilename As String
Dim strFieldName As String
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim i As Integer
Dim SNotifyDate As String, DxField As String, ProcField As String


strWSFormName = conMainFolder & strSample & "\" & strFacility & "\" & strCaseID & "\" & strCaseID & "_Worksheet.pdf"
Set AcroApp = CreateObject("AcroExch.App")
Set theWSForm = CreateObject("AcroExch.PDDoc")

If Dir(strWSFormName) <> "" Then
 MsgBox strWSFormName & " already exists. "
 Exit Function
End If

strfilename = "V:\Master_DB\eChart\Worksheet Template\011_NYSCAID Retrospective Review Worksheet_2018-08-01_R3Enable.pdf"
If theWSForm.Open(strfilename) = False Then
 Debug.Print strfilename & " not found"
 Exit Function
End If

' changed to pass through query for rstCaseInfoFiltered to speed up - P. Buethe 8/7/2019
Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.CreateQueryDef("")

strSQL = "SELECT dbo.tblWkshtHeader.CaseNbr, dbo.tblWkshtHeader.ReviewSite, " & vbCrLf
strSQL = strSQL & " dbo.tblWkshtHeader.Flag1, dbo.tblWkshtHeader.Flag2, dbo.tblWkshtHeader.Flag3," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtHeader.SampleNbr, dbo.tblWkshtHeader.County," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtHeader.NMonth, dbo.tblWkshtHeader.NDay, " & vbCrLf
strSQL = strSQL & " dbo.tblWkshtHeader.NYear, dbo.tblWkshtHeader.Provider," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtHeader.PatientName, dbo.tblWkshtHeader.MedRecNbr," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtHeader.MedicaidID, dbo.tblWkshtHeader.AdmitDate," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtHeader.DischDate, dbo.tblWkshtHeader.Sex," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtHeader.LOS, dbo.tblWkshtHeader.DOB, dbo.tblWkshtHeader.Age," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtHeader.BirthWeight, dbo.tblWkshtHeader.SequenceNbr," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtHeader.DRG, dbo.tblWkshtHeader.DRGDesc," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtHeader.DRGSOI, dbo.tblWkshtDRG.LowTrim," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.HighTrim, dbo.tblWkshtDRG.SIW, dbo.tblWkshtDRG.Dx1," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Dx2, dbo.tblWkshtDRG.Dx3, dbo.tblWkshtDRG.Dx4," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Dx5, dbo.tblWkshtDRG.Dx6, dbo.tblWkshtDRG.Dx7," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Dx8, dbo.tblWkshtDRG.Dx9, dbo.tblWkshtDRG.Dx10," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Dx11, dbo.tblWkshtDRG.Dx12, dbo.tblWkshtDRG.Dx13," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Dx14, dbo.tblWkshtDRG.Dx15, dbo.tblWkshtDRG.Dx16," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Dx17, dbo.tblWkshtDRG.Dx18, dbo.tblWkshtDRG.Dx19," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Dx20, dbo.tblWkshtDRG.Dx21, dbo.tblWkshtDRG.Dx22," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Dx23, dbo.tblWkshtDRG.Dx24, dbo.tblWkshtDRG.Dx25," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc1, dbo.tblWkshtDRG.DxDesc2," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc3, dbo.tblWkshtDRG.DxDesc4," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc5, dbo.tblWkshtDRG.DxDesc6," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc7, dbo.tblWkshtDRG.DxDesc8," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc9, dbo.tblWkshtDRG.DxDesc10," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc11, dbo.tblWkshtDRG.DxDesc12," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc13, dbo.tblWkshtDRG.DxDesc14," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc15, dbo.tblWkshtDRG.DxDesc16," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc17, dbo.tblWkshtDRG.DxDesc18," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc19, dbo.tblWkshtDRG.DxDesc20," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc21, dbo.tblWkshtDRG.DxDesc22," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc23, dbo.tblWkshtDRG.DxDesc24," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DxDesc25, dbo.tblWkshtDRG.Proc1, dbo.tblWkshtDRG.Proc2," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Proc3, dbo.tblWkshtDRG.Proc4, dbo.tblWkshtDRG.Proc5," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Proc6, dbo.tblWkshtDRG.Proc7, dbo.tblWkshtDRG.Proc8," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Proc9, dbo.tblWkshtDRG.Proc10, dbo.tblWkshtDRG.Proc11," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Proc12, dbo.tblWkshtDRG.Proc13, dbo.tblWkshtDRG.Proc14," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Proc15, dbo.tblWkshtDRG.Proc16, dbo.tblWkshtDRG.Proc17," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Proc18, dbo.tblWkshtDRG.Proc19, dbo.tblWkshtDRG.Proc20," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Proc21, dbo.tblWkshtDRG.Proc22, dbo.tblWkshtDRG.Proc23," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Proc24, dbo.tblWkshtDRG.Proc25, dbo.tblWkshtDRG.ProcDesc1," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.ProcDesc2, dbo.tblWkshtDRG.ProcDesc3," & vbCrLf
strSQL = strSQL & "dbo.tblWkshtDRG.ProcDesc4, dbo.tblWkshtDRG.ProcDesc5," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.ProcDesc6, dbo.tblWkshtDRG.ProcDesc7," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.ProcDesc8, dbo.tblWkshtDRG.ProcDesc9," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.ProcDesc10, dbo.tblWkshtDRG.ProcDesc11," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.ProcDesc12, dbo.tblWkshtDRG.ProcDesc13," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.ProcDesc14, dbo.tblWkshtDRG.ProcDesc15," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.ProcDesc16, dbo.tblWkshtDRG.ProcDesc17," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.ProcDesc18, dbo.tblWkshtDRG.ProcDesc19," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.ProcDesc20, dbo.tblWkshtDRG.ProcDesc21," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.ProcDesc22, dbo.tblWkshtDRG.ProcDesc23," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.ProcDesc24, dbo.tblWkshtDRG.ProcDesc25," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.DrgROM, dbo.tblDiagSOI.SOI1, dbo.tblDiagSOI.SOI2," & vbCrLf
strSQL = strSQL & " dbo.tblDiagSOI.SOI3, dbo.tblDiagSOI.SOI4, dbo.tblDiagSOI.SOI5," & vbCrLf
strSQL = strSQL & " dbo.tblDiagSOI.SOI6, dbo.tblDiagSOI.SOI7, dbo.tblDiagSOI.SOI8," & vbCrLf
strSQL = strSQL & " dbo.tblDiagSOI.SOI9, dbo.tblDiagSOI.SOI10, dbo.tblDiagSOI.SOI11," & vbCrLf
strSQL = strSQL & " dbo.tblDiagSOI.SOI12, dbo.tblDiagSOI.SOI13, dbo.tblDiagSOI.SOI14," & vbCrLf
strSQL = strSQL & " dbo.tblDiagSOI.SOI15, dbo.tblDiagSOI.SOI16, dbo.tblDiagSOI.SOI17," & vbCrLf
strSQL = strSQL & " dbo.tblDiagSOI.SOI18, dbo.tblDiagSOI.SOI19, dbo.tblDiagSOI.SOI20," & vbCrLf
strSQL = strSQL & " dbo.tblDiagSOI.SOI21, dbo.tblDiagSOI.SOI22, dbo.tblDiagSOI.SOI23," & vbCrLf
strSQL = strSQL & " dbo.tblDiagSOI.SOI24, dbo.tblDiagSOI.SOI25, dbo.tblWkshtDRG.POA1," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.POA2, dbo.tblWkshtDRG.POA3, dbo.tblWkshtDRG.POA4," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.POA5, dbo.tblWkshtDRG.POA6, dbo.tblWkshtDRG.POA7," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.POA8, dbo.tblWkshtDRG.POA9, dbo.tblWkshtDRG.POA10," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.POA11, dbo.tblWkshtDRG.POA12, dbo.tblWkshtDRG.POA13," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.POA14, dbo.tblWkshtDRG.POA15, dbo.tblWkshtDRG.POA16," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.POA17, dbo.tblWkshtDRG.POA18, dbo.tblWkshtDRG.POA19," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.POA20, dbo.tblWkshtDRG.POA21, dbo.tblWkshtDRG.POA22," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.POA23, dbo.tblWkshtDRG.POA24, dbo.tblWkshtDRG.POA25," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtDRG.Dispo, dbo.tblWkshtDRG.DispoDesc, dbo.tblWkshtUtil.ClaimRef," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtUtil.StartDate, dbo.tblWkshtUtil.EndDate, dbo.tblWkshtUtil.RC," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtUtil.PayAmt, dbo.tblWkshtFlag.Admission AS AS1," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtFlag.LOS AS AS2, dbo.tblWkshtFlag.DRG AS AS3," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtFlag.Discharge AS AS4, dbo.tblWkshtFlag.NYPORTS AS AS5," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtFlag.Quality AS AS6, dbo.tblWkshtFlag.Documentation AS AS7," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtFlag.ALC AS AS8, dbo.tblWkshtFlag.Transfer AS AS9," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtFlag.Mortality AS AS10, dbo.tblWkshtFlag.Complications AS AS11," & vbCrLf
strSQL = strSQL & " dbo.tblWkshtFlag.SAE AS AS12, dbo.tblWkshtFlag.CostOutlier AS AS13" & vbCrLf
strSQL = strSQL & " FROM (((dbo.tblWkshtHeader INNER JOIN dbo.tblWkshtUtil " & vbCrLf
strSQL = strSQL & " ON dbo.tblWkshtHeader.CaseNbr = dbo.tblWkshtUtil.CaseNbr)" & vbCrLf
strSQL = strSQL & " INNER JOIN dbo.tblWkshtDRG" & vbCrLf
strSQL = strSQL & " ON dbo.tblWkshtHeader.CaseNbr = dbo.tblWkshtDRG.CaseNbr)" & vbCrLf
strSQL = strSQL & " INNER JOIN dbo.tblDiagSOI" & vbCrLf
strSQL = strSQL & " ON dbo.tblWkshtHeader.CaseNbr = dbo.tblDiagSOI.CaseNbr)" & vbCrLf
strSQL = strSQL & " INNER JOIN dbo.tblWkshtFlag" & vbCrLf
strSQL = strSQL & " ON dbo.tblWkshtHeader.CaseNbr = dbo.tblWkshtFlag.CaseNbr" & vbCrLf
strSQL = strSQL & " WHERE CaseNbr = '" & strCaseID & "';"

qdfCurr.Connect = "ODBC;Description=Linkage to ChartCaidRetro;DRIVER=SQL Server;SERVER=AIMS-SQLSVR;Trusted_Connection=Yes;DATABASE=ChartCaidRetro;"
qdfCurr.SQL = strSQL
qdfCurr.ReturnsRecords = True
Set rstCaseInfoFiltered = qdfCurr.OpenRecordset
If rstCaseInfoFiltered.RecordCount <> 1 Then
 'Debug.Print "Check case ID " & strCaseID
 Exit Function
End If
I had a previous version of this code which used a stored Access query instead of building the strSQL. That was working but was very slow. I am trying to use a passthrough query in order to speed it up.

Another factor which may or may not have anything to do with this is that we have recently switched from Windows 7 to Windows 10.

Thanks for your assistance.
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,326
I'm not at my computer to check at the moment but for sql server recordsets, I think you need to add DbSeeChanges as an argument.
 

pbuethe

Returning User
Joined
Apr 9, 2002
Messages
199
I changed the line to:
Set rstCaseInfoFiltered = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

and got run-time error 3135 - syntax error in JOIN operation.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,631
I changed the line to:
Set rstCaseInfoFiltered = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

and got run-time error 3135 - syntax error in JOIN operation.
My guess is you'll now have to "fix" your SQL statement. If you copy and paste it in SSMS, does it work?
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,326
I agree.
Or add a debug.print strsql line then paste into a blank query and it should identify the section which needs attention
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,533
I'm still looking at the SQL and nothing jumps out, but for what it is worth, I doubt that the switch from Win7 to Win10 is significant. A change of Access version WOULD have made a difference, perhaps, but if it is the same version of Access, you are using the same .DLL files in either case. So on that narrow question, don't worry about Windows version for now. The odds favor that this problem is not unrelated to Windows itself.
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,326
I'm grasping at straws but possibly try changing to this

Code:
...
strSQL = strSQL & " FROM [B][COLOR="Red"]([/COLOR][/B](((dbo.tblWkshtHeader INNER JOIN dbo.tblWkshtUtil " & vbCrLf
strSQL = strSQL & " ON dbo.tblWkshtHeader.CaseNbr = dbo.tblWkshtUtil.CaseNbr)" & vbCrLf
strSQL = strSQL & " INNER JOIN dbo.tblWkshtDRG" & vbCrLf
strSQL = strSQL & " ON dbo.tblWkshtHeader.CaseNbr = dbo.tblWkshtDRG.CaseNbr)" & vbCrLf
strSQL = strSQL & " INNER JOIN dbo.tblDiagSOI" & vbCrLf
strSQL = strSQL & " ON dbo.tblWkshtHeader.CaseNbr = dbo.tblDiagSOI.CaseNbr)" & vbCrLf
strSQL = strSQL & " INNER JOIN dbo.tblWkshtFlag" & vbCrLf
strSQL = strSQL & " ON dbo.tblWkshtHeader.CaseNbr = dbo.tblWkshtFlag.CaseNbr[B][B][COLOR="red"])[/COLOR][/B][/B]" & vbCrLf
...
 

pbuethe

Returning User
Joined
Apr 9, 2002
Messages
199
I tried Colin's suggestion but got the same error message - syntax error in JOIN operation.
 

pbuethe

Returning User
Joined
Apr 9, 2002
Messages
199
I believe I do not have SSMS, since I don't have rights to use SQL Server directly. I have only Access.

Is there a way I can use my stored query in/as a pass through query?
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,631
I believe I do not have SSMS, since I don't have rights to use SQL Server directly. I have only Access.

Is there a way I can use my stored query in/as a pass through query?
Okay, I looked at your SQL statement and do not see any real reason why it's a passthrough, so Colin's idea should work. Do a Debug.Print and copy and paste the result into the Query Designer in Access and fix any issues until it works. Then, you can use the new SQL statement in your code, and it should work.
 

sonic8

AWF VIP
Joined
Oct 27, 2015
Messages
322
I believe I do not have SSMS, since I don't have rights to use SQL Server directly. I have only Access.

Is there a way I can use my stored query in/as a pass through query?

Check the DbEngine.Errors collection if there is more helpful info there.


Yes, you can paste the complete SQL into an Pass-Through-Query window and execute it there.
 

pbuethe

Returning User
Joined
Apr 9, 2002
Messages
199
Check the DbEngine.Errors collection if there is more helpful info there.


Yes, you can paste the complete SQL into an Pass-Through-Query window and execute it there.
Either way, it still says "Syntax error in JOIN operation" and does not say where the error is.
 

bastanu

Registered User
Joined
Apr 13, 2010
Messages
273
Why not try to create a view in SQL Server and link that in your Access front-end like any other SQL table. I think your problem is caused by the SQL statement which looks like Access SQL; for a pass-through you need to use the syntax of the server (I believe most SQL views use JOIN instead of INNER JOIN as INNER is optional in T-SQL).
If you do not have access to SSMS but you know that your account has CREATE rights you can use a pass-trough query to create your view (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15).

Cheers,
Vlad
 

sonic8

AWF VIP
Joined
Oct 27, 2015
Messages
322
Either way, it still says "Syntax error in JOIN operation" and does not say where the error is.
I don't see the error.

What I would do next:
1. Remove all those parentheses from the FROM clause. - Mainly because I hate them and would love to see them being the culprit. ;-)
(You don't need them in a Pass-Through-Query)
2. Add a table reference to CaseNbr in the where condition to make that unambiguous. (This will be the next error after the current is fixed).

The actual error will probably persist after the above measures...

For finding the error:
Copy the statement into a Pass-Through-Query,
replace the complete select column list with *,
remove all but the first table from the from clause,
run the query - it should work,
add the first joined table to the from clause,
run the query - does it still work?
- if yes, add the next joined table and repeat
- if not, try to spot the error or paste that state of the SQL here.
 

pbuethe

Returning User
Joined
Apr 9, 2002
Messages
199
I don't see the error.

What I would do next:
1. Remove all those parentheses from the FROM clause. - Mainly because I hate them and would love to see them being the culprit. ;-)
(You don't need them in a Pass-Through-Query)
2. Add a table reference to CaseNbr in the where condition to make that unambiguous. (This will be the next error after the current is fixed).

The actual error will probably persist after the above measures...

For finding the error:
Copy the statement into a Pass-Through-Query,
replace the complete select column list with *,
remove all but the first table from the from clause,
run the query - it should work,
add the first joined table to the from clause,
run the query - does it still work?
- if yes, add the next joined table and repeat
- if not, try to spot the error or paste that state of the SQL here.
OK, I have a pass-through query that works now, basically by following your first two steps! But it is working with a hard coded CaseNbr. How do I use it as a parameter in the pass-through query? How do I run the pass-through query in code?
 

sonic8

AWF VIP
Joined
Oct 27, 2015
Messages
322
OK, I have a pass-through query that works now, basically by following your first two steps! But it is working with a hard coded CaseNbr. How do I use it as a parameter in the pass-through query? How do I run the pass-through query in code?
You're kidding me!? By removing the parentheses? :)

Ok, the rest is easy. Go back to your original code and repeat those two steps there. - Then it should work.

You might also need to apply the change re dbOpenDynaset + dbSeeChanges.
 
Last edited:

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,326
Pbeuthe
Re brackets...
I think you've just made Phillip a happy man :D
 

pbuethe

Returning User
Joined
Apr 9, 2002
Messages
199
You're kidding me!? By removing the parentheses? :)

Ok, the rest is easy. Go back to your original code and repeat those two steps there. - Then it should work.

You might also need to apply the change re dbOpenDynaset + dbSeeChanges.
That did the trick! I did not need dbOpenDynaset + dbSeeChanges. It is FAST!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom