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.