April 22, 2015 at 2:42 pm
I have got 4 MS Access Database Files, which have got 3 Tables each, means Total 12 Tables which gets updated with new data every evening, by an external application. Means new data gets appended to all these 12 Tables.
I want to have exact same 4 Databases, which have got 3 Tables each, means Total 12 Tables, but WITHIN MS SQL SERVER. And then update all of these 12 Tables every evening, with the corresponding updates from the respective tables from the MS Access Databases.
Please suggest me the various options to get this kind of work done in SQL Server. I do not want to Manually Update all these 12 tables every evening into SQL Server. Hopefully there would be some easier method to do this in automatic manner.
Thanks a lot for any ideas.
April 22, 2015 at 2:48 pm
tlp6327 (4/22/2015)
I have got 4 MS Access Database Files, which have got 3 Tables each, means Total 12 Tables which gets updated with new data every evening, by an external application. Means new data gets appended to all these 12 Tables.I want to have exact same 4 Databases, which have got 3 Tables each, means Total 12 Tables, but WITHIN MS SQL SERVER. And then update all of these 12 Tables every evening, with the corresponding updates from the respective tables from the MS Access Databases.
Please suggest me the various options to get this kind of work done in SQL Server. I do not want to Manually Update all these 12 tables every evening into SQL Server. Hopefully there would be some easier method to do this in automatic manner.
Thanks a lot for any ideas.
I would just drop the Access databases and do this directly to sql server unless there is a solid reason to need to keep Access around. There are a number of ways to get data from an external source into sql server.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2015 at 9:25 pm
+1 to the idea of removing Access from the equation if possible and having the data provider write to SQL Server. If that is not possible then look into converting the tables in Access to Linked Tables that reference tables in a SQL Server database.
http://www.mssqltips.com/sqlservertip/1480/configure-microsoft-access-linked-tables-with-a-sql-server-database[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2015 at 12:18 am
Sean Lange (4/22/2015)
tlp6327 (4/22/2015)
I have got 4 MS Access Database Files, which have got 3 Tables each, means Total 12 Tables which gets updated with new data every evening, by an external application. Means new data gets appended to all these 12 Tables.I want to have exact same 4 Databases, which have got 3 Tables each, means Total 12 Tables, but WITHIN MS SQL SERVER. And then update all of these 12 Tables every evening, with the corresponding updates from the respective tables from the MS Access Databases.
Please suggest me the various options to get this kind of work done in SQL Server. I do not want to Manually Update all these 12 tables every evening into SQL Server. Hopefully there would be some easier method to do this in automatic manner.
Thanks a lot for any ideas.
I would just drop the Access databases and do this directly to sql server unless there is a solid reason to need to keep Access around. There are a number of ways to get data from an external source into sql server.
Thanks for your reply Sean Lange.
No, there is no way to update the SQL Server with that application. The data will get updated only into Access Database and we need to find a way to get that into SQL Server from there.
April 23, 2015 at 12:20 am
Orlando Colamatteo (4/22/2015)
+1 to the idea of removing Access from the equation if possible and having the data provider write to SQL Server. If that is not possible then look into converting the tables in Access to Linked Tables that reference tables in a SQL Server database.http://www.mssqltips.com/sqlservertip/1480/configure-microsoft-access-linked-tables-with-a-sql-server-database[/url]
Thanks for the idea of linked tables Orlando Colamatteo. I will try it to see if that can solve my current problem problem.
Regards
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply