Assigning Permissions to Temporary Database Tables

[Guest]

We are running an Access 2003 MDE (split Front-end/back-end) on Citrix. When
the application is launched the FE MDE is copied in the users profile area
and then opened. During the opening process a temporary database is created
containing a temporary reporting table. However I am getting an error message
as I am unable to give the user the appropriate rights they need to use this
table.

From other postings I can see that the problem is that the user does not
have the rights to give themselves rights. However, I don’t know how I can
get around this problem in these circumstances.

My code is below:

Public Sub CreateTempTables()
'*** Creates a Temporary Database and Temporary Table and Links to it ***
'*** The Name of the Temporary MDB Created is the Same as the Current Front
End (FE) Name with "Temp" Added to the End of the Name ***

Dim tdfNew As TableDef, rstRecordset As Recordset
Dim wrkDefault As Workspace
Dim dbsTemp As Database, strTempDatabase As String
Dim strTableName As String

'*** Get default Workspace ***
Set wrkDefault = DBEngine.Workspaces(0)
strTempDatabase = Left$(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "
Temp.mdb"

'*** Make Sure there isn't Already a File with the Name of the New Database
***
If Dir(strTempDatabase) <> "" Then Kill strTempDatabase

'*** Create a new Temp Database ***
Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase, dbLangGeneral)
strTableName = "tmpAuditTrail"

'*** Delete the Link to the Temp Table if it Exists ***
If TableExists(strTableName) Then
CurrentDb.TableDefs.Delete strTableName
End If

'*** Create the Temp Table ***
Set tdfNew = dbsTemp.CreateTableDef(strTableName)
With tdfNew
.Fields.Append .CreateField("cUser", dbText)
.Fields.Append .CreateField("dDate", dbDate)
.Fields.Append .CreateField("Change", dbText)
.Fields.Append .CreateField("cRecordChanged", dbText)
.Fields.Append .CreateField("cUserName", dbText)
.Fields.Append .CreateField("OldValue", dbText)
.Fields.Append .CreateField("NewValue", dbText)
dbsTemp.TableDefs.Append tdfNew
End With
dbsTemp.TableDefs.Refresh

'*** Link to the Tables in the Temp MDB ***
Dim tdfLinked As TableDef
Set tdfLinked = CurrentDb.CreateTableDef(strTableName)
tdfLinked.Connect = ";DATABASE=" & strTempDatabase
tdfLinked.SourceTableName = strTableName
CurrentDb.TableDefs.Append tdfLinked
CurrentDb.TableDefs.Refresh
RefreshDatabaseWindow

'*** Assign Permissions ***
Dim cat1 As ADOX.Catalog, usr1 As ADOX.User
Dim strCurrentDB As String, strSecurityDB As String
Set cat1 = New ADOX.Catalog
Set usr1 = New ADOX.User
usr1.Name = CurrentUser
strCurrentDB = Application.CurrentProject.FullName
strSecurityDB = Application.SysCmd(acSysCmdGetWorkgroupFile)
cat1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& strCurrentDB & ";Jet OLEDB:System Database=" & strSecurityDB & ";User
Id=system;Password=xxx"
cat1.Users(usr1.Name).SetPermissions strTableName, adPermObjTable,
adAccessGrant, adRightFull
Set usr1 = Nothing
Set cat1 = Nothing
End Sub

Public Function TableExists(strTableName As String) As Integer
'*** Check for Existance of Table Specified ***
Dim dbsDatabase As Database, tdfTable As TableDef

On Error Resume Next
Set dbsDatabase = DBEngine(0)(0)
dbsDatabase.TableDefs.Refresh
Set tdfTable = dbsDatabase(strTableName)

'*** If Error Occurs the Tabledef Object Could not be Accessed and Therefore
***
'*** doesn't exist or user doesn't have access Due to Security Rights. ***
If Err = 0 Then
TableExists = True
Else
TableExists = False
End If
End Function

 

[Keith]

We are running an Access 2003 MDE (split Front-end/back-end) on Citrix.
When
the application is launched the FE MDE is copied in the users profile area
and then opened. During the opening process a temporary database is
created
containing a temporary reporting table. However I am getting an error
message
as I am unable to give the user the appropriate rights they need to use
this
table.

From other postings I can see that the problem is that the user does not
have the rights to give themselves rights. However, I don't know how I can
get around this problem in these circumstances.

My code is below:

<snip>

Hi Pete,

I'm just wondering why you're creating temporary databases and tables at
all. Why not have stored tables which the user can populate and empty using
RWOP queries? Further, if the tables are for reports, could you not have
bespoke queries for them to run on the live data?

Regards,
Keith.
www.keithwilby.com

 

[TC]

Nothing to do with your problem, but, all those CurrentDb usages are
really truly inefficient! You're much better off (and more correct) to
evaluate that function once, and store the result in a variable for
subsequent use:

dim db as database
set db = currentdb()
....
db.this, db.that, db.whatever
....
set db = nothing

HTH,
TC

 

[Guest]

Hi TC

Point taken... I had actually 'borrowed' the code from Tony Toews web site
;-) and had yet to go through it and fine tune it - I was concentrating on
trying to get it to work first!
Thanks

 

[Rick Brandt]

I'm just wondering why you're creating temporary databases and tables
at all. Why not have stored tables which the user can populate and
empty using RWOP queries? Further, if the tables are for reports,
could you not have bespoke queries for them to run on the live data?

Using a temp file that is destoyed and recreated significantly reduces the
bloat that would occur if the temp data was stored in the main application
file.

 

[Rick Brandt]

We are running an Access 2003 MDE (split Front-end/back-end) on
Citrix. When the application is launched the FE MDE is copied in the
users profile area and then opened. During the opening process a
temporary database is created containing a temporary reporting table.
However I am getting an error message as I am unable to give the user
the appropriate rights they need to use this table.

From other postings I can see that the problem is that the user does
not have the rights to give themselves rights. However, I don't know
how I can get around this problem in these circumstances.

You shouldn't even have to give them Jet/Security permissions. Since they
are creating the objects they are the owner and automatically have rights to
them. Have you tried the code without all of the permission stuff in it?

 

[TC]

No probs, hope you fix your other problem.

TC

 

[Guest]

Hi Keith

I inherited this setup - originally they used to run a shared front-end on
Citrix, and each user had their own temporary tables.

Now that I have changed the setup so that each user has their own copy of
the Front End MDE there is no problem with the temporary table residing
within it and your suggestion of using adding RWOP to my Make Table queries
does the trick - I had not come across this syntax before - thanks. Database
bloat is not a problem as each time they launch the application, the Master
MDE is copied into their user area and overwrites the exiting copy there.

 

[Keith]

Hi Keith

I inherited this setup - originally they used to run a shared front-end on
Citrix, and each user had their own temporary tables.

Now that I have changed the setup so that each user has their own copy of
the Front End MDE there is no problem with the temporary table residing
within it and your suggestion of using adding RWOP to my Make Table
queries
does the trick - I had not come across this syntax before - thanks.
Database
bloat is not a problem as each time they launch the application, the
Master
MDE is copied into their user area and overwrites the exiting copy there.
--

It sounds as if you have the exact same method of supplying users with their
own front end as I do (and know and love). Glad I could help.

Regards,
Keith.