Need Help Interpreting Excel VB Error Message 1

[kc27]

I have an Excel file that errors with the following Visual Basic error message:

Run-time error '-2147217900 (80040e14)':
[Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]Invalid column name 'P1424'.

The debug code excerpt is shown below. The last line in the code excerpt is highlighted when I click the error message's debug button. Is this error message telling me that the SQL db has an incorrect column name? This Excel file also utilizes Access dbs, but the error reads to me that the issue is with the SQl db, and not Access. Am I correct, or how do I verify this?

Thanks

'##################   THIS MODULE HAS BEEN UPDATED TO NEW SQL DATABASE #########################

Dim DBD As DAO.Database
Dim wr As Integer

Sub Test_C050R()
    Run_C050R 7, 1, 8, 5, "088", "NNNNYYY", False, "01/05/2015"
End Sub

Sub Run_C050R(tType, sType, Rspec, SOlevel, SOLevelVal, SOsubs, SOoutline, Optional weekEnd)
'everything is now stype=1

    Dim anaRow As Integer, dmmRow As Integer, buyRow As Integer, cpsRow As Integer
    Dim DBI As DAO.Database
    Dim RSA As DAO.Recordset, RSD As DAO.Recordset
    Dim RSDCV As New ADODB.Recordset
    Dim RANA As DAO.Recordset, RDMM As DAO.Recordset, RBUY As DAO.Recordset
    Select Case SOlevel
        Case 1
            xWhere = ""
        Case 2
            xWhere = "Rspec = '" & SOLevelVal & "'"
        Case 3
            xWhere = "DMM = '" & SOLevelVal & "'"
        Case 4
            xWhere = "Buyer = '" & SOLevelVal & "'"
        Case 5
            xWhere = "Dept = '" & SOLevelVal & "'"
        Case 6
            xWhere = "Dept = '" & Mid(SOLevelVal, 1, 3) & "'"
        Case 7
            xWhere = "Dept = '" & Mid(SOLevelVal, 1, 3) & "'"
        Case Else
            msg = "Report failed - Invalid SOLevel Value"
            GoTo Abort_C050R
    End Select
    CnRP.Open "DRIVER={SQL SERVER};SERVER=M598-repldb-1.internal.bntn.com;DATABASE=replenishment", "Repluser", "R3plu53r"
    storeGroup = "Total Company"

'prep a blank workbook
    Application.Workbooks.Add xlWorksheet
    Set WBR = Application.ActiveWorkbook
    Set WSR = WBR.ActiveSheet
    Application.Calculation = xlCalculationManual
    Application.ActiveWindow.Zoom = 75
    wr = 1
    cArray = Array(0, 0, 35, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8)
    
    tArray = Array("", "", "", "Curr Model Units", "Curr Onhand Units", "Curr SKULoc", "Curr StkOuts", "Avg Mdl/Sku Loc", "LW", _
                  "WK2", "WK3", "WK4", "WK5", "12w Avg", _
                  "26w Avg")
    For n = 0 To 14
        WSR.Cells(1, n + 1).ColumnWidth = cArray(n)
        WSR.Cells(4, n + 1) = tArray(n)
    Next n
    With WSR.Range(WSR.Cells(4, 1), WSR.Cells(4, 26))
        .WrapText = True
        .HorizontalAlignment = xlHAlignCenter
    End With
    Set DBI = OpenDatabase(DB_IMG1)
    Set RSD = DBI.OpenRecordset("SELECT * FROM CntlWeek", dbOpenDynaset)
