Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013
    Posts
    2

    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!

    andre

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Use a stored procedure on sql server to start with.

  3. #3
    Join Date
    May 2013
    Posts
    2
    I wish I had that knowledge! So apparently, I don't even know how to start, dreadful. Alright, thanks for the insight.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •