Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-10
15
Medium Priority
?
583 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:casper114
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 35095187
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
 

Author Comment

by:casper114
ID: 35095328
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
 
LVL 28

Expert Comment

by:omgang
ID: 35095460
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 85
ID: 35095502
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
 

Author Comment

by:casper114
ID: 35100863
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35105834
>>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
 

Author Comment

by:casper114
ID: 35108602
AC,
     As coincidence would have it, I am running a 64 bit copy of SQL Server 2008.
0
 
LVL 28

Expert Comment

by:omgang
ID: 35109134
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
 
LVL 28

Accepted Solution

by:
omgang earned 750 total points
ID: 35109169
32-bit ODBC admin on 2008 Server 64-bit
C:\Windows\SysWOW64\odbcad32.exe
OM Gang
0
 

Author Comment

by:casper114
ID: 35112905
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 750 total points
ID: 35113667
>>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
 

Author Comment

by:casper114
ID: 35128672
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
 
LVL 28

Expert Comment

by:omgang
ID: 35130072
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
 

Author Comment

by:casper114
ID: 35234577
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
 

Author Closing Comment

by:casper114
ID: 35234601
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question