Should be simple - Linked Server with Microsoft Access 1

[pcawdron]

Sorry for asking a dumb question, but I can't figure this out...

When setting up a linked server with Microsoft access 97, SQL Server asks for four details.

1.Provider Name = Microsoft Jet 4.0 OLE DB Provider
2.Product Name
3.Data Source
4.Provider String

I've used a .udl file to build the Provider String and tested it elsewhere. Didn't work with SQL Server. I set up a DSN in ODBCAD32 and tried that as the Data Source, didn't work. SQL help says Product Name is OLE DB Data Source to add as a linked server - what the hell is a OLE DB Data Source and what makes it differ from a regular DSN, Data Source Name?

Why is it so hard to connect to an Access database? You'd think Microsoft would have the foresight to make it simple... (frustration)

If anyone can outline the setting to connect Northwind.mdb (as an example) I would deeply appreciate it.

Thanks,
Peter

 

[RickCole]

Definition from book on line:

"An OLE DB provider is a dynamic-linked library (DLL) that manages and interacts with a specific data source. An OLE DB data source is any data file accessible through OLE DB."

About as clear as mud I know!! Try running the following from SQL from Query analyser as it works fine for me:


-- This will add the Northwind database as a linked server
-- called Linked_db.
-- @datasrc is the path name of where Northwind.mdb lives.

EXEC sp_addlinkedserver
@server = 'Linked_db',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'c:\folder\Northwind.mdb'

-- This creates a mapping to ensure that all logins
-- to the local server connect through to the linked server
-- without using a login or password.
-- Check sp_addlinkedsrvlogin in BOL as you might want to change this.

EXEC sp_addlinkedsrvlogin 'Linked_db', 'false'


-- This should return all the customers from Northwind.

SELECT * FROM OPENQUERY(Linked_db, 'SELECT * FROM customers')



-- This will tidy up the linked server and login when you finish

EXEC sp_droplinkedsrvlogin 'Linked_db',Null

EXEC sp_dropserver 'Linked_db'


Rick.

 

[pcawdron]

Excellent. Thanks for taking the time to respond to my question, I deeply appreciate your help.

(Do you know how to do the same with FoxPro Free Table directory? I've got it set up without any errors, but I can't query it. I think I need to provide a text file that servers as a catalog as a free table directory is not a database, as such. If not, I'll just use an Access DB as a staging DB between FoxPro & SQL Server)

Thanks again for your help...

Peter

 

[RickCole]

Sorry I've never touched FoxPro at all and don't have it at my site to try anything out on.

Might be worth posting this as a new thread and some else might take a look and hopefully be able to help out.

Rick.