'Get the time span
    If tType = 6 Then
        xFromDate = Date - 182
        xToDate = Date
    ElseIf tType = 7 Then
        xToDate = CDate(weekEnd)
        xFromDate = xToDate - 175
    Else
        msg = "Report failed - Invalid timespan"
        GoTo Abort_C050R
    End If
    Do Until RSD!EOWDate > xFromDate - 1
        RSD.MoveNext
    Loop
    wc = 3
    xPref = "+P"
    span1 = "P" & Mid(RSD!YrWkNum, 3, 2) & Mid(RSD!YrWkNum, 5, 2)
    RSD.MoveNext
    For fld = 1 To 13
        span1 = span1 & xPref & Mid(RSD!YrWkNum, 3, 2) & Mid(RSD!YrWkNum, 5, 2)
        RSD.MoveNext
    Next fld
    span2 = "P" & Mid(RSD!YrWkNum, 3, 2) & Mid(RSD!YrWkNum, 5, 2)
    RSD.MoveNext
    For fld = 15 To 25
        Select Case fld
            Case 21
                Wk5 = "P" & Mid(RSD!YrWkNum, 3, 2) & Mid(RSD!YrWkNum, 5, 2)
            Case 22
                Wk4 = "P" & Mid(RSD!YrWkNum, 3, 2) & Mid(RSD!YrWkNum, 5, 2)
            Case 23
                Wk3 = "P" & Mid(RSD!YrWkNum, 3, 2) & Mid(RSD!YrWkNum, 5, 2)
            Case 24
                Wk2 = "P" & Mid(RSD!YrWkNum, 3, 2) & Mid(RSD!YrWkNum, 5, 2)
            Case 25
                Wk1 = "P" & Mid(RSD!YrWkNum, 3, 2) & Mid(RSD!YrWkNum, 5, 2)
                rTitle = storeGroup & " Replenishment Stock Out Analysis (for week ending " & RSD!EOWDate & ")"
                With WSR.Cells(1, 1): .Value = rTitle: .Font.Size = 14: .Font.FontStyle = "Bold": End With
        End Select
        span2 = span2 & xPref & Mid(RSD!YrWkNum, 3, 2) & Mid(RSD!YrWkNum, 5, 2)
        RSD.MoveNext
    Next fld
    RSD.Close: Set RSD = Nothing
    span1 = span1 & "+" & span2
    Set RANA = DBI.OpenRecordset("Repl_Spec", dbOpenDynaset)
    Set RDMM = DBI.OpenRecordset("DMM", dbOpenDynaset)
    Set RBUY = DBI.OpenRecordset("Buyer Table", dbOpenDynaset)
    wr = 5
    cpsRow = 0: anaRow = 0: dmmRow = 0: buyRow = 0
    cpsInd = 0: anaInd = 0: dmmInd = 0: buyInd = 0: depInd = 0
    maxInd = 0
    If SOlevel = 1 Then
        cpsRow = wr: wr = wr + 2
        If Mid(SOsubs, 2, 1) = "Y" Then anaRow = wr: wr = wr + 1: anaInd = maxInd: maxInd = maxInd + 1
        If Mid(SOsubs, 3, 1) = "Y" Then dmmRow = wr: wr = wr + 1: dmmInd = maxInd: maxInd = maxInd + 1
        If Mid(SOsubs, 4, 1) = "Y" Then buyRow = wr: wr = wr + 1: buyInd = maxInd: maxInd = maxInd + 1
        depInd = maxInd: maxInd = maxInd + 1
    ElseIf SOlevel = 2 Then
        anaRow = wr: wr = wr + 2
        If Mid(SOsubs, 3, 1) = "Y" Then dmmRow = wr: wr = wr + 1: dmmInd = maxInd: maxInd = maxInd + 1
        If Mid(SOsubs, 4, 1) = "Y" Then buyRow = wr: wr = wr + 1: buyInd = maxInd: maxInd = maxInd + 1
        'dmmRow = wr: wr = wr + 1: dmmInd = maxInd: maxInd = maxInd + 1
        'buyRow = wr: wr = wr + 1: buyInd = maxInd: maxInd = maxInd + 1
        depInd = maxInd: maxInd = maxInd + 1
    ElseIf SOlevel = 3 Then
        dmmRow = wr: wr = wr + 2
        If Mid(SOsubs, 4, 1) = "Y" Then buyRow = wr: wr = wr + 1: buyInd = maxInd: maxInd = maxInd + 1
        depInd = maxInd: maxInd = maxInd + 1
    ElseIf SOlevel = 4 Then
        buyRow = wr: wr = wr + 2
        depInd = maxInd: maxInd = maxInd + 1
    Else
    End If
    If xWhere = "" Then
        xWhere = "[Dept Table].Rspec Not Like '99' "
    Else
        xWhere = "((" & xWhere & ") AND ([Dept Table].Rspec Not Like '99')) "
    End If
    SQLA = "SELECT [Dept Table].Rspec, [Dept Table].DMM, [Dept Table].Buyer, [Dept Table].Dept, [Dept Table].DeptName " & _
           "FROM [Dept Table] " & _
           "WHERE " & xWhere & _
           "ORDER BY [Dept Table].Rspec, [Dept Table].DMM,[Dept Table].Buyer, [Dept Table].Dept"
    Set RSA = DBI.OpenRecordset(SQLA, dbOpenDynaset)
    If Not RSA.RecordCount > 0 Then Application.Calculation = xlCalculationAutomatic: Exit Sub
    currAna = RSA!Rspec
    currDmm = RSA!DMM
    currBuy = RSA!Buyer
    
    Do Until RSA.EOF
        With WSR.Cells(wr, 3)
            .Value = "Dept: " & RSA!dept & " " & RSA!DeptName
            .IndentLevel = depInd
        End With
        WSR.Cells(wr, 2) = 1
            'get dept stock outs
        SQL1 = "SELECT dbo.DCVWeek.Dept, dbo.DCVWeek.Ele, SUM(" & span1 & ") AS Span1, Sum(" & span2 & ") AS Span2, SUM(" & _
               Wk1 & ") AS WK1, Sum(" & Wk2 & ") AS Wk2, Sum(" & Wk3 & ") AS Wk3, Sum(" & Wk4 & ") AS Wk4, SUM(" & Wk5 & ") AS Wk5 " & _
               "FROM dbo.DCVWeek " & _
               "GROUP BY dbo.DCVWeek.Dept, dbo.DCVWeek.Ele " & _
               "HAVING (dbo.DCVWeek.Dept=" & Val(RSA!dept) & ")"
        RSDCV.CursorLocation = adUseClient
        RSDCV.Open SQL1, CnRP, adOpenStatic, adLockReadOnly, adCmdText

 

