I would like to know that where should I start if I want to transfer the MS access database to SQL server. First of all is this some thing that I can do using Express Edition SQL server 2008 if I install separate tools for SSIS? Or else could you please suggest me a way to do it and what version of SQL server is needed. Apart from that what is the best approach to transfer data from access database to SQL server and push this information on regular bases when ever new data is added in ACCESS database.
-
FYI, see meta.stackexchange.com/questions/2950/…John Saunders– John Saunders2011-10-29 23:20:43 +00:00Commented Oct 29, 2011 at 23:20
Add a comment
|
1 Answer
what is the best approach to transfer data from access database to SQL server and push this information on regular bases when ever new data is added in ACCESS database
IMO the best approach would be to move your Access tables to SQL Server. Access includes an Upsizing Wizard for this purpose. There is also Microsoft SQL Server Migration Assistant for Access, which is more capable than the Access Upsizing Wizard.
Afterward, in your Access application, replace the native Access (Jet/ACE) tables with links to the SQL Server tables.
The point of this suggestion is that you needn't bother about reconciling data changes between Access and SQL Server --- the data is kept in SQL Server, you can access that data from your Access application without any need to keep redundant copies in Access' storage format.
2 Comments
Ryan
You advice is helpful, but the reason I am not getting rid of access is because the client application is access based and I want to transfer everything into SQL because so that I can create views for some of the crystal reports that I am generating, we will start using SQL but gradually so for now I will have to pull information from access database. I like the idea of using SSMA and want to confirm that is there a way if I use SSMA and then I should be able to transfer the new data in access to SQL server. Please let me know.
HansUp
SSMA is intended to migrate Access objects to SQL Server, as a one time operation. But for the job of routinely exchanging data between the two databases, I think SSMA is the wrong tool.