Excel spreadsheet as a linked server - HELP!

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hi! Sorry in advance for the length of this post! I have
an Access 2000 project (back end is SQL Server - I think
7.0) that I am administering but did not create. I would
appreciate help with a view that is to query an Excel
spreadsheet that is a linked server.

Although I 'appear' to have no problem linking to the
spreadsheet, when I try to run my query I keep getting an
ADO error ([OLE/DB provider returned message: Neither the
isolation level nor a strengthening of it is supported.]
Could not start a transaction for OLE DB
provider 'Microsoft.Jet.OLEDB.4.0.'). I tried following
the instructions in Knowledge base article 306397 but to
no avail.

This problem does not exist when the same spreadsheet is
first linked to an external Access database, which is
subsequently identified as the linked server.

The working stored procedure to link to the Access
database is as follows:

Alter Procedure procLinkINMAST
As
exec sp_addlinkedserver
@server = 'INMASTServer',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@catalog = 'INMastCatalog',
@datasrc = '\\macdiddy\shared\ItemMaster_sep10.mdb'
exec procLinkINMASTLogin
return

The SQL for the view that works based on the above linked
server is:

SELECT fpartno AS PNum, fdescript AS PDescription,
f2totcost AS PCost, frev AS Prev FROM
INMASTServer.. .INMAST WHERE (f2totcost > 0)

I changed the stored procedure to the following:

Alter Procedure procLinkINMAST
As
exec sp_addlinkedserver
@server = 'INMASTServer',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@provstr = 'Excel 5.0',
@datasrc = '\\macdiddy\shared\Itemmaster_sep9.xls'
exec procLinkINMASTLogin
return

I changed the view to:

SELECT fpartno AS PNum, fdescript AS PDescription,
f2totcost AS PCost, frev AS Prev FROM
INMASTServer.. .Sheet1$ WHERE (f2totcost > 0)

What am I missing??

Thanks for any and all assistance!
 
Oops! I got it working but I'm not clear on exactly what
happened.

Whenever I modify something in the database (form, view,
report) I make a copy of the original and work from the
copy - just in case I have problems with my mods. In this
case, the ADO error was occuring when I was trying to save
a COPY of the view. Once I simply modified the existing
query I had no issue!
 
Back
Top