Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting Access Table to Sql Server

    Exporting to SQL Server

    Apologies previously incorrectly posted to spreadsheets:

    Hi wonder if you can help me ?? I have created a table (Tbl_XRef) in Access 2003 which is created on a click button (it performs various functions between old tables and new to create the above table). Once it is completed I want to transfer it automatically across to SQL Server on the same click event (after having manually deleted the previous version of the table in SQL server).

    I do have an ODBC link called ODBC1 which goes to the SQL Server database - can anyone tell me how to do this using a DoCmd.???

    Thanks for your help.

    Blitzy

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    There are at least a couple of options: Does the SQL Server table already exist, or do you simply reuse it? If the table needs to be created, you can create it using a Pass-Through query to execute the necessary T-SQL statements. If you have to create it, then you may choose to link to it, and you can do that using DDL statements to create the tabledef and link it. Then you can run an Access append query to add the data to it.l Or you could create a direct link to the SQL Server table using an ADO connect string to actually open the table and then use ADO commands to write the records from Access to the SQL Server table one record at a time. The latter is likely to be a fair bit slower however, so your choice may depend on how many records are involved.
    Wendell

  3. #3
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, several hundred thousand records so likely to be slow. I did try to set up a link in SQL to the access table but was booted out because of permissions - have had to park this just now, but may have to come back to it! Thanks for input

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Actually, I think you want to create a link in the Access database to the SQL Server table using ODBC, not the other way around. Your original post indicated you do have that. Rather than recreating the table, I would run a delete query and then an append query to move the data in Access to SQL Server. Or you could simply have the SQL Server table the destination for the process that creates the data to begin with.
    Wendell

  5. #5
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Troubles....

    Quote Originally Posted by WendellB View Post
    Actually, I think you want to create a link in the Access database to the SQL Server table using ODBC, not the other way around. Your original post indicated you do have that. Rather than recreating the table, I would run a delete query and then an append query to move the data in Access to SQL Server. Or you could simply have the SQL Server table the destination for the process that creates the data to begin with.
    Thanks Wendell, problem I have is that I am trying to create a wee automatic cleaning and export function on an on_click event, for which everything is working fine except the final export to SQL. Problem is that I cannot seem to find a way in VBA (I'm not a programmer just probably a slightly more "advanced" user than someone just putting queries together) where I can do that - I've found a transfer option which seems to relate to the entire database into SQL but not just one table. So if for example I wanted to punt my newly cleaned table directly into SQL Server how would I do it using a Docmd? I don't want to append to an existing table in SQL server but to put the Access table straight into the database. For example if my Access Table were called Table1, my ODBC connection called ODBC1 and the SQLServerDatabase called SQLServer1? Thanks for your help.

    blitzy

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    You can't transfer a table between two databases as such. You can, however, execute SQL commands, either directly or indirectly, that result in your table being created and then you can populate that table.

    Wendell's suggestion is actually the simpler one to implement - Create the table in SQL Server, link it from Access and then a single update query (maybe preceded by a delete query) would be enough to get your data into it.

    If you don't (want to) link to the SQL Server table, you cannot treat it as a local Access table, so you cannot run update queries that read that from other Access tables. You'd need to insert each record "manually" by writing an appropriate SQL INSERT INTO statement and execute it directly in the SQL Server database. This would need to loop over all the records you wanted to add to the table.

  7. #7
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Ruirib, that makes sense.

    Will give it a go.

    M

Posting Permissions

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