Stored Procedure: get a value from MS-Access database

[kreskin]

Hello.

I'm trying to create a stored procedure which would compare an ID from external MS-Access table with an ID in a table within SQL and succeed or fail based on their comparison. Is there a way to do it using T-SQL, something like
select @var = max(ID) from \\server\database.mdb\tablename

Any help would be appreciated.

Igor.

 

[JohnDTampaBay]

You should be able to create a linked server to the Access database, then reference the Access table using the four-part object name Server.Database.Owner.Object. Another option might be OPENROWSET or OPENDATASOURCE; check out BOL. They should provide similar capability, but I am less familiar with them. HTH, Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[kreskin]

Hello,

Thanks for a quick reply. I ran the following in SQL Query analyser:

exec sp_addlinkedserver
@server = 'Rfax1',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = '\\Rfax\FaxRpt\Reports\Rfax.mdb'

It successfully created the link. However, when I try to browse the tables/views, the following error is displayed:

Error 7302: Could not create an instance of OLE DB Provider 'Microsoft.Jet.OLEDB.4.0'.
OLE DB error trace [Non-interface error: CoCreate of DSP for 'Microsoft.Jet.OLEDB.4.0 returned 0x80040154].

Any clue on how to fix that?

 

[kreskin]

Sorry I split this in two posts...

Running

SELECT a.*

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    '\\Rfax\FaxRpt\Reports\Rfax.mdb';'admin';'', Faxes) 
    AS a
GO

or

SELECT   *
FROM  OPENDATASOURCE(
 'Microsoft.Jet.OLEDB.4.0',
 'Data Source="\\Rfax\FaxRpt2\Reports\Rfax.mdb";
 User ID=Admin;Password='
 )...Table1

Gives the same error:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
OLE DB error trace [Non-interface error: CoCreate of DSO for Microsoft.Jet.OLEDB.4.0 returned 0x80040154].

I tried using my own username/password, but get the same error.

 

[kreskin]

Sorry for the confusion, I seem to get another error now. I looked it up and it seems to be an 'access denied' error. I tried my own login, with domain and everything, but to no avail...

SELECT   *
FROM  OPENDATASOURCE(
 'Microsoft.Jet.OLEDB.4.0',
 'Data Source="\\Rfax\FaxRpt2\Reports\Rfax2.mdb";
 User ID=admin;Password='
 )...Table1

returns

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'MSDASC'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSDASC returned 0x80070005].

 

[JohnDTampaBay]

The only KnowledgeBase article I found on error 7302 is in reference to linked servers to Oracle. The fix says to update MDAC.

http://support.microsoft.com/default.aspx?scid=kb;en-us;280106

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)