I have been tasked with working with an Access 2010 database. this database needs to be available to non-technical users for basic data entry, and the CEO of my company needs to be able to run reports off of dynamic data. My first thought was to make a custom ASP.NET solution off of the database back-end, but let’s face it- that’s a lot of work. So I decided upon another route.
- The database will be split in Access into a front end and back-end.
- The front-end’s will be turned binary and distributed to multiple users.
- In the front-end’s, our designer will create the forms needed for users to input information.
- We will link the back-end access file to an instance of SQL.
- The back-end file will remain editable, for the database creator to add data and fields.
- SQL Server Reporting Services will connect to the linked SQL database.
- I will pre-configure reports for the CEO.
This article takes into account that you know a thing or two about computers.
- How to share and distribute an item.
- The differences between file formats (.accdb, .mdb, .accde)
- You have all the required software installed and configured (SQL Non-Express, Access, IIS 6+).
- You are willing to use new tools.
- You are familiar with connection strings and working with database connections.
My set up (And you should have most of the items here, or at least comparable).
Member server running Windows Server 2008 R2.
SQL Server 2008 R2 Enterprise- most full editions will work.
SQL Server Reporting Services- this will not run on some versions of Express, and I had trouble getting it installed on a Small Business Server 2008 server.
Micrososft Access 2010. Access 2010 Help.
We are going to download a few more items to make this easier. These items are not always necessary, but just the way I went about it with limited database knowledge.
Download and install SQL Server Migration Assistant 2008 for Access from Microsoft here.
Download and install Microsoft Access Runtime 2010 here. You should not need this if you have Access 2010 installed (which I did and do), but the installer for the first tool told me to do it, so I did 😛
Optionally, I also installed Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies.
You need to have your Reporting Server up and running, and configured. For help on doing this, visit this page. Once you are set up, make sure your Report services is running of an SQL instance. For this walk-through, mine is named
My websites (automatically configured by SSRS) are set as
Create a Shared Drive on your server or on a secure PC. Enable the drive to be accessible by Everyone, or Authenticated Users. I created my share on the member servers C: drive and named it theDatabase (UNC location: \\memberserver\thedatabase).
I am going to drop my Access 2010 database file there, which is still in .accdb form. The database has roughly 6 tables, 300 rows in the main table, and 20 columns. It will receive roughly 6 entries per week max, from no more than 2 users. As you can see- small scale project.
Inside of this shared folder, create a folder named Backup. At this time, before we do anything, you should make a backup of your Access database, make the filename into todays date and time, and place it in this folder. We will create several backups throughout this process, and they should all follow this format and location.
With the file in the right place, and backed up, open up the tool we installed SSMA 2008 for Access.
You are presented with a wizard (I love wizards) that tells you the upcoming steps. Let’s use it:
Enter a name for the project (not the database or connection). I named mine SQLMigration1. Make sure the last box is set on SQL (for this blog post. You can change this if you are doing other sources).
Click Add Database (or find database, but we know where it is). In the popup, browse to the folder we created and placed the database in, then select it and click Open.
You might at this point receive an error. Reading this error, you can upgrade your connectivity drivers, or you can re-run the program. Mine was running in 32-bit mode (shortcut on desktop). To run in 64-bit mode, go to the file location and double-click the .exe file without the 32 on it.
The default location is:
“C:\Microsoft SQL Server Migration Assistant 2008 for Access\bin\SSMAforAccess.exe”
Repeat the steps and it should complete successfully. Select the items that you are going to import or link (in my case) to SQL. I selected both of the queries and all the tables.
Now type in your destination SQL server information.
Server name: MEMBERSERVER\REPORTING
Server Port: [default]
Authentication: Windows Authentication.
Since there is no database named WIMS in that instance of SQL, the wizards asks about creating it. I select yes.
If you want to link it, check the box. Read the paragraph for more information.
Let the tool run. You will get a popup about metadata, triggers, indexes, etc. Just click ok and let it run.
After it finishes, I have a boatload of errors- so we will send the database back to the creator to fix, but we will continue the process with this database, today.
At this point if you open up the database in access, you should see all of the linked tables. You will also see your database does exist in SQL Management Studio.
Close out Access, and close out SSMA. Save your project. Now backup (copy) your database into the backup folder.
Now browse to your Reporting Server site (http://memberserver/reports).
I have created a few folders here, do not mind them. On the link bar in the middle of your screen click New Data Source.
Now customize this page to fit your set up. Here is a link on how to create the connection string portion. the security will be of your own choosing, I use Windows Integrated.
When you are finished, Test it (you will see a green success or a red error message), then click Ok.
You will then see an icon for the Database on your home view- oops! I do not want users to see that. click the little arrow next to the database, select manage. check the box for hide in tiled view and click apply changes, then navigate to home again.
Now click on Report Builder, and get to making your reports. I will get into that in my next post, mostly because I do not know how to use the tool well as of yet.
Now we are going to split our linked Access Database into a front and back-end. We will leave the back end in the shared folder, and distribute the front end. when users make changes to the front end, they trickle down to the back-end, which replicates to SQL, and is then displayed in your reporting.
Open up your Access database. Click on Database Tools Ribbon, and then select Move Data > Access database.
Select Split Database. Save it, I typically leave the default formatting alone- which is to add _be (back-end) to the file name. This is the file you leave alone. you now give out copies of the original file for people to access the database.
You can make this a compiled project by saving it as accde, in the file menu> Save and Send, then save as ACCDE.