When calling OpenRecordSet(), ODBC Call Failed, 3146 error appears

J

Jean

Hi,

Below is our code, we are running MSSQL 2000 with SP2, MSACCESS 2002. This application is converted from MSACCESS 97/MSSQL 7.0.

We ran into the error "ODBC Call Failed, 3146" at the last line of the below statement, and don't know what caused the error. The select query "qryTotalBatchAmount" ran just fine if we executed in Query design, but not inside the code.

=======================================
Dim varReturn As Variant
Dim rs As Recordset
Dim qdf As QueryDef

If IsNull(Me.cboBatchID) Then
MsgBox "You Must Select a Batch to Proceed", vbCritical, "Select a Batch"
Exit Sub
End If

Set qdf = gdb1.QueryDefs("qryMemberPostedPaymentsCount")
qdf.Parameters("IDX") = Me.cboBatchID
Set rs = qdf.OpenRecordset()

If rs!CountIDX > 0 Then
MsgBox "Records for this batch have previously been posted." _
& vbCrLf & "Please Contact Your System Administrator!", _
vbCritical, "Process Aborted!!"
GoTo cmdPostExit
End If

Set qdf = gdb1.QueryDefs("qryTotalBatchAmount")
qdf.Parameters("lngBatchID") = Me.cboBatchID
Set rs = qdf.OpenRecordset()

===============================================

Any suggestion is appreciated.

Thanks

Jean
 
R

Ron Weiner

Try changing the dim to unambigiously use the DAO library:

Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

and just before:

Set qdf = gdb1.QueryDefs("qryTotalBatchAmount")
qdf.Parameters("lngBatchID") = Me.cboBatchID
Set rs = qdf.OpenRecordset()

add:

rs.close
set qdf = nothing
set rs = nothing

to explicitly close and un-set the qdf and rs variables before attempting to use them again. Hope this helps!


Ron W
Hi,

Below is our code, we are running MSSQL 2000 with SP2, MSACCESS 2002. This application is converted from MSACCESS 97/MSSQL 7.0.

We ran into the error "ODBC Call Failed, 3146" at the last line of the below statement, and don't know what caused the error. The select query "qryTotalBatchAmount" ran just fine if we executed in Query design, but not inside the code.

=======================================
Dim varReturn As Variant
Dim rs As Recordset
Dim qdf As QueryDef

If IsNull(Me.cboBatchID) Then
MsgBox "You Must Select a Batch to Proceed", vbCritical, "Select a Batch"
Exit Sub
End If

Set qdf = gdb1.QueryDefs("qryMemberPostedPaymentsCount")
qdf.Parameters("IDX") = Me.cboBatchID
Set rs = qdf.OpenRecordset()

If rs!CountIDX > 0 Then
MsgBox "Records for this batch have previously been posted." _
& vbCrLf & "Please Contact Your System Administrator!", _
vbCritical, "Process Aborted!!"
GoTo cmdPostExit
End If

Set qdf = gdb1.QueryDefs("qryTotalBatchAmount")
qdf.Parameters("lngBatchID") = Me.cboBatchID
Set rs = qdf.OpenRecordset()

===============================================

Any suggestion is appreciated.

Thanks

Jean
 
J

Jean

Thanks Ron. I discovered the cause of my ODBC Call fail. It was due to a dead lock. I will post my question again under another subjects.

Thanks

Jean
Try changing the dim to unambigiously use the DAO library:

Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

and just before:

Set qdf = gdb1.QueryDefs("qryTotalBatchAmount")
qdf.Parameters("lngBatchID") = Me.cboBatchID
Set rs = qdf.OpenRecordset()

add:

rs.close
set qdf = nothing
set rs = nothing

to explicitly close and un-set the qdf and rs variables before attempting to use them again. Hope this helps!


Ron W
Hi,

Below is our code, we are running MSSQL 2000 with SP2, MSACCESS 2002. This application is converted from MSACCESS 97/MSSQL 7.0.

We ran into the error "ODBC Call Failed, 3146" at the last line of the below statement, and don't know what caused the error. The select query "qryTotalBatchAmount" ran just fine if we executed in Query design, but not inside the code.

=======================================
Dim varReturn As Variant
Dim rs As Recordset
Dim qdf As QueryDef

If IsNull(Me.cboBatchID) Then
MsgBox "You Must Select a Batch to Proceed", vbCritical, "Select a Batch"
Exit Sub
End If

Set qdf = gdb1.QueryDefs("qryMemberPostedPaymentsCount")
qdf.Parameters("IDX") = Me.cboBatchID
Set rs = qdf.OpenRecordset()

If rs!CountIDX > 0 Then
MsgBox "Records for this batch have previously been posted." _
& vbCrLf & "Please Contact Your System Administrator!", _
vbCritical, "Process Aborted!!"
GoTo cmdPostExit
End If

Set qdf = gdb1.QueryDefs("qryTotalBatchAmount")
qdf.Parameters("lngBatchID") = Me.cboBatchID
Set rs = qdf.OpenRecordset()

===============================================

Any suggestion is appreciated.

Thanks

Jean
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-Time Error 3146 ODBC Call Failed 3
ODBC call failed : Error # 3146 6
VBA error calling parameter query 2
Run-time error 3146 ODBC--call failed 4
"Data type mismatch" error 4
Table Update ODBC 2
Run Time error 3146 - ODBC Call Failed 0
run time 3146 (odbc call failed) on second pass 6

Top