Solved

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

Posted on 2011-03-10
15
571 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
 
LVL 84
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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 250 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 250 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now