can't call update query from Excel VBA

Question

I have a stored query in an Access 2007 database. I have Access 2010 on this machine. I am trying to do the following:

1. export an Excel spreadsheet as a temp table in the database.
2. add a column to the temp table and populate it with the filename
3. update a linked table using the contents of the exported table.

The update is a stored query in the Access front end. When I run the update query from Access, it works fine. But when I run it from VBA using the code:

sub test()

filename=thisworkbook.name
Set db_fe = OpenDatabase("C:\Data\myDB.mdb")
If TableExists(db_fe, "tempCorrection") Then
    DoCmd.RunSQL "drop table tempCorrection;"
End If
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tempCorrection", "C:\Data\corrections.xls", True


DoCmd.RunSQL "alter table tempCorrection add column newColumn text;"
DoCmd.RunSQL "update tempCorrection set newColumn='" & filename & "';", dbFailOnError
db_fe.Execute "updateCorrections", dbFailOnError
DoCmd.RunSQL "drop table tempCorrection;"

end sub

then on the "db_fe.execute" line I get Run-time error '3078': "The Microsoft Access database engine cannot find the input table or query 'tempCorrection'. Make sure it exists and that its name is spelled correctly."

Here's what query updateCorrections looks like:

UPDATE production AS p
INNER JOIN tempCorrection AS t
ON
(p.filename=t.filename)
AND
(p.a1=t.a1)
AND
(p.a2=t.a2)
set p.a3=t.a3

Any ideas as to why I'm having trouble executing this query from VBA?

Answer 1

If your code runs correctly from inside an Access session, consider creating an Access application instance from your Excel code, then running the rest from that Access application instance.

Const cstrDbPath As String = "C:\Data\myDB.mdb"
Dim appAccess As Object
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase cstrDbPath, False
'then your code ... for example ... '
appAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
    "tempCorrection", "C:\Data\conrrections.xls", True
'before you exit the procedure ...'
appAccess.Quit
Set appAccess = Nothing

I hope that points you to something useful. However, I suspect there is more involved here. I encourage you to include Option Explicit in the Declarations session of your module, then Debug->Compile your application's code from the VBE main menu. Seems like the compiler might complain about db_fe since you didn't Dim it ... is it a global variable declared elsewhere? Whatever it is, make sure to use Option Explicit!

Edit: Set a break point on the TransferSpreadsheet line, then step through the rest of the procedure line by line (F8). Just before you get to the db_fe.Execute line, try something like this to see if tempCorrection is found:

Debug.Print DCount("*", "tempCorrection")

Not sure how useful that will be, either ... at this point I'm basically grasping at straws. My gut suggests this may have something to do with dropping then re-creating tempCorrection each time ... I would code around that.

Edit2: Something puzzles me about this section of your code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
    "tempCorrection", "C:\Data\corrections.xls", True
DoCmd.RunSQL "alter table tempCorrection add column newColumn text;"
DoCmd.RunSQL "update tempCorrection set newColumn='" & _
    filename & "';", dbFailOnError
db_fe.Execute "updateCorrections", dbFailOnError

You use TransferSpreadsheet to create the table tempCorrection. Later you get an error on db_fe.Execute that the database engine can't find tempCorrection. But in between, you execute 2 DDL statements which refer to tempCorrection --- I don't understand why those don't throw an error about not finding the table. Maybe it's something to do with DoCmd.RunSQL (and/or you have SetWarnings False). I would replace DoCmd.RunSQL with db_fe.Execute plus dbFailOnError.

Also the second parameter to DoCmd.RunSQL is to tell the db engine whether to use a transaction when executing the SQL. Using dbFailOnError as the second parameter to DoCmd.RunSQL just seems wrong.

Answer 2

Per @David-W-Fenton's suggestion, I'm posting my revised code for update() here. I've also included the code for writeSheetTable(), the subprocedure that reads in the worksheet values from Excel and writes them to the temp table tempCorrection. I used this sub instead of doCmd.transferspreadsheet because I thought maybe there was a problem with using doCmd and database.execute in the same procedure.

Sub update()

Dim db_fe As Database
Dim rs As Recordset
Dim tbl As TableDef
Dim fld As DAO.field
Dim tablestruct As String
dim filename as string


'open database'
Set db_fe = OpenDatabase("C:\Data\myDB.mdb")

'define SQL for creating temp table'
tablestruct = "create table tempCorrection " & _
"(a1 text,a2 text,a3 text,a4 text,a5 text,a6 text,a7 text," & _
"a8 text,a9 text,a10 text,a11 text,a12 text,a13 text,a14 text);"

'generate temp table from spreadsheet data'
writeSheetTable "my excel data", db_fe, "tempCorrection", tablestruct


'add field for userID and populate it, normally this is taken from filename'
Set tbl = db_fe.TableDefs("tempCorrection")
Set fld = tbl.CreateField("filename", dbText, 30)
tbl.Fields.Append fld
filename="TEST"
db_fe.Execute "update tempCorrection set filename='" & filename & "';", dbFailOnError
Debug.Print DCount("*", "tempCorrection")

'execute stored query updateCorrections, which I provided in my original question'
db_fe.Execute "updateCorrections"

'delete temp table'
db_fe.Execute "drop table tempCorrection;"

End Sub


Sub writeSheetTable(sheetname As String, db As Database, tablename As String, tablestruct As String)

Dim lastrow, lastcol, max As Long
Dim prodarray As Variant
Dim rs As Recordset
Dim ws As DAO.Workspace
Dim r, c As Long

'read in the sheet contents to prodArray'
With Sheets(sheetname)
    lastrow = .UsedRange.Rows.Count
    lastcol = .UsedRange.Columns.Count
    prodarray = .Range(.Cells(2, 1), .Cells(lastrow, lastcol))
End With
max = UBound(prodarray, 1)


'drop temp table if it already exists'
If TableExists(db, tablename) Then
    db.Execute "drop table " & tablename & ";"
End If


'create table using SQL defined in update()'
db.Execute tablestruct, dbFailOnError


'build table row by row as a recordset, using transaction to speed up appends'
Set rs = db.OpenRecordset(tablename)
Set ws = DBEngine.Workspaces(0)
ws.BeginTrans    
With rs
For r = 1 To UBound(prodarray, 1)
    .AddNew
    For c = 1 To UBound(prodarray, 2)
        .Fields(c - 1) = IIf(prodarray(r, c) = Empty, "", prodarray(r, c))
    Next
    .update
Next
End With
ws.CommitTrans

'destroy recordset object'
rs.Close
Set rs = Nothing

End Sub

It's still getting errors, even though I've replaced all the DoCmd.RunSQl statements with database.execute. The error sequence is as follows:

1. I run it once and get an "Item Not Found in this Collection" error on Set tbl = db_fe.TableDefs("tempCorrection")
2. If I leave tempCorrection in existence, when I run it again, it works fine. If I delete tempCorrection and run it again, it gives the same "Item Not Found" error.