Linked servers

[bloke]

Hi

Having difficulty with adding an MS Access database to sqlserver 2000 as a linked server.

Well, that's not strictly true! I have done it and it appears on sqlserver and I can query the tables in query analyser using:

select * FROM OPENQUERY([CRONERS], 'SELECT * FROM TRAINEE')

..and that's fine.

The problem I'm having is querying this in asp. I don't know if I'm barking up the wrong tree but I think my problem may be to do with DSN's. Please correct me if I'm wrong.

I'm trying this:

gstrConCroners = "DSN=CRONERS"

dim rsX
set rsX = server.CreateObject("ADODB.Recordset")
rsX.Open "select * FROM OPENQUERY([CRONERS], 'SELECT * FROM TRAINEE')", gstrConCroners

response.write rsX.fields("forename") & " " & rsX.fields("surname")
response.end

but it falls over on the rsX.Open line with the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

I'm lost. Please help!

Cheers

[bloke]

Hmm, that's what I thought but I can't see whats wrong with it.

I'll press on. At least I'm heading in the right direction, which is nice!

Cheers

[bloke]

Ok, so I finally admit defeat.

I've tried setting up a file dsn using:

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
ReadOnly=0
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=512
ImplicitCommitSync=Yes
FIL=MS Access
DriverId=25

Pointing this at the database via the control panel then setting the connection string to:

gstrConCroners = "filedsn=c:\Program Files\Common Files\ODBC\Data Sources\croners.dsn;DBQ=M:\H&S\Croner\Program Files\MyFile\MyDB.mdb;UID=blah;PWD=blah;"

Followed by:

dim rsX
set rsX = server.CreateObject("ADODB.Recordset")
rsX.Open "select * FROM OPENQUERY([CRONERS], 'SELECT * FROM TRAINEE')", gstrConCroners

response.write rsX.fields("forename") & " " & rsX.fields("surname")
response.end

...and I get exactly the same error.

I'm guessing there is something blatantly obvious that I am blissfully unaware of that is causing my problem but I'm starting to go a bit crosseyed now!

ps desperate now so muchos helpos appreciated!

Cheers

[bloke]

Doing that as we speak, so to speak. Seems to be a pretty generic one though......

I know it's gotta be something really obvious - I just don't know what!

[bloke]

Yes, it's our own server. I have set up the DSN on the server.

[bloke]

Still not sorted this so if anyone has any clues, I'd be most grateful.

Maybe if I explain a bit better(!) There is an MS Access database on server A. The asp is running on server B with SQLServer. I have managed to link SQLServer on server B to the MS Access database on server A and I can query the database using Query Analyser - no problem.

The problem comes when I try to execute a query in asp on server B.

If someone could explain from scratch how I should do this, I should then be able to see where I went wrong!



Cheers