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

pbuethe

pbuethe

Returning User
Local time
Today, 13:10
Joined
Apr 9, 2002
Messages
210
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.
 
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.
 
I changed the line to:
Set rstCaseInfoFiltered = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

and got run-time error 3135 - syntax error in JOIN operation.
 
pbuethe said:
I changed the line to:
Set rstCaseInfoFiltered = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

and got run-time error 3135 - syntax error in JOIN operation.
Click to expand...
My guess is you'll now have to "fix" your SQL statement. If you copy and paste it in SSMS, does it work?
 
I agree.
Or add a debug.print strsql line then paste into a blank query and it should identify the section which needs attention
 
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.
 
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
...
 
I tried Colin's suggestion but got the same error message - syntax error in JOIN operation.
 
pbuethe said:
I tried Colin's suggestion but got the same error message - syntax error in JOIN operation.
Click to expand...
Hi. Have you tried using SSMS to help troubleshoot the SQL syntax for you?
 
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?
 
pbuethe said:
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?
Click to expand...
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.
 
pbuethe said:
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?
Click to expand...


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.
 
sonic8 said:
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.
Click to expand...

Either way, it still says "Syntax error in JOIN operation" and does not say where the error is.
 
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
 
pbuethe said:
Either way, it still says "Syntax error in JOIN operation" and does not say where the error is.
Click to expand...
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.
 
sonic8 said:
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.
Click to expand...

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?
 
pbuethe said:
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?
Click to expand...
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:
Pbeuthe
Re brackets...
I think you've just made Phillip a happy man :D
 
sonic8 said:
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.
Click to expand...

That did the trick! I did not need dbOpenDynaset + dbSeeChanges. It is FAST!
 
You must log in or register to reply here.

Similar threads

T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
378
CJ_London
C
C
Optional Variant in Inventory Control code
Replies
7
Views
411
gemma-the-husky
gemma-the-husky
N
Implementing Parameterized Queries in DAO for Enhanced Security in Access- SQL Server Application
Replies
12
Views
1,170
mdlueck
M
Gasman
Solved How does this work?
Replies
18
Views
838
jdraw
jdraw
E
Solved Listbox search not working with aggregated query
Replies
7
Views
483
elannesse
E

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom