?
Solved

Can I query MDB file via SQL server (not convert to MDF)

Posted on 2001-07-25
5
Medium Priority
?
932 Views
Last Modified: 2013-12-05
I use MS SQL 7 and have a MDB file in its local harddisk.

If I don't convert my MDB file to MDF, can I query this MDB file via SQL server?

Thank you very much.
0
Comment
Question by:saikit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 1

Expert Comment

by:chrhedga
ID: 6316851
First set up the link:

sp_addlinkedserver 'Nwind', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'c:\mydata\Nwind.mdb'

Next, logininfo:

sp_addlinkedsrvlogin 'Nwind', false, 'Joe', 'Admin', NULL

Query like this:

SELECT * FROM Nwind...Employees
0
 

Author Comment

by:saikit
ID: 6316974
The MDB file is Access 2000.

I'm new to SQL server, would you please give more details on doing this?

Thanks.
0
 

Author Comment

by:saikit
ID: 6317069
How to specify the MDW file for the Access 2000 MDB for the linkedserver ?
0
 
LVL 1

Accepted Solution

by:
chrhedga earned 800 total points
ID: 6317342
Sorry, I am no expert on Access (actually I don't think I've ever created a table in access... ) so I don't know what a MDW file is. I got it to work by doing this:

1. I created an Access database, with one table called foo. This table had one column called bar of type Number (Long Integer). I added a few rows to this table in Access and then closed Access.

2. I executed the following code in SQL Server (SQL Server running on the same machine as the Access database was placed on, make sure this machine has the Jet provider installed):

EXEC sp_addlinkedserver
   @server = 'AccessTest',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:\db1.mdb'
GO

sp_addlinkedsrvlogin 'AccessTest', false, 'sa', 'Admin', NULL
go

select * from AccessTest...foo

This resulted in the following showing up in my results window of Query Analyzer:

bar        
-----------
2
6
81
5
54
0
 

Author Comment

by:saikit
ID: 6639173
Thanks. I followed you example and successed.

My Client program is using DSN (SQL server driver) to connect the SQL server, but the LinkedServer doesn't showing in the database list of the SQL server.

How to make linked server showing in the DB list?


Thanks.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 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