Upload Data from Access to SQL Server

[N Vikram]

hi gurus,

i am working on a project where in i have 2 packages. One is an offline package which is a combination of VB and Access database. There are around 20 locations where this type of offline package is installed. All these places are not networked. Once in a while i.e.,once in 15 days or 1 month data from these places are uploaded to an internet server through Internet. In the Internet server a web application is hosted through ASP and SQL Server. The data from the offline package(Access database) has to be uploaded to SQL Server database. Data from 15 tables will be uploaded. The table schema of both the databases are similar. There might be 100 records in each table.


At present, we are uploading by creating 2 connections from VB code. One connection is to the Access database and another connection is to the SQL Server . (We know the IP address of the Server where SQL Server is present).

Which is the best way of uploading data from Access to SQL Server?

[DinoVaught]

Open up the Access database, navigate Access’s menus <File>, <Get External Data>, <Link Table>, choose the appropriate type at the bottom of the dialog, then click the <Link> button. You may need to set up an ODBC connection to your SQL server before you can link to it through MS Access

Once you have the link established create a SELECT query that properly selects the data you want to move to SQL Server. Once you have your SELECT query working correctly add your linked SQL Server / table to the query, change the SELECT query to an APPEND query. Point the appropriate tables / fields in MS Access to their equivalents in SQL server then run the APPEND query.