Permissions with linked tables

microsoft.public.access.security

20 Nov 2006 10:54 AM

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

24 Nov 2006 9:23 AM

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

Show quoteHide quote

"Martin Walke" <martin.walke_no_spam@vega_dot_co_dot_uk> wrote in message
news:O6kciKJDHHA.3660@TK2MSFTNGP02.phx.gbl...
> 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
>
>
>
>

24 Nov 2006 3:17 PM

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

Show quoteHide quote

"Geoff" <geoff@nospam.com> wrote in message
news:%23Y9N1o6DHHA.3228@TK2MSFTNGP03.phx.gbl...
> 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" <martin.walke_no_spam@vega_dot_co_dot_uk> wrote in message
> news:O6kciKJDHHA.3660@TK2MSFTNGP02.phx.gbl...
>> 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
>>
>>
>>
>>
>
>