You know, I'm wonderign if I'm looking too closely at the tree and am
missing the forest. You want to update a table in Access with values
from an Excel spreadsheet, right? I'm wondering if you could simply
establish a connection between the two (using the linked table function
in Access). This way, when you update your spreadsheet and save it,
the data will be automatically updated in Access. This is probably the
simplest solution. Of course, it means that the Excel table will be an
exact replica of the Access table, so if there's 1000 records in
Access, then they'll all show up in the Excel sheet. You could copy
only the rows you need to update from one Access table to another, and
then update between the two tables, but I'm not sure if that's a good
solution or not.

I guess I'm a bit confused (it's late, I'm tired, and I am probably
overthinking it); where does the data come from? Are you using Excel
as a data import and recording tool? Or is it that you have data in an
Access table that you want to manipulate in Excel and then move back?

Of course, with SQL Server, it gets a bit more complicated. You could
do a linked table, but the performance would suffer. The whole point
of SQL Server is that your data should be easily accessible to a great
deal of users, and having them all hit an Excel spreadsheet doesn't
sound like it would work so well. In that case, you're better off
thinking about a stored procedure or DTS package to upload the data
from your Excel sheet to the SQL Server database. Both of those
options require a bit more study than you'll probably find in this
post, but if you play around with it, I'm sure you'll get it

Stu

Stu wrote:

>You know, I'm wonderign if I'm looking too closely at the tree and am
>missing the forest. You want to update a table in Access with values
>from an Excel spreadsheet, right? I'm wondering if you could simply
>establish a connection between the two (using the linked table function
>in Access).

Thanks, I had already considered and rejected the possibility of making
a link between Excel and Access. The Excel spreadsheet will have
only
some of the rows that the Access spreadsheet has and our Excel sheets
are comming from several users who build them either manually or else
get the data from a program. So, I really need to do the update of
the Access table from VBScript.

I do think that mesages such as "UPDATE syntax error" from Access are
woefully inadequate and I will actively seek open source alternatives
to Microsoft Office database products in the future.


Thanks Again
Jim

Just as a final note, and again thanks to everyone for the helpful
suggestions.

The following method worked immediately:

'STEP 1, COPY EXCEL INTO UNIQUE ACCESS TABLE
SQL = "SELECT * INTO UpdTbl" & _
" FROM [C1R0$] IN ''" & _
" 'Excel 8.0;database=c:\excel\UpdateFinal1.xls';"

'STEP 2 UPDATE MAIN ACCESS TABLE WITH TABLE FROM STEP 1
SQL = "UPDATE C1R0 INNER JOIN UpdTbl ON C1R0.[Index] = UpdTbl.[Index]"
&_
" SET C1R0.QTY = UpdTbl.QTY, C1R0.MFG = UpdTbl.MFG"

where "QTY" and "MFG" are just some fields common to both tables that
I put in to verify that the updates where working.

A few caveats along the way:
1. Let Access build its freaking autonumber index column - I had wanted
to use my own index column, which also had unique numbers, but it
refused to work with it.
2. Forget about importing directly from the Excel spreadsheet unless
you enjoy reading goofy error messages. Even if the spreadsheet is
structured exactly the same (same columns and types of data) as the
main Access table.
3. Step back and wonder how the hell managers can spend tens of
thousands of dollars on this nonsense - be on the lookout for open
source or Cache or some better way of doing these things. If nothing
else turns up, I may write it myself.

Jim