Copy data from Access to SQL

Question

I am in the process of migrating an existing Access database to a SQL database with a web front-end. I have successfully copied the database to SQL using the SQL Server Migration tool and am working on developing the application. For reasons I won't go into here we cannot just link the Access front-end to the SQL DB and use the SQL DB as the single data source, so the Access DB is still in use and the data being updated.

What I am trying to find is a simple way to update the data in the SQL database with the new data in the Access DB. The table structure etc is the same so all I need to do is copy the data, is there a script or easy method to do this? I don't really want to have to run the migration wizard every time I need to do an update.

Answer 1

Its possible to link SQL Server tables into Access where your Access forms can enter the data in as normal but it will actually be going into SQL Server.

Answer 2

I would also suggest using linked tables to the SQL server but by the sounds of it that's out of your control. I hope this is just temporary so the users can keep working on the Access DB while development is going on, and not because some manager thinks this kind of manually-integrated system is a good idea "going forward".

There is a better tool for migration to SQL server called Sql Server Migration Assistant (SSMA) that is much better than the "upsizing wizard" built into Access. You may be able to script that to make the task repeatable.

Another approach would be to set up an Express edition of SQL server wherever the Access DB lives and link to that, using it as a staging DB. You can set up the SSEE version as a replication subscriber, and synchronize changes between that and your development/production databases whenever you want. To the users it will be transparent and you can tell management that the Access DB is totally separate from the development/production databases. Or just copy over the database each time if you can't be bothered learning about replication, but I think replication could make it all much simpler.

Answer 3

You can run access queries through VBA that add new records and update previous records in the SQL Server. The connection string is identical to that used to connect an SQL table, as I indicate in this rough example, so you can test connect to the relevant table to obtain a suitable string:

Dim db As Database
Set db = CurrentDb

strConnect = db.TableDefs("dbo_test").Connect
strSQL = "UPDATE [" & strConnect & "].[test] As s " _
& "INNER JOIN TestAccess As a " _
& "ON s.ID=a.ID " _
& "SET s.Descr=a.Descr WHERE s.Descr<>a.Descr"
db.Execute strSQL, dbFailOnError
Debug.Print db.RecordsAffected

strSQL = "INSERT INTO [" & strConnect & "].[test] (ID, Descr) " _
& "SELECT a.ID, a.Descr FROM TestAccess As a " _
& "LEFT JOIN [" & strConnect & "].[test] s " _
& "ON s.ID=a.ID " _
& "WHERE s.ID Is Null"
db.Execute strSQL, dbFailOnError
Debug.Print db.RecordsAffected