Upload records from MSAccess to SQL SERVER

[tom in houston]

Hi:

I'm missing the big flick somewhere... hope somebody can clue me in.

I have a local table in MSAccess that holds temporary data. The point of the temp table is to allow the user to scrub up the data and change a couple field values that vary by record before committing the data to the main table.

What is the most efficient way to move the scrubbed data from the local table to SQL Server table?

Please note that due to performance issues, I can't use a linked table (the linking causes severe lag in performance, trying to troubleshoot that, but not having much luck. In the meantime, I need to be able to use the app).

If it matters, the table has maybe 20 fields, mix of text and numbers, no memos. Updates may consist of up to 2000 or so records at a time.

I've thought about stepping thru each record and using a pass thru query to insert one-by-one, but that seems wildly inefficient.

Thanks

 

[Lightwave]

Pull the linked table and the temp table into one front end on a computer that has a good connection to the backend and then append or update the SQL table with the temp table.

 

[mdlueck]

What is the most efficient way to move the scrubbed data from the local table to SQL Server table?

This sounds like code I have that caches "Pick List" choices into the FE DB. The admin interface which allows edits to the pick list starts by downloading a copy of the entire pick list into the FE DB, allows edits to the FE DB, and when it is time to commit the changes back to the BE DB the code steps through looking for changed records and executing UPDATE Stored Procedures as needed.

I have no concern of orphan records in the BE DB as deletes are never allowed. Only INSERT and UPDATE operations are allowed. If someone decides to abandon a pick list choice, they may change the Enabled / Disabled value of that record, and in production use the disabled entries do not appear.

I am using an in-memory table (copy) to be able to step through as-was/as-is states of the pick list, and when differences are detected that signals an update of the BE DB necessary. This is facilitated by a custom collection class in VBA. I believe the site it was from is the following one, just the site is not coming up for me currently:

http://www.databaseadvisors.com/new...ng custom collections in microsoft access.asp

 

[mdlueck]

Confirmed that is/was the correct URL.

Here is the article via Archive.org

http://web.archive.org/web/20100929...ng custom collections in microsoft access.asp