Access / Excel / DAO / Data on Terminal Server

[petehilljnr]

Gidday,

From within Excel, I was asked if I could automate a process to extract data from an Access database that resides on a terminal server on our domain.

I can navigate to the server within our network through explorer ok.
I found the database I wanted to get into and tried to open it and it opened fine except it said it was read only and I couldn't make changes.

No problems so far because I just want to extract data, not change it.

So, I jump into Excel and write the following code to test connecting to that database.

Sub test_link()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim dbPath As String

dbPath = "\\MyTerminalServer\Databases\DataTables\MyDatabase.mdb"

Set dbs = OpenDatabase(dbPath)

Set rst = dbs.OpenRecordset("MyTable")

MsgBox rst.RecordCount

rst.Close
dbs.Close

End Sub

Unfortunately, it fails on the OpenDatabase(dbPath) line.
The error reads:

Run-time error '3051'
The Microsoft Jet database engine cannot open the file "\\MyTerminalServer\Databases\DataTables\MyDatabase.mdb".
It is already opened exclusively by another user or you need permission to view its data.

Well ... scratch the "opened exclusively by another user" part because there isn't anyone using it.

So, I changed the line:
OpenDatabase(dbPath)
to
OpenDatabase(dbPath,False,False) (don't open exclusively and open read only)

Still no go. There are no passwords on the database either.

Any thoughts on how I can get into this table from Excel? I'm not particularly fussed about using or not using DAO, it's just what I know.

Other points to note:
I am able to "Link" to this table from within Access on my computer but can't change the data (and am not wanting to).
I can copy files from the directory on that server, but not write files to that directory.
When I log in to the terminal server using Remote Desktop, I have full permissions, but this is obviously different than if I navigate there using Windows Explorer.

Regards,
Pete

 

[petehilljnr]

Unfortunately, there are no LDB files in that directory.
I tried having a Remote Desktop screen open, and running that code in another screen and no LDB file appears either.

Actually, I created a database, linked to that remote database table, opened the table, then looked at the directory in my RD window and no LDB file appeared then either? Possibly because it is read only?

Does DAO not handle the permissions correctly or am I missing something in the code like a setting or something?

 

[petehilljnr]

Now here's the fun part (and my work around)

In a database on my machine, I created a linked table to that database on the terminal server.

Then, using the code in the original post, I pointed it to the database on my computer and the linked table within it and it works fine.

I kinda baffled as to why I need to take the extra step.

Pete.

 

[petehilljnr]

To those that replied, I thank you.

For everyones information, it was a problem with the share permissions on the server that was botching it all up.

If you come up with the same problem, take a look there also.

Pete.

 

[The_Doc_Man]

The share permissions are almost certainly part of the problem. But we have seen many problems with terminal services setup and a copy of Access on another machine. You MUST be able to create and delete files in the directory where your Access database resides - but this doesn't apply to the case where a table is linked from another machine or folder. Because the place where the files get created or deleted is the folder where the primary MDB file is located. The locks are managed locally in the case you describe, and you certainly have control over the local folders, so that is why it works locally and fails the other way.

Just a guess - but at least a slightly educated one.