[SkipVought]

Hmmmmmmmm???

Why are you running a query on a REPORT rather than normalized data?

You’ve set yourself up for great difficulty and anguish.

First normalize your data. Then the query process will be much MUCH less arduous.

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

 

[Andrzejek]

The last line in the code excerpt is highlighted when I click the error message's debug button"

What do you get in the Immediate Window if you insert the BLUE line of code?
Does it make sense? - the SQL1

SQL1 = "SELECT dbo.DCVWeek.Dept, dbo.DCVWeek.Ele, SUM(" & span1 & ") AS Span1, Sum(" & span2 & ") AS Span2, SUM(" & _
       Wk1 & ") AS WK1, Sum(" & Wk2 & ") AS Wk2, Sum(" & Wk3 & ") AS Wk3, Sum(" & Wk4 & ") AS Wk4, SUM(" & Wk5 & ") AS Wk5 " & _
       "FROM dbo.DCVWeek " & _
       "GROUP BY dbo.DCVWeek.Dept, dbo.DCVWeek.Ele " & _
       "HAVING (dbo.DCVWeek.Dept=" & Val(RSA!dept) & ")"
RSDCV.CursorLocation = adUseClient[blue]
Debug.Print SQL1[/blue]
RSDCV.Open SQL1, CnRP, adOpenStatic, adLockReadOnly, adCmdText

---- Andy

There is a great need for a sarcasm font.

 

[kc27]

OK, thanks for your help on this. The Debug.Print SQL1 resulted in the following,

SELECT dbo.DCVWeek.Dept, dbo.DCVWeek.Ele, SUM(P1424+P1425+P1426+P1427+P1428+P1429+P1430+P1431+P1432+P1433+P1434+P1435+P1436+P1437+P1438+P1439+P1440+P1441+P1442+P1443+P1444+P1445+P1446+P1447+P1448+P1449) AS Span1, Sum(P1438+P1439+P1440+P1441+P1442+P1443+P1444+P1445+P1446+P1447+P1448+P1449) AS Span2, SUM(P1449) AS WK1, Sum(P1448) AS Wk2, Sum(P1447) AS Wk3, Sum(P1446) AS Wk4, SUM(P1445) AS Wk5 FROM dbo.DCVWeek GROUP BY dbo.DCVWeek.Dept, dbo.DCVWeek.Ele HAVING (dbo.DCVWeek.Dept=88)

