July 4, 2012 at 5:18 am
Hello everybody, this is my first post on this forum so I hope to respect netiquette and I hope that I am posting in correct place.
However, I need to synchronize a Access 2003 database to SQL 2008 R2.
I can import Access 2003 database to SQL 2008 R2 without problem using "import and export data" tool, but after this first import I need to synchronize Access 2003 DB to SQL 2008 R2 every tot hours.
Each transaction will be on the Access 2003 so the synchronization will be one way from Access to SQL.
Any ideas?
Thanks in advance...
Gio
ps: I already check on forum solutions to this "problem" but without success....
July 5, 2012 at 7:42 am
Nobody have an idea? :crying: :crying: :crying: :crying:
July 5, 2012 at 7:44 am
Why leave the data in access, do a one time import to sql then point the access front end at SQL instead?
July 5, 2012 at 8:09 am
I think the same.
The problem is that the DB on access 2003 is used by an application that is currently in use but no more supported because the Software House that wrote the code is closed (you know the big crisis in Italy...)
So this our customer want use the application with Access but want have a copy in SQL to make report etc etc...
July 5, 2012 at 8:13 am
Only thing I can think of is to do your initial load and a second load to a different DB.
Then to sync, load into that different DB and use something like SQL Data Compare or write your own merge scripts between the two databases, either that or drop and re-create the DB every time you want to sync it.
July 5, 2012 at 9:23 am
Thanks for your answer... probably I will choose to drop and to reload the SQL db.
Obviously not for each transaction but sometimes during the day.
July 5, 2012 at 4:02 pm
You should be able to link the tables to the SQL data from the access database. From my past experiences with doing this it will work and you get a nice performance improvement as well.
Link the SQL tables into the database,
transfer all data into the linked SQL tables
rename the existing MS Access tables
Rename the SQL tables to the original Access table names.
After performing those steps, the application should be none the wiser. Obviously you will need to test everything before putting out into production.
Hope it works or helps with a solution.
Nigel
Nigel H.
Infrastructure
Lockheed Martin Australia
July 6, 2012 at 3:37 am
Another option is to save your import package as a SSIS Package. This can then be edited to pick up only new data. The packaged then can be run on SQL server agent as a job at any time interval required.
Regards
NPC
July 6, 2012 at 3:46 am
This is a very interesting solution...
I already saved import package as SSIS but if I run it again it tell me that already exist an object named "Table Name" in the database.
Can you tell me where I can find documents that explain how to create SSIS to import only updates?
I searched information about this kind of solution but without success... :crying: :crying: :crying:
However thanks a lot for your suggestions.
July 6, 2012 at 4:06 am
The package has already created the table. You can use BIDS (business intelligence development studio) to edit the package. On the package there will be a SQL command that creates the table. You can insert a drop table command before this table is created. The other option is to take out the create table and use truncate table which will just delete all the data in the table.
July 6, 2012 at 4:37 am
Ah ok, I already evaluated this solution (drop and recreate DB every time using SSIS) but I'm searching if is possible import only the updates.
I'll do some test with your suggestions and I'll post the adopted solution.
July 6, 2012 at 4:38 am
Ah ok, I already evaluated this solution (drop and recreate DB every time using SSIS) but I'm searching if is possible import only the updates.
I'll do some test with your suggestions and I'll post the adopted solution.
July 6, 2012 at 6:36 am
I Created an SSIS Package. Amended the SQL Text on the source control data flow to below
SELECT ACCESS_2003_MYTABLE.*
FROM ACCESS_2003_MYTABLE
WHERE CREATED_DATE=DATE()
Destination on Data flow was TMP_MYTABLE.
The above places all data that was created today in a temp table in sql.
I added a sql command to truncate TMP_MYTABLE before the data flow was ran.
Below deletes anything that was in Tmp table out of MYTABLE and Adds them back in again. This will allow the process to be ran every few minutes depending on how long the query takes.
DELETE FROM dbo.[MYTABLE]
WHERE (ID IN (
SELECT dbo.[MYTABLE].ID
FROM dbo.[MYTABLE]
INNER JOIN dbo.TMP_MYTABLE
ON dbo.[MYTABLE].ID= dbo.TMP_MYTABLE.ID) )
GO
INSERT INTO dbo.[MYTABLE]
(
ID
,INVOICE_NO
,CREATED_DATE
)
SELECT ID
,INVOICE_NO
,CREATED_DATE
FROM dbo.TMP_MYTABLE
GO
At least this way you can see what data is created today and create a sql server agent job to update every 5 minutes for example.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply