×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Run-time error '3151': ODBC Question

Excel Run-time error '3151': ODBC Question

Excel Run-time error '3151': ODBC Question

(OP)
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?

RE: Excel Run-time error '3151': ODBC Question

Hi,

Quote:

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

This is an incorrect assumption.

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Run-time error '3151': ODBC Question

(OP)
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"

RE: Excel Run-time error '3151': ODBC Question

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Run-time error '3151': ODBC Question

(OP)
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.

RE: Excel Run-time error '3151': ODBC Question

(OP)
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.

RE: Excel Run-time error '3151': ODBC Question

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

RE: Excel Run-time error '3151': ODBC Question

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Run-time error '3151': ODBC Question

(OP)
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.

RE: Excel Run-time error '3151': ODBC Question

(OP)
Thanks for the link to the additional resources.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close