I tried it on another report that also produces this error:
Run-time error '-2147217900 (80040el4)': [Microsoft] [ODBC SQL Server Driver] [SQL Server] Invalid column name 'P1044'.

Debug.Print SQL1 generated this in the immediate window.

SELECT Sum(P1044+P1045+P1046+P1047+P1048) AS Data FROM DCVWeek WHERE ((Ele=4) AND (Dept=422)) GROUP BY Dept

Am I reading this correctly that the issue is with the DVCWeek table?


Note to Skip - I am a technical user, not a developer, so I am not sure how to implement your advice. The tool that I am troubleshooting makes use of an XLA file, that is where all the report definition modules reside. The tool has an Excel front end with a form in which the user chooses their criteria. They then click a button, and the report is generated. My issue is two of the report definitions (or code modules) are not returning a report. But there is no error message when they fail. The front end of the tool just stays on screen and never displays an Excel report. The tool (Excel) is locked up. You have to use Task Manager to quit the Excel tool once the report fails. I am hoping by debugging the XLA file it will lead to the root of the problem. That's why I was wondering if the content of the immediate window indicates an issue with the db or with the Excel template. I am leaning toward the the db, because the tool was working fine until one week ago.

Anyway, thanks again for the help.

 

[Andrzejek]

Run-time error '-2147217900 (80040el4)': [Microsoft] [ODBC SQL Server Driver] [SQL Server] Invalid column name '[red]P1044[/red]'.

Debug.Print SQL1 generated this in the immediate window.
[tt]
SELECT Sum([red]P1044[/red]+P1045+P1046+P1047+P1048) AS Data
FROM DCVWeek WHERE ((Ele=4) AND (Dept=422)) GROUP BY Dept
[/tt]
Do you have a field [red]P1044[/red] in your DCVWeek table?

Are those really field names in your DCVWeek table?
[tt]
Dept, Ele,
P1424, P1425, P1426, P1427, P1428, P1429, P1430,
P1431, P1432, P1433, P1434, P1435, P1436, P1437,
P1438, P1439, P1440, P1441, P1442, P1443, P1444,
P1445, P1446, P1447, P1448, P1449, P1438, P1439,
P1440, P1441, P1442, P1443, P1444, P1445, P1446,
P1447, P1448, P1449, P1449, P1448, P1447, P1446,
P1445[/tt]
---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

[tt]P1424, P1425, P1426, P1427, P1428, P1429, P1430,
P1431, P1432, P1433, P1434, P1435, P1436, P1437,
P1438, P1439, P1440, P1441, P1442, P1443, P1444,
P1445, P1446, P1447, P1448, P1449, P1438, P1439,
P1440, P1441, P1442, P1443, P1444, P1445, P1446,
P1447, P1448, P1449, P1449, P1448, P1447, P1446,
P1445[/tt]

I’d move heaven and earth to find a way to get NORMALIZED data, preferable not summarized.

And I often did!

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

 

[kc27]

The DCVWeek table design looks like this

The first two digits of the "P" numbers represent a year, the second two numbers represent a week number in that year.

The table current has P1514 to P1753. The DCVWeek table is a linked SQL table on an Access database - it is view only for me. For my industry's (retail) purposes, this is week P1749 (the 12 month retail sales period is approximately February to January), so there are a few more weeks to go

The P14** field names you asked about above probably were in the DCVWeek table at some point.

 

[kc27]

Skip - you are correct, these tools are probably not ideal. There are several of them, and you would probably get a rash if you looked under the hood on any of them. They were developed many years ago outside of the company's Information Technology (IT) Department. The user who developed these tools saw people spending time doing a lot of unnecessary manual retrieval and management of data. His efforts to develop solutions through the IT Department were not successful, so he taught himself Excel and Access programming, got IT to let him link to the production tables, and created tools to help people work more efficiently. His tools were and are regarded by the teams that use them as indispensable. IT, not so much. These tools were not developed or purchased by them, so any support they provide is nearly non existent.

