VBA issues with Excel 2007 talking to Access 2007 DB
Posted on 2009-06-29
1) I have an Excel 2007 App that interfaces with an Access 2007 db using VBA code. I have a simple data import statement (using TransferText) that imports raw text data into a staging table in Access. This works fine.
2) After this, I construct a simple "INSERT INTO prodtable SELECT * FROM stagingtable " query,
and try to submit it for execution via VBA.
3) The above query works only if I have the VBA code window open and I step through the code.
3) the above query fails if I have the VBA code window closed.Might strange eh?
4) Can someone educate me as to what is going on with Excel 2007 vba and Access db? More importantly, how do I ensure that this query works consistently all the time?
Any and all help seriously appreciated.