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

  • Thread starter Thread starter Jean
  • Start date Start date
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
 
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
 
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
 
Back
Top