need an updatable query error

[osurfc]

I have a MsAccess app that is/was being used to populate a spreadsheet everyday. Since the Jet was updated it no longer works. From an order form I create a temporary table and use that to push to the excel file.

Using the following sql
INSERT INTO Sheet1 ( F1, Vendor, Item, [$ each], [# each], Cost )
SELECT TempTable.DateOrd, TempTable.MFG, TempTable.ITEM, TempTable.[$EA], TempTable.qty, TempTable.[Tot$]
FROM TempTable, Sheet1;

Not sure how to fix the error. Any help is appreciated.

 

[dhookom]

Access can no longer update Excel files. You might need to use exporting or automation to write the records.

Duane
Hook'D on Access
MS Access MVP

 

[osurfc]

Thanks for the info.

 

[KevinsHandle]

Hi Duane,

Just saw this and am wondering about the specific meaning of "Access can no longer update Excel files.". I have several Access mods that document Access queries, tables, etc into an Excel sheet. It specifically says "

wbExcel.Sheets(1).Range("A" & lngRow) = db.QueryDefs(lngQuery).Name
wbExcel.Sheets(1).Range("D" & lngRow) = db.QueryDefs(lngQuery).SQL

Can I no longer do this? Which version of Access or Excel cut it off?

Thx

Kevin

 

[PHV]

Kevin, you still can use automation to update excel files, but not SQL code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KevinsHandle]

Hi again PHV. Thanks for prior help.

I also have Excel code that simple tells Excel to read a recordset (that is created by Access or Excel and presumably others) that I hand it.

Is that still working??

Thx

Kevin

 

[dhookom]

Creating a recordset should be no problem. You would not be able to create a recordset of a linked Excel table in Access and edit the data.

Duane
Hook'D on Access
MS Access MVP