October 27, 2022 at 10:38 pm
We have a department that has quite a few Microsoft access databases. MDB files. They don't have resources to build applications so they created Access db to use with forms and reports on it.
They would like to export data from some of the tables into SQL server, so that they can use together with other existing database and data source on the SQL server to make reports.
Problem is there are so many tables, even I think I can create SSIS packages to export to SQL server and automate it to run daily, but wondering if that is the good approach. The other solution I am thinking is to use linked server to link to MS access database with readonly not sure if this will affect MS access database performance or not.
Any other solutions without investing too much time?
Thanks.
October 27, 2022 at 10:52 pm
Not much to go on here. Are the databases radically different (no similar tables)? Or do they have lots of similar / same tables?
one way of doing this is to create a "front end" of sorts in Access that has a connection to the SQL Server table(s) you want to write to, and then loop through the local Access database tables and append from those. (You can untwist the screwy tables using VBA, if you need to, and write the data to a "temporary" table in your "processing database" and then dump all the data to SQL Server after that.
October 27, 2022 at 11:08 pm
have a look at https://learn.microsoft.com/en-us/sql/ssma/access/sql-server-migration-assistant-for-access-accesstosql?view=sql-server-ver16
you can have your Access db working normally with the tables on a SQL Server instance.
few caveats - the tables that the tool creates is not always the best in terms of datatypes - but you can change them and adjust datatypes and Access will accept it afterwards without any issues.
November 4, 2022 at 6:16 am
Hopefully the department will have built their apps to hold the forms in one Access db and the data in a second Access db.
If not then the first step is to do this, it will make maintenance of the app much easier and help support more users running the app. It also allows you to migrate the data db from Access to SQL.
The advice given by frederico will help with the migration. The end state will be one Access db hosting the forms that connects to a SQL db hosting the data. You can then join the SQL db with other dbs to get your reporting done.
Enjoy.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 10, 2022 at 6:36 pm
I found they have multiple access database with different purposes. And some of the Access databases have only forms, tables are connected with another Access mdb databases. and some tables connected with Excel spreadsheet.
I know the best way probably is to setup tables in SQL server and Access is only from front end. But that seems a project that need some time.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply