Permissions with linked tables

[Martin Walke]

Hi all,

I have a back-end access database that is accessed from different machines
via ADO/JET4 from VB6. A link to another table in another backend database
gets created during the running of the program. This link can be to
different tables in different databases but the schema of the table is
always the same.

What I currently do is delete the link before adding it to the same (or a
different) table when required. The problem is that i get an error
80040e09(You do not have the necessary permissions to use the 'questions'
object. Have your system administrator or the person who created this object
establish the appropraite permissions for you).

I've tried deleting the link via DAO and get a similar error (3033). I've
googled looking for a solution and most solutions talk about using Access
itself to add the user to the work group.

Two problems there. One, I'm not using workgroups and two, this has to done
programmically as the program is used in different from a number of
different workstations in different locations in the UK.

One solution i've thought about is to not delete the link but update its
linked file reference instead. Is this possible?

The code i use to add the link is:

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
p:\s12247\studanswers.mdb"

tbl.Name = "questions"
Set tbl.ParentCatalog = cat

tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Datasource") = QFile
tbl.Properties("Jet OLEDB:Remote Table Name") = "Questions"

cat.Tables.Append tbl
Set cat = Nothing

and the code to remove the link is simply

Mydb.Mode = adModeReadWrite
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
p:\s12247\studanswers.mdb"

MyDB.Execute "drop table questions"


TIA
Martin

 

[Geoff]

Martin:

Try searching the Microsoft website for the exact error message you receive.

It appears you're not using user-level security so maybe a newsgroup dealing
with ADO/DAO would be better?

Here are a few thoughts (which probably won't be much help!):

1. Check that the current backend table isn't locked (ie that the current
link isn't being used).

2. Check that the network connection is OK.

3. Check that the new backend table exists in all backend files (ie is
named correctly).

Geoff

 

[Martin Walke]

Thanks for replying Geoff.

Did a search before the NG and all I got was muttering about Excel. Wasn't
sure which NG to post the enquiry to and didn't want to post to all and
sundry!

I've found a work around where the user that creates the link to the other
table, also deletes the link when he's finished with it. Means there's a bit
more thrashing of the database but it solves the problem.

Martin