Re: SQL 2005 linked server to Access 97

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I tried using Admin and '' that also did not work. I also tried this, without
using a linked server, it also does not work ON THE DEVELOPER EDITION ON WIN
XP Pro Sp2 sorry for shouting but it does work on Win 2003 Standard R2 Sp1
with SQL 2005 Standard. Is this a limitation of Developer Edition, or is this
a server config problem?

SELECT * FROM
OPENDATASOURCE
(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\BILL\Test97.mdb";User ID=Admin;Password=;'
)...Client

....it gives this message:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Cannot open database ''. It may not be a database that
your application recognizes, or the file may be corrupt.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I had to use the SAC Tool to enable this, but again, it works on Standard
with Server 2003 but not on Developer with XP. Any other ideas?

Thanks,
Bill

"Mary Chipman [MSFT]" wrote:

Access 97 is a tough nut because it's not supported any more. When you
run sp_addlinkedsrvlogin, try Admin with a blank '' password and see
if that helps.

By any chance is the A97 mdb you're trying to link to secured using
user-level security? That is a stumbling block with linked servers.
You can get around it by tinkering with registry entries, but if I
remember correctly, it only works for one mdb. IOW, if you have
another Access mdb you can't create a linked server to link to it.

-Mary

On Thu, 6 Dec 2007 17:47:00 -0800, wvmitchell
<wvmitchell@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Need to create a linked server from SQL 2005 to Access 97. I am using the
following:

EXEC master.dbo.sp_addlinkedserver
@server = N'TEST97',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'Microsoft Access',
@datasrc = N'C:\BILL\Test97.mdb'

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'TEST97',
@useself=N'False',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL
GO

Using Win 2003 R2 Standard with SQL 2005 Standard, it all works. But on dev
PC:
Win XP Pro SP2 with SQL 2005 Developer, it appears to create the linked
server, but cannot access any of the data in the Access file.

It works with an Access 2000 file on both systems. The Access 97 is a
3rd-party app, so can't upgrade the version.

Is this a limitation of Developer, or is there a config problem on dev PC?

Thanks, Bill Mitchell

.

Relevant Pages

  • Re: SQL 2005 linked server to Access 97
    ... I assume you're talking about the developer edition of SQLS 2005? ... I can't imagine what the difference would be between A97 ... using a linked server, it also does not work ON THE DEVELOPER EDITION ON WIN ...
    (microsoft.public.data.oledb)
  • Re: trying to create view on linked server
    ... > Linking only through the Access Project rather than OSQL or ... A linked server is a linked server. ... > to an external data source that you create in SQL Server. ... > table in an mdb. ...
    (microsoft.public.access.externaldata)
  • Re: trying to create view on linked server
    ... Linking only through the Access Project rather than OSQL or ... A linked server is a linked server. ... to an external data source that you create in SQL Server. ... In terms of your code and how it works with an mdb, ...
    (microsoft.public.access.externaldata)
  • Problems with linked server
    ... I have a view in SQLDB A that is built out of a linked server that points at ... an Access (mdb) database. ... The linked database is set that security should make connections 'without ... If I use another SQL authenticated user and make the same select I get: ...
    (microsoft.public.sqlserver.security)
  • Re: Crosstab Qry to ADP
    ... mdb as a linked server that will that will execute and return the result of ... Edit the mdb to, itself, have linked table to the original MS ... SQL Server (and sure, must have the crosstab pre defined, in the mdb). ...
    (microsoft.public.access.queries)