Excel Run-time error '3151': ODBC Question

[kc27]

Hello

I have an Excel xlsm file in which users can pick options for a report, then generate a worksheet that includes data pulled from a database. When the user first attempts to generate a report (worksheet), the worksheet starts to load with some hard coded values, then should load with data from the database. Instead, the user sees this message:

Run-time error '3151':
ODBC-connection to 'SQL.Servermid.sqldb.prod.ton.com' failed.

This is the debug code that gets highlighted in a module called A_Func
Set RSBN = DBM.OpenRecordset("SELECT BN_ID, BN_NAME FROM dbo_BASE_NODE WHERE HN_RID =" & HN_RID, dbOpenSnapshot)

If the user closes the failed worksheet, goes back to the report option selection screen, makes their selections, the report will successfully complete.

The error began happeining after code in a module further down in the vba module hierarchy was modified. I am assuming the code processes modules in the order they appear in the VBA tree. So am not sure why the connection fails when none of the code in the A_Func module (the one that displays the highlighted code that failed) was modified.

Does anyone have any ideas on why the report creation errors the first time, but runs the second time? And where one would look to fix the problem?

 

[SkipVought]

Hi,

I am assuming the code processes modules in the order they appear in the VBA tree.

This is an incorrect assumption.

If the user closes the failed worksheet...

A worksheet cannot be closed. ???

Do you mean a workbook?

So what code was modified? What was the code before and after the modification?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

Here is an excerpt of the PL6M module that has the changed code in bold. The code in bold was edited to remove a formula. The first section is the code prior to editing, the second section is after the edits were made.

You are correct, it generates a workbook you can save.

Also, this problem cannot be reproduced every time. I can put in the same criteria 5 times and it will generate a report successfully 1 or 2 times, and fail the others. The times that it fails, if you try again without closing the xlsm file, it will create the report.


Prior to change

'Net MD
tr = 129
With WSP.Range(WSP.Cells(tr, MCOL1), WSP.Cells(tr, MCOL1 + 8)): .FormulaR1C1 = "=R" & r_gmdB1 & "C-R" & r_mfaB1 & "C-R" & r_mcxB1 & "C": .NumberFormat = "#,##0": End With: r_nmkdB1 = tr
With WSP.Range(WSP.Cells(tr + 1, MCOL1), WSP.Cells(tr + 1, MCOL1 + 8)): .FormulaR1C1 = "=R" & r_gmdB2 & "C-R" & r_mfaB2 & "C-R" & r_mcxB2 & "C": .NumberFormat = "#,##0": End With: r_nmkdB2 = tr + 1
With WSP.Range(WSP.Cells(tr + 2, MCOL1), WSP.Cells(tr + 2, MCOL1 + 8)): .FormulaR1C1 = "=R" & r_gmdB3 & "C-R" & r_mfaB3 & "C-R" & r_mcxB3 & "C": .NumberFormat = "#,##0": End With: r_nmkdB3 = tr + 2
With WSP.Range(WSP.Cells(tr + 3, MCOL1), WSP.Cells(tr + 3, MCOL1 + 8)): .FormulaR1C1 = "=R" & r_gmdB4 & "C-R" & r_mfaB4 & "C-R" & r_mcxB4 & "C": .NumberFormat = "#,##0": End With: r_nmkdB4 = tr + 3
P6_CalcRow tr, "qsum", "#,##0"
P6_CalcRow tr + 1, "qsum", "#,##0"
P6_CalcRow tr + 2, "qsum", "#,##0"
P6_CalcRow tr + 3, "qsum", "#,##0"


After the change:

'Net MD
tr = 129
With WSP.Range(WSP.Cells(tr, MCOL1), WSP.Cells(tr, MCOL1 + 8)): .FormulaR1C1 = "=R" & r_gmdB1 & "C-R" & r_mfaB1 & "C": .NumberFormat = "#,##0": End With: r_nmkdB1 = tr
With WSP.Range(WSP.Cells(tr + 1, MCOL1), WSP.Cells(tr + 1, MCOL1 + 8)): .FormulaR1C1 = "=R" & r_gmdB2 & "C-R" & r_mfaB2 & "C": .NumberFormat = "#,##0": End With: r_nmkdB2 = tr + 1
With WSP.Range(WSP.Cells(tr + 2, MCOL1), WSP.Cells(tr + 2, MCOL1 + 8)): .FormulaR1C1 = "=R" & r_gmdB3 & "C-R" & r_mfaB3 & "C": .NumberFormat = "#,##0": End With: r_nmkdB3 = tr + 2
With WSP.Range(WSP.Cells(tr + 3, MCOL1), WSP.Cells(tr + 3, MCOL1 + 8)): .FormulaR1C1 = "=R" & r_gmdB4 & "C-R" & r_mfaB4 & "C": .NumberFormat = "#,##0": End With: r_nmkdB4 = tr + 3
P6_CalcRow tr, "qsum", "#,##0"
P6_CalcRow tr + 1, "qsum", "#,##0"
P6_CalcRow tr + 2, "qsum", "#,##0"
P6_CalcRow tr + 3, "qsum", "#,##0

 

[SkipVought]

Well this tells me nothing. Four formulas changed.

How did they change?

How would these changes affect the creation of a connection for a query?

There must be some relationship.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

I think something changed on our network, and that is causing the error. I was helping the user who manages this form (and who made the code edits). I think the assumption was the edits resulted in the ODBC error. Tonight I went back to the original form, and got the same error, too. Not every time, but I did get it.

There are many users that utilize this xlsm file. They are still using the original version. I am going to suggest to the person that made the edits, that they poll those users, and see if the users have been getting the ODBC error in the past few days with the original version of the form.

I will update this thread with what I learn.

 

[kc27]

Tested again this morning and it is back to the previous version working, and the edited version getting the ODBC error. Not sure what the relationship of the edited formula -R" & r_mcxB1 & "C is to the server connection, but whatever it is, once if fails, on the second and subsequent tries, the connection is no longer a problem. If the user closes the Excel xlsm file, and starts again,then the form will fail again the first time. Subsequent runs will be successful.

 

[DjangMan]

Are the cells that the formulas are updating affecting the SQL query? e.g. are they affecting a WHERE clause, for example?

 

[SkipVought]

Why not upload your workbook. Need to see all the code and try to relate the formula results with the process.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

I would like to upload the workbook. It has a lot of hard coded company info, so I am reluctant to do so. To make this even more fun, we had a few other users test today, and they did not get the ODBC error message. Will have to take a closer look at those user's PCs/Excel to see if something jumps out. Possibly a problem between versions of Office? Our organization is transitioning from Office 2013 to Office 2016. So some users have Office 2013, some Office 2016.

 

[DjangMan]

This thread on SO discusses that same error message:

https://stackoverflow.com/questions/3787484/odbc-call-failed-error-3151

 

[kc27]

Thanks for the link to the additional resources.