I'm developing a FE/BE database and I want to link to a different BE
depending on whether the person logging in is a registered user or is a "demo
user". The registered user will link to the "real" BE database and the "demo
user" will link to a database that contains dummy data and read-only
privileges. I have all the code for logging in the users, but can't get my
table relinking code to work. Here's the code (modified from the MS Access
Security FAQ) I'm using to connect to the distribution database. I have an
identical functions (ConnectLinkDEMO) for the "demo" database (with a
different filename of course).

'************* Begin Code ***************
Function ConnectLinkDIST()
On Error GoTo ERR_ConnectLink

' This function can be run by any user who has OpenRun permission
' on the source database. It works equally well to link tables
' from scratch or to relink previously attached tables. In-line
' error handling is used to ignore any errors

Dim db As Database
Dim tdf As TableDef
Dim tdfLoop As TableDef
Dim strTable As String

Set db = CurrentDb()

With db
For Each tdfLoop In .TableDefs
strTable = tdfLoop.Name
If Left$(strTable, 3) = "tbl" Then ' Don't mess with system
tables
' Delete the link if it already exists
db.TableDefs.Delete strTable
' Create new link
Set tdf = db.CreateTableDef(strTable)
' Set the properties of the new link
' and append to the tabledefs collection
tdf.SourceTableName = strTable
tdf.Connect = ";DATABASE=balloons_secured_DATA_DIST.mdb"
db.TableDefs.Append tdf
End If
Next tdfLoop
End With

Exit_ConnectLink:
Exit Function

End Function
'************* End Code *****************

When I run either of these functions, I'm still linked to the distribution
table. It doesn't matter if I do it as the db owner or a "demo user" nor
does it matter if I do it from the immediate window or within my application.

What am I missing here??? Any and all help will be greatly appreciated!

Regards, Chris

Well, I see one problem already. I have changed this...

On Error GoTo ERR_ConnectLink

.... to this:

On Error Resume Next

It still doesn't work for either the "demo user" or myself (as db owner and
admin).

Btw, the "demo user" has Open/Run and Open/Exclusive permissions on the BE
demo database, and ReadDesign/ReadData permissions on all of the tables in
the database.

Regards, Chris

"Chris O''Neill" wrote:

> I'm developing a FE/BE database and I want to link to a different BE
> depending on whether the person logging in is a registered user or is a "demo
> user". The registered user will link to the "real" BE database and the "demo
> user" will link to a database that contains dummy data and read-only
> privileges. I have all the code for logging in the users, but can't get my
> table relinking code to work. Here's the code (modified from the MS Access
> Security FAQ) I'm using to connect to the distribution database. I have an
> identical functions (ConnectLinkDEMO) for the "demo" database (with a
> different filename of course).
>
> '************* Begin Code ***************
> Function ConnectLinkDIST()
> On Error GoTo ERR_ConnectLink
>
> ' This function can be run by any user who has OpenRun permission
> ' on the source database. It works equally well to link tables
> ' from scratch or to relink previously attached tables. In-line
> ' error handling is used to ignore any errors
>
> Dim db As Database
> Dim tdf As TableDef
> Dim tdfLoop As TableDef
> Dim strTable As String
>
> Set db = CurrentDb()
>
> With db
> For Each tdfLoop In .TableDefs
> strTable = tdfLoop.Name
> If Left$(strTable, 3) = "tbl" Then ' Don't mess with system
> tables
> ' Delete the link if it already exists
> db.TableDefs.Delete strTable
> ' Create new link
> Set tdf = db.CreateTableDef(strTable)
> ' Set the properties of the new link
> ' and append to the tabledefs collection
> tdf.SourceTableName = strTable
> tdf.Connect = ";DATABASE=balloons_secured_DATA_DIST.mdb"
> db.TableDefs.Append tdf
> End If
> Next tdfLoop
> End With
>
> Exit_ConnectLink:
> Exit Function
>
> End Function
> '************* End Code *****************
>
> When I run either of these functions, I'm still linked to the distribution
> table. It doesn't matter if I do it as the db owner or a "demo user" nor
> does it matter if I do it from the immediate window or within my application.
>
> What am I missing here??? Any and all help will be greatly appreciated!
>
> Regards, Chris
>

Chris O''Neill <(E-Mail Removed)> wrote:

> If Left$(strTable, 3) = "tbl" Then ' Don't mess with system
>tables

Here I'd check to see if the .Connect string is not null.

> Set tdf = db.CreateTableDef(strTable)
> ' Set the properties of the new link
> ' and append to the tabledefs collection
> tdf.SourceTableName = strTable
> tdf.Connect = ";DATABASE=balloons_secured_DATA_DIST.mdb"
> db.TableDefs.Append tdf

Nowhere do I see where ERR_ConnectLink is defined so the code isn't
successfully compiling or you've snipped some code.

I suspect this is erroring out and you're not seeing the messages.

If you visit http://www.mvps.org/access/tables/tbl0009.htm you will
see somewhat different code which does a .RefreshLink. and doesn't
do the .Append.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Chris O''Neill <(E-Mail Removed)> wrote:

> On Error Resume Next

I'd never do that. Well, unless I had some inline error code
checking which I don't often do. You need to see the error messages.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Chris O''Neill wrote:

