locked
SQL table import to MS Access database(SQL syntax) RRS feed

  • Question

  • How to transfer SQL Server table to MS Access database using sql syntax?

    i have some sql syntax from w3schools.com but it is not working,

    cite example:

    select *

    into users_bak [new table to back up] in 'C:\Database\Backup.mdb'[databasename w/ path]

    from users [old database]

    Pls. help from this situation, tnx very much!!!

    • Changed type onpnt Tuesday, February 15, 2011 3:51 PM
    Tuesday, February 15, 2011 9:21 AM

Answers

  • This syntax is for from Access to Access but not from SQL-Server to Access. For this, look for OPENROWSET on Google or for Linked Server if you intend to do this repeatedly.

    http://msdn.microsoft.com/en-us/library/ms190312.aspx

    You also have to make sure that the 'Ad Hoc Distributed Queries' option is set to ON and you may have to take a look at the AllowInProcess and DynamicParameters properties of the driver, see

    http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx

    Finally, it's quite easy to have trouble with these sort of thing. For example, on my machine, the OLEDB provider doesn't seem to work but I can do the job using the MSDASQL provider calling the ODBC provider:

    Select a.* from Openrowset ('MSDASQL',
    'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\data\DB1.mdb;',
        'select * from Page') as a
    

    Don't put any extra blank and don't remove them neither.  The above query are for selecting but you can use the Openrowset to write to it as with any other ordinary table.

    Finally, the easiest option would probably to create an ODBC Linked Table in Access and use it to import the data from the SQL-Server.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    • Proposed as answer by NSMP Tuesday, February 15, 2011 3:56 PM
    • Marked as answer by Har Das Wednesday, February 16, 2011 7:36 AM
    Tuesday, February 15, 2011 11:09 AM
  • Like I said at the end of my previous message, writing to Access change nothing: you simply use the OpenRowSet as the target table.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    • Marked as answer by Har Das Wednesday, February 16, 2011 7:36 AM
    Wednesday, February 16, 2011 4:48 AM

All replies

  • You can use SSMS Import/Export function to achieve the data movement. I'd think it is the quicker way.

    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    Tuesday, February 15, 2011 9:42 AM
  • This syntax is for from Access to Access but not from SQL-Server to Access. For this, look for OPENROWSET on Google or for Linked Server if you intend to do this repeatedly.

    http://msdn.microsoft.com/en-us/library/ms190312.aspx

    You also have to make sure that the 'Ad Hoc Distributed Queries' option is set to ON and you may have to take a look at the AllowInProcess and DynamicParameters properties of the driver, see

    http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx

    Finally, it's quite easy to have trouble with these sort of thing. For example, on my machine, the OLEDB provider doesn't seem to work but I can do the job using the MSDASQL provider calling the ODBC provider:

    Select a.* from Openrowset ('MSDASQL',
    'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\data\DB1.mdb;',
        'select * from Page') as a
    

    Don't put any extra blank and don't remove them neither.  The above query are for selecting but you can use the Openrowset to write to it as with any other ordinary table.

    Finally, the easiest option would probably to create an ODBC Linked Table in Access and use it to import the data from the SQL-Server.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    • Proposed as answer by NSMP Tuesday, February 15, 2011 3:56 PM
    • Marked as answer by Har Das Wednesday, February 16, 2011 7:36 AM
    Tuesday, February 15, 2011 11:09 AM
  • tnx SEVENKNIGHTS

    but im tryng to transfer table from SQL Server to MS Access using sql syntax,

    Wednesday, February 16, 2011 1:29 AM
  • Tnx for the info Sylvain lafontaine,

    I've been used ur sql syntax, but ur syntax probably transfer data tables from MS Access to SQL Server,

    But what im tryng to do is to transfer table from SQL Server to MS Access,

    could you help me,

    tnx Harold Dasco

    Wednesday, February 16, 2011 1:49 AM
  • Like I said at the end of my previous message, writing to Access change nothing: you simply use the OpenRowSet as the target table.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    • Marked as answer by Har Das Wednesday, February 16, 2011 7:36 AM
    Wednesday, February 16, 2011 4:48 AM
  • to Sylvain Lafontaine,

    lets ellaborate,

    im citing example,

    i have a table from SQL Server namely Acct_info, then i wanted to transfer this table to MS Access database namely DB1.mdb,

    sql syntax;

    Select a.* from OpenRowSet ('MSDASQL', 'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\data\DB1.mdb;',
        'select * from Acct_info')

    Hence, it didnt trigger where should i insert the syntax for transferring table, i create my own syntax same to you,

    Procedure;

    declare @xStr nvarchar(250)
    declare @wholeStr nvarchar(250)
    set @xStr= 'insert into Acct_info values'
    set @wholeStr= @xStr +(select * from acct_info)

    Select a.* from OpenRowSet ('MSDASQL', 'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\data\DB1.mdb;',
        @wholeStr)

    therefore, theres an error occured, will you pls. cite example for me, pls. tnx so much

    Harold Dasco

    Wednesday, February 16, 2011 6:00 AM
  • You must use an Insert into against the table returned by the OpenRowSet; like with any ordinary table:

    Insert Into OpenRowset (...) ...


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    Wednesday, February 16, 2011 6:55 AM
  • to Sylvain Lafontaine,

     

    tnx very much for your help, your the best, but im thinking how could i transfers all the datas from sql server table to ms access,is theres an easy way to transfer all the datas?? syntax;

    insert into openrowset  ('MSDASQL', 'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\data\DB1.mdb;', 'select * from Acct_info')

    values (select * from acct_info)

    error occured:

    incorrect syntax near the keyword 'select',

    • Marked as answer by Har Das Wednesday, February 16, 2011 7:36 AM
    • Unmarked as answer by Kalman Toth Wednesday, February 16, 2011 10:27 AM
    Wednesday, February 16, 2011 7:19 AM
  • tnx very much for your help sylvain lafontaine, i already know the answer,
    Wednesday, February 16, 2011 7:36 AM
  • Glad to see that it's now working OK for you.

    I forgot to mention that you cannot use variable inside an OPENROWSET: all the parameters must be string constants; so you cannot really build dynamic queries unless you use an EXEC(@FullSQLString) command.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    Wednesday, February 16, 2011 10:18 AM