run time error 3146 odbc call failed

[Tupacmoche]

Hi All,

I have this run time error that is strange. I say this because the error is occurring in my development environment but not in my production environment and the code is exactly the same. As, I add new features, I move this copy into the production folder for users to use. In fact, I have backup copies that work fine which is true of the production version and the development version. Here is the code has anyone seen this type of error and know how to fix it and what causes it? Also, I can't just use a copy of the dev backup since they are not exactly in sync with changes that I have made. I should also mention that they are running in the exact same environment and machine. Everything is the same for both versions.

Private Sub btnCS_Click()

Dim strSQL As String
Dim strCn As String

'***********VALIDATES THAT FOUR REQUIRED FIELDS ARE ENTERED TO CREATE SCHEDULE********************************'
If ValPaySch() = False Then
MsgBox "You must fill in all four required values to create a schedule!.", vbRetryCancel + vbExclamation
Exit Sub
Else
End If
'*************************************************************************************************************'

strCn = "ODBC;Description=BIRWCDCPVM001;DRIVER=SQL Server;SERVER=BIRWCDCPVM001;Trusted_Connection=Yes;APP=2007 Microsoft Office system;"

strSQL = "usp_EgatePledgeSchedule " & Me.PledgeAmount.Value & ", " & Me.txt_strHMP & ", '" & _
Format(Me.txt_strSD, "yyyy-mm-dd") & "', '" & Me.txt_strFreq.Value & "', " & Me.GiftID & ";"

With CurrentDb.CreateQueryDef(vbNullString)
.Connect = strCn
.SQL = strSQL
.ReturnsRecords = False
.Execute dbFailOnError
End With

Me.tblPledgePayments_subform.Requery
MsgBox ("Pledge Schedule added!")

Me.btnCS.Enabled = False

Call ClearTxtBoxes

End Sub:banghead:

 

[JHB]

At which code line does the error occur?

 

[Cronk]

No password?

 

[Tupacmoche]

No pw and the error msg is the generic Access error. There is no line indicator.

 

[MajP]

strSQL = "usp_EgatePledgeSchedule " & Me.PledgeAmount.Value & ", " & Me.txt_strHMP & ", '" & _
Format(Me.txt_strSD, "yyyy-mm-dd") & "', '" & Me.txt_strFreq.Value & "', " & Me.GiftID & ";

That does not look like any SQL string, I have ever seen. Can you debug.print the SQL and see what that resolves to?

 

[Tupacmoche]

Please note that is works in the productions environment but all of a sudden stopped working in the dev environment. The code is exactly the same. In fact, I copied the code from productions and pasted it into the dev environment and it still gives me the same error.

 

[Tupacmoche]

Tried it again and there is a specific spot where it fails. At 'Execute dbFailOnError'

 

[JHB]

Have you tried it on another computer, (not one from the production but another one in the office)?
Should that be a text, (it is treated as a number)? Me.txt_strHMP

 

[The_Doc_Man]

I presume that you are launching a stored procedure named usp_EgatePledgeSchedule and following it with actual arguments to the procedure.

If that is a stored procedure launch (which is really all that it could be), the question then comes down to whether the dev environment contains exactly the same stored procedures as the prod environment, and whether the session settings between your PC and the dev environment are the same as the settings between your PC and the prod environment.

 

[Tupacmoche]

I appreciate everyone's effort to id the issue but all of a sudden the same error message started to occur in the production version. I don't know why but the code is not at issue since I made a new Access DB and put the code into it and it worked just fine. Even through it did work fine I decided to change the data connection to the more generic currentdb() which will save me time and headache in the future should the connection string change. Here it is:

Set qdef = CurrentDb.CreateQueryDef(vbNullString)
qdef.Connect = CurrentDb.TableDefs("dbo_tblPledgePayments").Connect

qdef.SQL = "Exec MED.[dbo].usp_EgatePledgeSchedule " & Me.PledgeAmount.Value & ", " & Me.txt_strHMP & ", '" & _
Format(Me.txt_strSD, "yyyy-mm-dd") & "', '" & Me.txt_strFreq.Value & "', " & Me.GiftID & ";"

qdef.ReturnsRecords = False
qdef.Execute
qdef.Close

The only other recommendation, I can ask about is that this application is being used by about 25 user simultaneously and it is running from a shared network drive. Additionally, at least 5 of those user are accessing it from MAC
remoting into a Citrix server. Is this good practice or should this be done differently?

 

[JHB]

Is Me.txt_strHMP at text value?