why can't I access a linked table, when a local table works fine?

[bazango]

Hi.

I am trying to connect to a linked table in Access. My VBscript
connection looks like this:

cnn.Open "driver={Microsoft Access Driver (*.mdb)};DBQ="&
Server.MapPath("myDB.mdb")

Then my query:

sqltext = "SELECT * FROM myLinkedTable"

The error:

The Microsoft Jet database engine cannot open the file
'\\myPath\myLinkedTable.xls' It is already opened exclusively by another
user, or you need permission to view its data.

But... it's not open and permissions are set correctly. If I substitute a
local table for the linked one, it works fine. What am I doing wrong? Coming
to work in the morning? :-/

Sure appreciate any help.

Thanks.

 

[John W. Vinson]

The Microsoft Jet database engine cannot open the file
'\\myPath\myLinkedTable.xls' It is already opened exclusively by another
user, or you need permission to view its data.

The other user may be *you* - are you perhaps connecting to the spreadsheet
twice from your application? For instance, if you have it open as a linked
table in the Tables window already, you'll get this message.

John W. Vinson [MVP]

 

[bazango]

The other user may be *you* - are you perhaps connecting to the spreadsheet
twice from your application? For instance, if you have it open as a linked
table in the Tables window already, you'll get this message.

John W. Vinson [MVP]

Yeah, no. I even tried closing the mdb.

As for connecting twice, would a linked table behave differently from a
local one in this respect? If I substitute a local table of the same name, it
works fine.

At any rate, I only have one "cnn.Open "driver={Microsoft Access Driver
(*.mdb)};DBQ="& Server.MapPath("myfile.mdb")," so I don't think that's it.

So... what I'm trying to do, use VBscript to connect to a linked table in
access... it is doable, right?

 

[John W. Vinson]

So... what I'm trying to do, use VBscript to connect to a linked table in
access... it is doable, right?

I don't know; I've never used VBScript from within Access since there are
other much simpler ways to connect.

What's the context? Are you doing this in an Access .mdb? a Data Access Page?
a webpage? or what?

John W. Vinson [MVP]

 

[Douglas J. Steele]

Yeah, no. I even tried closing the mdb.

As for connecting twice, would a linked table behave differently from a
local one in this respect? If I substitute a local table of the same name,
it
works fine.

At any rate, I only have one "cnn.Open "driver={Microsoft Access Driver
(*.mdb)};DBQ="& Server.MapPath("myfile.mdb")," so I don't think that's it.

Look at the error message you're getting. It's talking about
'\\myPath\myLinkedTable.xls', not myfile.mdb.

Or are you saying that myfile.mdb is what's linked to
'\\myPath\myLinkedTable.xls'? (In other words, you've got something.mdb
linked to myfile.mdb linked to '\\myPath\myLinkedTable.xls') In that case,
then I believe that technically you DO have two separate connections to the
spreadsheet (even if myfile.mdb isn't explicitly opened)

 

[bazango]

I don't know; I've never used VBScript from within Access since there are
other much simpler ways to connect.

What's the context? Are you doing this in an Access .mdb? a Data Access Page?
a webpage? or what?

John W. Vinson [MVP]

From a webpage. Just trying to serve up a list of citations on the interweb,
from an Access mdb. But since tables in access are a pain in the butt to
edit, I am trying to set it up so the master table is an .xls, and I want to
avoid continually having to import. This way, users can just alter an excell
spreadsheet to update the list on the web. My users would be scared of
access. :-D

Does that make sense?

 

[bazango]

Look at the error message you're getting. It's talking about
'\\myPath\myLinkedTable.xls', not myfile.mdb.

Or are you saying that myfile.mdb is what's linked to
'\\myPath\myLinkedTable.xls'? (In other words, you've got something.mdb
linked to myfile.mdb linked to '\\myPath\myLinkedTable.xls') In that case,
then I believe that technically you DO have two separate connections to the
spreadsheet (even if myfile.mdb isn't explicitly opened)

"myfile.mdb" is my database, and it contains a linked table named
"myLinkedTable.xls"

Does that clarify anything?

 

[John W. Vinson]

From a webpage. Just trying to serve up a list of citations on the interweb,
from an Access mdb. But since tables in access are a pain in the butt to
edit, I am trying to set it up so the master table is an .xls, and I want to
avoid continually having to import. This way, users can just alter an excell
spreadsheet to update the list on the web. My users would be scared of
access. :-D

<shrug>

Maybe it's just my Access bias, but I cannot see how Excel is any easier to
edit than a properly-designed Access continuous form. The form could be made
to look just like an Excel spreadsheet if that will make the users more
comfortable.

I think Douglas nailed it: you have the spreadsheet opened exclusively, by
virtue of the link from the .mdb file. You're trying to open it AGAIN, from
your VBScript code. Why not just cut Access out of the picture altogether, and
simply open Excel from VBscript, if you're not actually using Access?

John W. Vinson [MVP]

 

[bazango]

"myfile.mdb" is my database, and it contains a linked table named
"myLinkedTable.xls"

Does that clarify anything?

Oh... maybe need to clarify... not creating a webpage from within access.
Just writing in notepad.

 

[Douglas J. Steele]

Why not just cut Access out of the picture altogether, and
simply open Excel from VBscript, if you're not actually using Access?

My thoughts exactly.