Avatar of casper114
casper114
Flag 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?
Microsoft AccessMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
casper114

8/22/2022 - Mon
omgang

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
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?
omgang

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Scott McDaniel (EE MVE )

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).
casper114

ASKER
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
Anthony Perkins

>>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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
casper114

ASKER
AC,
     As coincidence would have it, I am running a 64 bit copy of SQL Server 2008.
omgang

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
omgang

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
casper114

ASKER
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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
casper114

ASKER
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.
omgang

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
casper114

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
casper114

ASKER
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.