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?
casper114Asked:
Who is Participating?
 
omgangIT ManagerCommented:
32-bit ODBC admin on 2008 Server 64-bit
C:\Windows\SysWOW64\odbcad32.exe
OM Gang
0
 
omgangIT ManagerCommented:
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
0
 
casper114Author Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
omgangIT ManagerCommented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
0
 
casper114Author Commented:
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
0
 
Anthony PerkinsCommented:
>>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.
0
 
casper114Author Commented:
AC,
     As coincidence would have it, I am running a 64 bit copy of SQL Server 2008.
0
 
omgangIT ManagerCommented:
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
0
 
casper114Author Commented:
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?
0
 
Anthony PerkinsCommented:
>>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? <<
It depends.  As I indicated previously, you cannot use JET to create a linked server or use OPENROWSET as you are using SQL Server 64-bit.  This should not stop you from using something like SSIS to import the data into MS SQL Server from MS Access.
0
 
casper114Author Commented:
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.
0
 
omgangIT ManagerCommented:
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
0
 
casper114Author Commented:
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.
0
 
casper114Author Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.