Post reply

Making SQL process an append from SQL to Access

  • February 17, 2005 at 7:26 am

    #162652

    I'm trying to write a pass-through query to be executed by my SQL 2000 server. The query needs to take a table on SQl and append the data to an Access table over the network. Here's what I'm trying right now that's not working, I get an "error near 'IN' statement"

    INSERT INTO AccessTable (field1, field2, field3) IN 'networkpath\subfolder\AccessDatabase.mdb'

    SELECT field1, field2, field3

    FROM SQLTable;

    Any ideas? I know it's backwards to take data from SQl to Access, but for a few months this will be necessary for me. Thanks!

  • February 17, 2005 at 8:37 am

    #542825

    You can use the OpenRowset method or the OpenDataSource method to open the access table and append the SQL records. You can view more about these methods in BOL. The example below uses the OpenRowset Method.

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', '\\Networkpath\subfolder\AccessDatabase.mdb';'Admin';'', AccessTable) (field1, field2, field3)

    SELECT field1, field2, field3

    FROM SQLTable

  • February 17, 2005 at 9:52 am

    #542866

    Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply