Exporting Access Table to Sql Server

Topic

New Reply

[h=2]Exporting to SQL Server[/h]Apologies previously incorrectly posted to spreadsheets:

[INDENT]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 [/INDENT]

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thanks Ruirib, that makes sense.

Will give it a go.

M

Reply | Quote