Link to home
Start Free TrialLog in
Avatar of casper114
casper114Flag for United States of America

asked on

How can I connect SQL 2008 to an active Access 2003 database?

I had a request come in to me yesterday to see if I could create an intranet page for employees to go to where they could run reports on their clock-in & clock-out times. Creating the report is no big deal, but the problem is that I use SQL Reports to create and publish reports to our intranet. We are currently running SQL Server 2008 and the access control system we use to track the in/out times stores its data in an Access database. I'm trying to find a way to connect to the Access database through SQL and to pull the existing data into SQL and then to keep it updated for all future use of the clock-in/clock-out data. Can anybody help me with this?
Avatar of omgang
omgang
Flag of United States of America image

Linked server to the MS Access db?  I just setup a linked server (in SQL Server 2008) to an Oracle db for the same purpose.  Once the linked server is created you'll be able to query the tables in the MS Access db from withing SQL server.
OM Gang
Avatar of casper114

ASKER

Om Gang, I had tried that yesterday, but I set it up the provider as Microsoft.Jet.OLEDB.4.0 and when I try to test the connection I get an error stating that provider can not be used for distributed queries because the provider is configured to run in single-threaded apartment mode. What form of provider are you setting up for your Oracle db?
I am using MS OLE DB Provider for ODBC Drivers.  I created a DSN on the local machine using the Oracle Instant client and then specified that DSN as the Data Source in the Linked Server config.  Give that a shot.
OM Gang
Here's a help topic that may provide more details:

http://office.microsoft.com/en-us/access-help/import-or-link-to-sql-server-data-HA010200494.aspx

It's fairly simple to do, as omgang indicates. Make sure that you've installed the correct drivers on the machine where you're doing this, and if you plan on distributing the Access file to others, make sure they also have the necessary drivers (and you'll have to move the DSN to those machines as well).
Om Gang, what kind of DSN was created by the Oracle Instant client? By that, I mean was it a system DSN, or one of the others? It seems as though tthe DSN should be a system DSN to me, but I don't have the option to select anything other than a SQL DSN type under the System DSN options on my SQL 2008 server. I'm able to setup the ODBC just fine from the MS Access side (resides on a different machine) but that's not getting me anywhere when I try to create the Linked server on the SQL server. I hate to ask this, but can you walk me through the type or even show me a screen shot of the settings for your Oracle ODBC? I don't usually have a lot of trouble with this kind of thing, but this item is kicking my tail today
>>I had tried that yesterday, but I set it up the provider as Microsoft.Jet.OLEDB.4.0 and when I try to test the connection I get an error<<
Is it SQL Server 2008 64 bit if so that would explain the error.  There is no 64-bit JET provider.
AC,
     As coincidence would have it, I am running a 64 bit copy of SQL Server 2008.
On a totally different project I'm working on the client has stood up a new Win 2008 server 64-bit and I am porting an Access application that integrates with Mas90.  I had to use the 32-bit Data Source Administrator on the server to create the 32-bit DSN for the Mas90 application.  I don't know if this is applicable to the 64-bit SQL Server you are working with.  I can't recall off the top of my head what the path is to the 32-bit ODBC Admin but I'll see if I can find it for you.

To your question earlier I did create a System DSN using the Oracle Instant Client.
OM Gang
ASKER CERTIFIED SOLUTION
Avatar of omgang
omgang
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OM gang, thanks for the heads up. That actually got me further. However, even with using all the Access options that deal with *.mdb's I still can't get this working write. I keep running into "The specified DSN contains an architecture mismatch between the Driver and Application". I've found that the Access Control System is using an Access 2000 format to store it's data and some articles that I've looked up seem to indicate that SQL Server 2008 R2 is no longer compatible with Access 200 formats. Does anybody know if this is true?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
AC, I've dumped trying to use Jet due to the indication that it's not supported for 2008. However, even with all the Access options available to me (3 or 4 of them) under the 32 bit odbc's, I still get the above error.
casper114, I have also been trying to create a linked server to an Access database both .mdb and .accdb formats and have been unsuccessful.  I've been trying on a Win 7 Pro 32-bit machine with SQL Server 2008 R2 32-bit.  I think you want to follow acperkins suggestion and look at accomplishing your goal without using a linked server.
OM Gang
unfortunately I was not able to find my solution, and the requestors determined that I should cease looking into it as it was taking more effort trying to determine how to accomplish it than they wanted me to expend.
everyone on this question was helpful. Unfortunately though, I just was not able to find the answer I was looking for before the project was retracted.