I have seen other threads on these forums that describe similar scenarios in other companies, too.

 

[Andrzejek]

We have this approach here where I work, too. It is called ‘shadow IT’ – applications developed by non-IT personnel, no maintenance, no support.

Bot – what a terrible table design! “The table current has P1514 to P1753” – so 2017 had 53 weeks? And every week a new field gets added to that table? P1801, P1802, etc?

If so, let me say:



---- Andy

There is a great need for a sarcasm font.

 

[kc27]

Yes, in the retail world the year 2017 has 53 weeks. It's due to the type of calendar (4-5-4)

The calendar known as 4-5-4 divides the year into months of 4 weeks, then 5 weeks, then 4 weeks and so forth. Beginning on Sundays and ending on Saturdays, it also ensures holidays are lined up and like days, such as a particular Wednesday, are lined up so that sales reporting can be done accurately.

As effective and beneficial as this calendar is, there is one problem that it brings to the table. It only covers 364 days. The extra one day that isn’t covered on the calendar is easily managed, though, by proper bookkeeping completed by each unique retailer. During Leap Year, though, retailers may opt to add another week to the calendar – though some retailers ignore this altogether. When a 53rd week is added, you simply push your weeks back by one to compare to the previous year.

I see why I am getting the "Invalid column name 'P1044'" error on the second report - and this is probably a similar problem for the report I listed in the first post.. I am using test code that is hard coded at the beginning of the modules. The test is for 2010. I will test again with current dates.

'-------TEST MODE-------
Sub TESTC070()
    Run_C070 "430", "20", 1, 5, 5, 11, 2010, True
End Sub
'------------------------

 

[kc27]

Changing the dates in the test code resolved the VB error. Which leaves me back to my original problem of some reports not being generated.

The developer created a front end for this Excel reporting tool. In his XLA file, he has 15 modules (report definitions) that will retrieve data to build 15 different reports. From the front end of the tool, the user selects one of those report definitions, add their user specific criteria, and runs the report to retrieve their data. The tool also allows the users to save the report criteria they entered so so that they can run the report in the future without the need to re-enter the criteria.

Two of those saved reports lock up the tool, and they do not produce a report or an error message, so I have no way of understanding why it is failing. Is there any method I can employ to figure out what is failing?

The tool also offers the user the ability to run the same report definitions (modules) on the fly, without the definition being saved. Those reports are generated successfully. So same module, if used with saved report criteria, it does not generate a report. If used unsaved (adhoc) report criteria, a report is created.

 

[Andrzejek]

Is there any method I can employ to figure out what is failing?

Yes, there are many methods to do so. That's why programmers have jobs and get pay. And that's why the efficient way of writing the application, proper data base design, and maintenance of the code / application is so important.

It is not the answer you are looking for, but... that's the way it is.

What you can do is: put some brakes in the code and narrow down to the point where your code freezes up.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

You might check out this tool that I use often.

 faq707-4594

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

 

[kc27]

When the tool fails, it gets stuck at this screen (after clicking the run now button). Not sure how to access the code from this screen.

 

[Andrzejek]

The picture of the screen your program gets stuck on is not going to help you much. Properly established error handlers usually do the job nicely. You need to know where is your problem in VBA code. Syntax errors will give you a debug option to see your error code, logic errors do not do that. You need to drill down to the problem spot in your code to determine what’s going wrong.

In Excel, hit Alt-F11, that will open VBA IDE
In Project Explorer (usually upper-left corner of the screen) look for Forms
After you find the Form you are looking for, double-click on "Run Now" command button to get to the code behind it.


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Alt+F11 toggles between Excel and VBA.

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

 

[kc27]

Instead of quitting the tool, I just let it run for a couple of hours. It finally gave this error message. My next step is going to be to access the "Run Now" button code to see what it does differently than running the macro manually.