>I'm developing a FE/BE database and I want to link to a different BE
>depending on whether the person logging in is a registered user or is a "demo
>user". The registered user will link to the "real" BE database and the "demo
>user" will link to a database that contains dummy data and read-only
>privileges. I have all the code for logging in the users, but can't get my
>table relinking code to work. Here's the code (modified from the MS Access
>Security FAQ) I'm using to connect to the distribution database. I have an
>identical functions (ConnectLinkDEMO) for the "demo" database (with a
>different filename of course).
>
>'************* Begin Code ***************
>Function ConnectLinkDIST()
>On Error GoTo ERR_ConnectLink
>
>' This function can be run by any user who has OpenRun permission
>' on the source database. It works equally well to link tables
>' from scratch or to relink previously attached tables. In-line
>' error handling is used to ignore any errors
>
> Dim db As Database
> Dim tdf As TableDef
> Dim tdfLoop As TableDef
> Dim strTable As String
>
> Set db = CurrentDb()
>
> With db
> For Each tdfLoop In .TableDefs
> strTable = tdfLoop.Name
> If Left$(strTable, 3) = "tbl" Then ' Don't mess with system
>tables
> ' Delete the link if it already exists
> db.TableDefs.Delete strTable
> ' Create new link
> Set tdf = db.CreateTableDef(strTable)
> ' Set the properties of the new link
> ' and append to the tabledefs collection
> tdf.SourceTableName = strTable
> tdf.Connect = ";DATABASE=balloons_secured_DATA_DIST.mdb"
> db.TableDefs.Append tdf
> End If
> Next tdfLoop
> End With
>
>Exit_ConnectLink:
> Exit Function
>
>End Function
>'************* End Code *****************
>
>When I run either of these functions, I'm still linked to the distribution
>table. It doesn't matter if I do it as the db owner or a "demo user" nor
>does it matter if I do it from the immediate window or within my application.


Are you sure you have the correct path to the demo db?

I think you need to use:
.TableDefs.Refresh
after the Next statement.

--
Marsh
MVP [MS Access]

"Tony Toews [MVP]" wrote:

> Chris O''Neill <(E-Mail Removed)> wrote:
>
> > On Error Resume Next
>
> I'd never do that. Well, unless I had some inline error code
> checking which I don't often do. You need to see the error messages.

Thanks for responding, Tony!

Yes, not being able to see if there's an error has been a problem, so I
added an error handler to it. The function runs without triggering the error
handler when I'm logged in as me (the db owner), but I'm getting 3011 and
3033 errors when I try and do it logged in as the demo user. More on that in
another reply.

Regards, Chris

"smartin" wrote:

> Hi Chris,
>
> Are you sure this is actually running--not just bailing out at the first
> error? I suggest you set a breakpoint on the first line of code and step
> your way through it.

I added an error handler so I can see if it's barfing. It runs without
error (but doesn't relink) when I'm logged in as myself (db owner), but I'm
getting 3011 and 3033 errors when logged in as the demo user. More on that
in another reply in a moment.

> Might strTable contain spaces?

None of my table names contain spaces. I learned *that* lesson the hard way
awhile back. ;-)

"Tony Toews [MVP]" wrote:

> > If Left$(strTable, 3) = "tbl" Then ' Don't mess with system
> >tables
>
> Here I'd check to see if the .Connect string is not null.

Good idea! Thanks!

> Nowhere do I see where ERR_ConnectLink is defined so the code isn't
> successfully compiling or you've snipped some code.

I messed up when I posted my earlier message. There is an ERR_ConnectLink
error handler. I just didn't copy it and past it into the message.

> I suspect this is erroring out and you're not seeing the messages.

It runs without triggering an error when I'm logged in as myself (db owner
with full privilages) but I'm getting 3011 and 3033 errors when I try it
logged in as the demo user.

> If you visit http://www.mvps.org/access/tables/tbl0009.htm you will
> see somewhat different code which does a .RefreshLink. and doesn't
> do the .Append.

That looks like a better way of doing it, alright, but when I run that
logged in as demo user I'm still getting 3033 permission errors. (SIGH!)
DemoUser has Read/Run privilages on the demo database and ReadDesign/ReadData
privilages on the tables. Isn't this sufficient privilages to link to the
demo database using
I'm this is happening because the "DemoUser" account has read only the
RefreshLink method?

Thanks, again, for all the assistance provided.

Regards, Chris

"Marshall Barton" wrote:

> Are you sure you have the correct path to the demo db?

Yes, the path is correct.

> I think you need to use:
> .TableDefs.Refresh
> after the Next statement.

I tried that both after the Next statement and also right after the Append
statement. Neither generated errors, but neither worked either.

Any more suggestions would be greatly appreciated.

Regards, Chris

Oh, my! I *really* messed up that reply! Let me try again....

"Tony Toews [MVP]" wrote:

> If you visit http://www.mvps.org/access/tables/tbl0009.htm you will
> see somewhat different code which does a .RefreshLink. and doesn't
> do the .Append.

This function looks like a better solution than the one I found in the MS
Access Security FAQ. I ran it while logged-in as myself (the db owner with
full rights) and it worked flawlessly. However, when I logged-in as DemoUser
with limited rights, I get 3033 permission errors.

Just to rephrase what I'm doing...

The demo database contains dummy data. DemoUser has Open/Run privilages on
the demo database and ReadDesign/ReadData privilages on all of the tables in
the database. (In case it matters, these privilages aren't assigned directly
to the DemoUser account but, rather, are assigned to a DemoUser group that
DemoUser belongs to.)

In the destination database (balloons_PRG.mdb), DemoUser has OPenRun
privilages on the database and full privilages on all of the tables. Again,
these are assigned through a DemoUser group that DemoUser belongs to.
Because Access uses the "most restrictive" privilages on linked tables,
DemoUser's privilages to the demo database's tables is downgraded to
ReadDesign/ReadData only because that's what the demo MDB file assigns.

What I need is a way to connect to the "real" database or "demo" database
based on whether the logged-on user is DemoUser. The code in the Form_Open
event of my startup form goes like this:

If CurrentUser = "DemoUser" then
' Code to link to the demo database goes here
Else
' Code to link to the real database goes here
End If

Thanks for any help or guidance you can provide.

Regards, Chris