Update MSSQL table with Uploaded MS Access DB

[bigbrain28]

I could use a little advice in this task. I have (potentially) dozens of users
that will want to update their data on our server (MSSQL) by simply uploading
their own exported data from their local systems. The first client I am trying
to accomodate wants to send a MS Access DB.

So I need to be able to do several things, read the NON datasourced Access DB,
and INSERT and UPDATE the MSSQL version where appropriate. The data is
membership listings, so there will be some new, some deleted and some simply
edited. Would it be easier to replace the DB entirely with the new one? I
assume so. If so, the question simply becomes;

"How do I read an uploaded Access database with no named DATASOURCE attached
to it and use its contents to replace a table in a MSSQL database?"

[Sojovi]

Use this but I assume 2 things :
1) The SQL server is in the same server where the Access files are uploaded.
2) You know the structure of the tables in the Access DB and the SQL DB.



<CFQUERY NAME="Query Name" DATASOURCE="SQL DATASOURCE">
DELETE FROM sql_table .......

INSERT INTO sql_table (column1, column2 .....)
SELECT AccessTable.column1, AccessTable.column2 .....
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\UPLOAD_FOLDER\access.mdb';'';'', Access_Table) AS AccessTable

/**** OR ****/

UPDATE sql_table SET column1=AccessTable.column1
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\UPLOAD_FOLDER\access.mdb';'';'', Access_Table) AS AccessTable, sql_table
WHERE .........

</CFQUERY>

[bigbrain28]

Well, thank you, firstly for your response, I will certainly give it a shot.
Your first assumption, unfortunately is not true for me. Our SQL server and the
webserver (where the uploaded Access DB goes) are not the same PC, they are
however on the same network, so I should be able to path to the MDB. I do,
however know/control the structures and should have no issues with that aspect.
If the OPENROWSET call does the connection job, I believe I'll be in business.
I will reply again once I have had the opportunity to test this code, thanks
again!