Unanswered: Syntax OPENROWSET from MS Access VBA to SQL Server
I recently migrated tbl_Logger_Data to SQL Server and linked an MS Access front-end to this table. The following SQL is integrated into a form used to import data from the front-end (temporary table) to the table on SQL Server,
CurrentDb.Execute "INSERT INTO tbl_Logger_Data ( DeSeUnID, Date_Time, Amount ) SELECT TEMP_Import_Logger_Data.DeSeUnID, TEMP_Import_Logger_Data.New_Date, TEMP_Import_Logger_Data.Amount FROM TEMP_Import_Logger_Data WHERE TEMP_Import_Logger_Data.Amount Is Not Null;"
This works but it's very slow. I've been trying to integrate the T-SQL option OPENROWSET but can't get the syntax right. For example ... errors follow when I run,
CurrentDb.Execute "INSERT INTO tbl_Logger_Data ( DeSeUnID, Date_Time, Amount ) SELECT DeSeUnID, New_Date, Amount from TEMP_Import_Logger_Data FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\Users\abr eton.CNRDOM\Documents\Insight_Database_Design\Soft ware_Consulting\Barnes_Buck\Hibernation\Replica_Hi bernators_Front_050113.mdb';'admin';'',TEMP_Import _Logger_Data);"
I understand that I can integrate "BULK" as well, which would be very helpful since I'm transferring 200,000-600,000 records at times. Any guidance would be greatly appreciated!