Best Currently Known Practice for Integrating Access Databases for Analysis with MS SQL

Currently at my school, we have a need to pull data from software that uses a Microsoft Access back-end.  One of the challenges of this, is that we can not tinker generally around with the MS Access database directly, because it will void any support we want to get, and it usually stops the product from working.   So, I’ve wanted to be able to have the information go into our MS SQL server, so I can access it with our Meta-Database (I talked a little about this idea in my previous post.)

After working on this problem for 20+ hours, and going down a lot of wrong paths, I found a solution that works.  To possibly help others in the same situation, I am writing this blog entry, to not only hopefully help you quickly to get to a solution, but at the same time to help you know why the other paths didn’t work for me, so that you can avoid them, or if you decide to tread down them, that you have some forewarning… 🙂

Final Solution: Linked Servers with Microsoft OLE DB Provider for ODBC and OpenQuery

After going down a lot of bad roads, I found that by creating an ODBC connection to my Access Database, and then using the Microsoft OLE DB Provider for ODBC to attach to that ODBC driver, I was able to get to my data.  But for some reason, I could not still directly query the data, but I could use the OpenQuery command to extract the data, and then use this to make the mirror views in my Meta-Database.

Step 1 – Setup ODBC

I made a System DSN that pointed to my access file.  Since my access file was on another server, I had a hard time typing in the UNC to get to it.  But I was able to get around this by pointing to it locally at first, and then doing a registry hack to point the UNC later.   I also found that if chose “read-only” in the ODBC driver, it screwed things up (I forget the exact error message off-hand, but it took a bit of searching forums to find that this caused problems.)

Step 2 – Setup the Linked Server

I used the following code to create my linked server

EXEC sp_addlinkedserver 
    @server = N'Name_to_use_as_Linked_Server', 
    @provider = N'MSDASQL', 
    @srvproduct = N'Access',
    @datasrc = N'ODBC_Name',
    @catalog = N'Path_to_MDB'
GO
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = N'Name_to_use_as_Linked_Server',
    @useself = N'FALSE',
    @locallogin = NULL, 
    @rmtuser = NULL, 
    @rmtpassword = N'Database_Password' 
GO

In this code the Name_to_use_as_Linked_Server should be whatever name you want to reference the database with later in MS SQL.   For simplicity sake, I generally name my ODBC connection and Linked Server, the same as the file name of the MDB.  So if I was connecting to sample.mdb, I would call all of those sample.

The @rvproduct apparently doesn’t really matter,   But the ODBC_Name does, although again, if you just use the same name as the MDB then this is easy.   An interesting thing to note is that the Path_to_MDB does not need to be what is defined in the ODBC driver.   So in my case I actually had multiple Linked Servers to Multiple MDBs all using the same ODBC connection (and so I actually broke my rule about having the ODBC driver having the same name as the Linked Server.)

I also was working with a password protected MDB, which made me go down a lot more wrong paths, as I figured that part out.  But by leaving the @rmtuser being NULL and then just having a @rmtpassword, it worked fine.  I suspect that if there were user accounts and passwords on the MS Access files, then the @rmtuser would need to be one of them, and you might run into issues with the System.mdw

Step 3 – Create the Mirror Views using OpenQuery

For some reason, I could never get my queries to work directly against the MS Access linked server.  I got a variety of errors, that all were ambiguous, and that I couldn’t track down a solution from any web searches.  But I found that if I used the OpenQuery command to run the query through the MDB instead of running it through MS SQL, that it actually would return what I was looking for, and by putting this into a view, I could then treat it as a normal MS SQL table (for the most part) in the future.

Here was the syntax I used to make the Mirror Views:

Use Database_Name
GO
CREATE VIEW View_Name AS 
SELECT * FROM OPENQUERY(Name_of_Linked_Server, 'SELECT * From Table_Name')
GO

Where Database_Name is the name of the Meta-Database, and View_Name is the name of the mirror View.  I use naming convention of LinkedServer_Table, thus if I had a linked server named EmployeeDB, and a table named Employees, I would name the Mirror View: EmployeeDB_Employees

So, doing these steps, the process worked, and I’m happy, because it does not seem to conflict with the third party software that uses the MS Access back-end.   And all the updates are real-time, so that when the third party software changes something, the mirror views can instantly see these changes.  Next, lets look at all the other paths I explored before coming to this solution.

Method that Doesn’t Work: Using the Microsoft OLE DB Provider for Microsoft Jet

This method would have probably worked if I had a 32-bit version of MS SQL server that we were using.  But we are using a 64-bit version, and Microsoft does not make a 64-bit OLE DB Jet driver.   So while the supplied template explorer still attempts to do this, it just doesn’t work.   But after reading a lot of forum articles, it looked like using a new ACE provider might work…. But….

Method that Doesn’t Work: Using the Microsoft Access Database Engine 2010

There is now a redistributable 64-Bit OLE DB driver that in theory should work for Access Databases, it is listed as the Microsoft.ACE.OLEDB.12.0  and it can be found at http://www.microsoft.com/en-us/download/details.aspx?id=13255

But, I just couldn’t get it to work, and apparently neither could a lot of other people, because it is now reported as a bug on Microsoft Connect. And the only work-around that has been given is to use the ODBC method, hence that is why I went with that route.

Methods that Would Not Have Worked as Well: Replicating to the SQL Server

Before I got the Linked Server to work, we previously manually replicated the data from the Access Databases to the SQL server on a daily basis.  This was not an optimal method because it was not automated, and the data was not real-time when I did analysis, or needed it for things.   But, because I knew linked servers were a pain, and originally I wasn’t sure about which route would be best, I explored looking at replication as a solution.

There are two major ways I explored doing replication, either pulling in data through SQL Server Integration Services (SSIS) or pushing the data to the server using Microsoft Access.   Of these two options the SSIS is clearly the best for automating, but would have required more development time, and because I am not familiar with the system, it would have taken more time for me to learn.

Using Microsoft Access to push the data, is the best manual process, as Access 2010 has a very good upsizing wizard, but this wizard can not be automated easily.  (It probably could with macros, but probably needing a 3rd party macro software to do the key presses.)   Access also has methods that could use VBA, but it would require having one database open the other, and then do those methods on the other database.  This could be workable, but not as good as SSIS, and as previously stated, replication even with SSIS, is not as good of an option as the real-time access that linked servers can do.

One Comment

  1. Kevin Hall

    Great info Jacob! Thank you for your efforts here.

Leave a Comment

Your email address will not be published. Required fields are marked *