Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 939
  • Last Modified:

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

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
saikit
Asked:
saikit
  • 3
  • 2
1 Solution
 
chrhedgaCommented:
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
 
saikitAuthor Commented:
The MDB file is Access 2000.

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

Thanks.
0
 
saikitAuthor Commented:
How to specify the MDW file for the Access 2000 MDB for the linkedserver ?
0
 
chrhedgaCommented:
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
 
saikitAuthor Commented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now