Ok, gotcha.
1)
Firstly, by the sounds of your scenario, you mightn't want to start implementing access's built in security as this *can* be somewhat a lengthy process to understand, set up, implement and maintain. PLus you've already stated you dont want to use it, as you have a process in place already.
Which is a shame because implementing access's own security messaures you can literally give read/write permissions to tables and queries. If you do decide this would be the right route for you, it is well worth playing around with - there is loads of support on tek-tips for this particular topic as well.
2)
The second method I can suggest, is to 'lock down' the database by splitting it client/server (creating an MDE for the client side), hide the database window (so objects cannot be seen), and setting the bypasskey to null.. whilst this method is not un-crakable to the enthusiast, you have to know a moderate amount of coding to break in, and when you do.. would it really be worth it?
Once you've split the database, and locked it down, you can create a form that allows users to create and run custom queries, export them etc etc.. there is a form already designed for this purpose by the boys at mvps.com .. it comes either as acc97 or 2000 as can be downloaded from:
- Find Record 97 (
)
- Find Record 2000 (
)
Large amouts of Kudos to Dev Ashish and Terry Kreft for these.
I've a feeling this second method would be easier for you to implement, as it takes no time at all to create a 'secure' mde that has a custom form in it.
(With the MDE, use linked tables into your backend 'master' db...)
I have some code for locking down the by pass key if you would like it:
New Module: "SetByPass"
Code:
Option Compare Database
'Following functions are to set the bypass to
'either true or false. This allows the designer
'to say who gets to see the tables or not :)
'(asuming that the database window is hidden)
'disallow letting the shift key be pushed when opening
'the database to access the db window
Sub SetBypassPropertyFalse()
Const DB_Boolean As Long = 1
ChangePropertyFalse "AllowBypassKey", DB_Boolean, False
End Sub
Function ChangePropertyFalse(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
Dim dbs As Object, prp As Variant
Const conPropNotFoundError = 3270
Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True
Change_Bye:
Exit Function
Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, _
varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangePropertyFalse = False
Resume Change_Bye
End If
End Function
Sub SetBypassPropertyTrue()
Const DB_Boolean As Long = 1
ChangePropertyTrue "AllowBypassKey", DB_Boolean, True
End Sub
Function ChangePropertyTrue(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
Dim dbs As Object, prp As Variant
Const conPropNotFoundError = 3270
Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangePropertyTrue = False
Change_Bye:
Exit Function
Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, _
varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangePropertyTrue = True
Resume Change_Bye
End If
End Function
You can then make a call to one of two functions
Call SetBypassPropertyFalse ' secures the database.. shift key can't bypass the startup code, and cannot view the database window.
Call SetBypassPropertyTrue ' does the reverse.. allows users to use the shift key to bypass the start up.
(Once again, code adapted from an outside source.. can't remember where, so speak up if you want to take credit.)
Hope the above is of *some* help.. obviously would be happy to make suggestions on anything else you need... just ask
Si
------------------------
Hit any User to continue
)