Help me Access my SQL!

March 14, 2013 7:35 AM

Ok, I have an application that is using a back end of MS Access...I have another application that needs to get some of the data in the Access db but it is only SQL compatible. So...how do I set up an ODBC connection that will pull data from the Access db and write it out to a SQL db? This data needs to be updated hourly... Thanks for your assistance!
posted by keep it tight to technology (11 answers total)

By "SQL", do you mean "Microsoft SQL Server"? Many users of that product acquire the odious habit of calling it "SQL", which is the name of a language standard, not a commercial database platform.
posted by thelonius at 7:39 AM on March 14 [1 favorite]

Anyway. Assuming that is what you do mean, doesn't SQL Server have the capability to link to an external data source as an ODBC table? You can do that in Access for almost any type of external data, and I'd be very surprised if SQL Server does not have provide a simple interface for doing the same thing. A linked Access table would then work the same as a local SQL Server table for most purposes, although it will be slower.

If you do not have control over the SQL Server database, and cannot change it like that, then you'll have to cobble together some kind of scheduled task that opens a connection to the Access database, reads a recordset of the table you need, and then opens a connection to the SQL Server database and updates it. In the MS world this is probably easiest to do with an ADO Connection object.
posted by thelonius at 8:09 AM on March 14

Sorry, I did mean MSSQL.

So what you are saying is that if I go into MSSQL, I should be able to link it directly back to MS Access from inside the MSSQL Administrator?
posted by keep it tight at 8:13 AM on March 14

I have not ever used MS SQL, but I'd really be amazed if there is not some way to easily do that. I mean, MS has code libraries for using ODBC - I have a ton of Access databases that link to Oracle tables, for example. I hope someone with experience in SQL Server comes along. Good luck!
posted by thelonius at 8:16 AM on March 14

This looks like the ticket in terms of the high-level mechanics. See Thomas Rushton's comment.

You would probably have to automate some stored procedures in SQL Server to keep the data up to date.
posted by Currer Belfry at 8:25 AM on March 14

Yeah, I think you're going to want to set up the Access DB as a linked server in your MSSQL db. This might be a decent starting point.
posted by capnsue at 8:36 AM on March 14

I really have to know, why is this one application using MS Access as the back end if you have a MS SQL server available?
posted by playertobenamedlater at 8:41 AM on March 14

player...its a legacy app that is only written in access
posted by keep it tight at 10:20 AM on March 14

Assuming your Access DB resides on a networked drive, yes, set it up as a linked server and then create a SQL Server Agent job to fetch your data, scheduled hourly.

Do you have a DBA at your place of work who you can talk to about this?
posted by trunk muffins at 11:56 AM on March 14

What versions?

The MS way of doing this would use SSIS.

That said: Can you not do it the other way around? I think it'd be easier to chuck together some VBA in the access database to update a linked table on the SQLServer. Then it's just about how you schedule the running of the VBA.
posted by pompomtom at 4:19 PM on March 14

You say that the back end is in Access--what is the front end? The same Access database, a separate Access database, a webpage...?
I would suggest moving all of the tables onto SQL Server, and re-pointing the front end to look there instead of the current Access database. Besides allowing the other application to see the data, you'll have fewer problems with locking and corruption, in my experience.

(I am responsible for a mission-critical database at my company, which we moved from Access to SQL Server last year. Unfortunately, the project fizzled out before a decent front-end was built, so we still touch the data primarily through Access front-ends that each user has built for hirself.)
posted by Tool of the Conspiracy at 6:02 PM on March 14