I have been trying to explore different ways of populating a SQLServer database with data from a fairly complex multiuser Microsoft Access application, with a view to migrating the application to using SQLServer for its primary data server. The application is split into front end accessing a main backend database (and a couple of other smaller backed ones), and is protected via a workgroup file that is NOT the standard system.mdw.
Using SQLServer Management Studio, I was attempting to import the data. However all attempts at that were failing because I was not specifying the workgroup file and so the Jet db engine was (rightly) rejecting attempts to connect.
Although I would have assumed this is a fairly standard operation – since the recommendation to secure your Access application with a new workgroup file is fairly strong, I was unable to find much information about this issue on the internet. However after a bit of digging I did finally find how to achieve it, so I thought I would add a post here to show how its done.
Rather than embed individual pictures of the different screenshots in the Post, I have included it as a gallery at the end. Click on any picture to bring up a lightbox with the complete set.
The first set is to right click on the database you wish to import the tables into and select the “tasks” menu entry followed by the import data entry – as shown in the first picture.
We then have to select our data source. As shown in the second picture, we select Microsoft Access from the “Data Source” drop down list, and also select the filename of the .mdb file we wish to import from. It is also necessary to enter a valid username and password for the database that we are going to connect to. But as yet we have not told the import wizard about our workgroup file, so we do that now by hitting the “Advanced” button.
This brings up the Data Link Properties dialog box – just ensure in the Password box off the connection tab that the password is entered and the “Allow saving password” box is checked, but the “Blank password” checkbox isn’t. Switch to the “All” tab of this dialog box as shown in our third picture. We need to select the line shown in this picture (“Jet OLEDB:System Database”) before hitting the “Edit Value” button to bring up the “Edit Property Value” dialog box in our forth picture. Enter the path to your workgroup file here.
You can now hit “OK” for each dialog box until you are out at the Choose Data Source (second picture) page again. Now hit the “Next” button which should bring up the fifth picture where we choose our destination database, and the connection user we are going to connect with. Ensure you have a user who has the rights to add a table to the database.
Hitting “Next” again will now bring up the page to choose whether to copy a table or write a query to define the source (see sixth picture). It is normal to select “Copy data from one or more tables or views” and hit the “Next” button, which brings us to the seventh picture where we can choose which tables to use and map them to destination tables in the database.
Finally hitting “Next” twice (we are selecting to run the query immediately) We can start the copy processes where progress can be see (see last picture) the copying taking place.
I hope you can replicate the process and find it useful