Access to SQL (Sql 7/Access 97)

Topic

New Reply

How can I take data from an access database and load into my sql server database? I have a separate database that contains the same fields but a different department’s information and I want to put all my records together on the sql database.

Thanks,
DGP

Reply | Quote

Replies

If these “separate” databases are both Access, and contain the same fields(which you said they do), then you can append one to the other and then export the appended table to your sql server(see Access help under the word “server”, for step by step instructions.) Then once you have a table on the server, you could link to it in an Access database if that is what you require. You need the appropriate ODBC driver and probably a userid and password, if the sql server has security set up(which it should).

Reply | Quote

Still another option is to use DTS to transfer the data into existing SQL Server tables. DTS is part of SQL Server, and you can select Access as a source for records to append to SQL tables. You can even use transformation to manipulate which fields in Access wind up in which fields in SQL Server. Just keep in mind that SQL Server sometimes balks at the data, but at least it will tell you that it had a problem.

Reply | Quote

This is probably to obvious for you to miss, but you do want to be sure to have a field (column) in SQL that tells you which department is which. Also, be sure to make a backup of your SQL table before you do the transfer, just in case murphy’s law happens to rear it’s head.

Reply | Quote