2

I have an Access 2003 database that data is inserted into throughout the day and I want to convert to a SQL Server database. There are many different instruments and processes that insert into the Access database, so it would take a lot of modification to change all of them to insert into SQL Server.

I know Microsoft has a tool to convert an Access database to a SQL Server one, but as far as I can tell, it can't be run automatically and it wouldn't be able to just import the new data since the last "merge" (please correct me if I am wrong)

What would be the best way to go about this problem? Maybe some sort of script?

Thanks.

Improve this question
7
  • What version of ms-access are you using? If it is at least 2010 or above you can create event-driven macros which function similar to a trigger in SQL server, this post may help (stackoverflow.com/questions/5623440/ms-access-trigger), with those you could just insert data automatically from Access to your SQL Server Commented Sep 14, 2018 at 14:08
  • I'm using Access 2003 Commented Sep 14, 2018 at 14:11
  • Maybe this tool would work for your case, "SQL Server Migration Assistant for Access (AccessToSQL)" (learn.microsoft.com/en-us/sql/ssma/access/…) Commented Sep 14, 2018 at 14:14
  • The problem with Microsoft's SSMA is that I don't see a way to automatically run the migration. From what I can tell it is used as a one-time conversion from Access to SQL Commented Sep 14, 2018 at 14:17
  • 1
    @Andre There are only inserts on the Access side, and the SQL will be read-only Commented Sep 14, 2018 at 14:25

1 Answer 1

Reset to default
5

There are only inserts on the Access side, and the SQL will be read-only

Then I would do this:

  • Link the SQL Server tables into the Access database
  • Run INSERT queries to copy the new data into the server tables. This assumes that all tables have primary keys.

The queries would e.g. look like this (air code):

INSERT INTO dbo_Customers
SELECT src.* 
FROM Customers AS src LEFT JOIN dbo_Customers AS tgt 
  ON src.ID = tgt.ID
WHERE tgt.ID IS NULL

Customers is the local table, dbo_Customers the server table.

The query selects all records from Customers that don't exist in dbo_Customers.

Since several other processes access the original database, it's probably best to set up a new Access database, link both the original and the Server tables, and run the INSERT queries in the new db. You don't want any code running in the "data" db.

It can be automated e.g. with a Form_Timer procedure.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This seems like what I'm looking for. Thank you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.