Same query, different results?

[Ormusia]

First post.



I have been assigned an Access project.
It's fun, yet challenging when inexperienced.

When you first open the db, specifically the main form, the code detects the absence of two tables, xps and mps. (Images below).
It then creates these tables by importing from two spreadsheets...

DoCmd.TransferSpreadsheet acImport, , "mps", mps, True
DoCmd.TransferSpreadsheet acImport, , "xps", xps, True

Once that is done, the user will press a button to insert into a third table a collation of fields from the two created tables based on a field they share and how old is the entry, using this code:

Private Sub MagicButton_Click()
    Dim db As Database
    Dim sql As String
    Dim sDate As Date
    sDate = DateAdd("m", -2, Date)
    
    sql = "INSERT INTO res " & _
            "(" & _
               "[Asset Number], " & _
               "[Market Centre]," & _
               "[Partner or XPS Account Name], " & _
               "[Account Name], " & _
               "[Serial Number]," & _
               "[3rd Party Manufacturing Serial Number]," & _
               "[Offering], " & _
               "[Price Plan], " & _
               "[Last Bill Date]," & _
               "[Current Read Date]" & _
            ") "
            
    sql = sql & "SELECT DISTINCT " & _
               "mps.[Asset Number], " & _
               "mps.[Responsibility Name], " & _
               "xps.[Partner or XPS Account Name], " & _
               "xps.[Account Name], " & _
               "xps.[Serial Number], " & _
               "xps.[3rd Party Manufacturing Serial Number], " & _
               "xps.[Offering], " & _
               "xps.[Price Plan], " & _
               "xps.[Last Bill Date], " & _
               "xps.[Current Read Date] "
               
     sql = sql & "FROM xps INNER JOIN mps " & _
               "ON xps.[Serial Number] = mps.[Asset Number] " & _
               "WHERE xps.[Last Bill Date] = #12/30/1899# Or xps.[Last Bill Date] < " & "#" & sDate & "#" & " ORDER BY mps.[Responsibility Name];"
               
    Debug.Print sql
    
    Set db = CurrentDb
    db.Execute sql, dbFailOnError
End Sub

So, after some time, I got this INSERT INTO statement to work.
Worth noting that this was done through many cycles of creation and destruction and creation of those two tables via spreadsheet imports.
Every time you close the form it deletes the tables and when you open the form recreates them based on the transferspreadsheet call.

So, it was working.
I had done it!

So I copied the db, opened it, manually deleted the two tables, and ran the form again.
This time the tables got reimported, and then, when I pressed the magic button to do the INSERT INTO...

It Failed!
Same code, yet it failed. It also never worked after I had manually deleted the tables.

So I opened the original on one side, the copy on the other side.
Same code, same tables, same field names and types, same bloody everything.
Did not manually delete the tables: works.
Did manually delete the tables: fails forever after.

This, well, this melts my mind.

The error it gives is one I had wrestled against before: 3061 Too few parameters, Expected 1.

Here's the error, and the tables for both the copy (not working after tables recreations) and the original (works unless I recreate the tables)




Any advice would be greatly appreciated.

[ItsMe]

Maybe the issue is Responsibility Name and Market Centre not matching. It is past my bed time but that is what jumped out at me.

[June7]

I don't know why manually deleting tables would cause code to fail but try running Compact & Repair after manual delete before running code.

Why create and delete tables? This causes design modification which is not advisable as a routine process. Maybe better to delete records then import into existing tables.