How can I lock the database to only allow users to use forms 1

[GriffG]

I have a database that runs as a program. it has a log in screen and multiple forms. I am wanting to be able to lock access to the tables, querys and forms from being edited.

all i want the users to be able to do is open the database and have the forms. I also want this with a password so I can them make changes if I need to.

 

[PPJOSEPH]

Hi Griffg:

Here is what you need to do.

Step I:
Create a backup copy just in case things do not work out.

Step II:
Click on Module and copy the following code right under OPTION COMPARE DATABASE

Function SetStartupProperties() As Integer
Const DB_Boolean As Long = 1
ChangeProperty "StartupShowDBWindow", DB_Boolean, False
ChangeProperty "StartupShowStatusBar", DB_Boolean, False
ChangeProperty "AllowBuiltinToolbars", DB_Boolean, False
ChangeProperty "AllowFullMenus", DB_Boolean, False
ChangeProperty "AllowToolbarChanges", DB_Boolean, False
ChangeProperty "AllowShortcutMenus", DB_Boolean, False
ChangeProperty "AllowBreakIntoCode", DB_Boolean, False
ChangeProperty "AllowSpecialKeys", DB_Boolean, False
ChangeProperty "AllowBypassKey", DB_Boolean, False
End Function

Function ChangeProperty(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
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.
ChangeProperty = False
End If
End Function


Save the module as ModStartUp

Step III:
From Tools > Database Utilities > Make MDE File
(You can use the same file name)
This will restrict users opeining the forms in design mode. Therefore, the users can not see and change your codes.


Step IV:
Close all the files

Step V:
Open the newly created file YourNewDatabase.mde (note the extension)

Step VI:
Open a new Macro
Type in RunCode in the first line under Action

and in the FUNCTION NAME type in
SetStartUpProperties ()

Save the Marco as AutoExec

Step VII:
From Tools > StartUp
Uncheck Display Database Window (so the database window will not be visible to the user)

Step VIII
From Tools > Options > General
Check Compact on Close (so the database compacts and reduce its size before closing).

Step IX:
Open and close the database a couple of times for the codes to work. Once the code starts working all you will see is the switchboard.

Note:
Step III thru Step IX should be done only on the newly created .mde file (Rule of thumb - always create a back up mdb file before creating a mde file because even you are restricted from viewing and changing the codes)

Step II is done so that the user does not open the db by using shift+enter to view the database window.

GOOD LUCK AND HOPE THIS HELPS.

 

[GriffG]

works fine till step three. the convertion process tells me there there is an error


"This error is usually associated with compiling a large database into an MDE file.  Due to the method used to compile the database, a considerable number of TableID references are created for each table.  The Microsoft Jet database engine version 4.0 can only create a maximum of 2048 open TableIDs at one time.  Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Jet database engine uses during the process of compiling a database as an MDE.  However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.
More information about this error message online."

 

[PPJOSEPH]

Griffg:
My recommendation would be is to copy all the objects to a new db, this would definetly reduce the size of the db before you go thru all the steps.
Paul

 

[PHV]

Usually we compile all the VBA code before the conversion.
When in VBE (Alt+F11) menu Debug -> Compile ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PPJOSEPH]

From your question, I seems that you have the tables, queries, forms and reports in the same database. If so, you need to split the database into two. One should have only the tables (TABLE 1) and the rest of the objects should be in a separate table (TABLE 2).
Link the tables in TABLE 1 from TABLE 2
HOW TO:
File > Get External Data > Link tables.

If you are loading the dbs on the server, you should be linking the tables using network neighborhood, so that you have the correct path. Double check the path from Tools > Database Utilities > Linked Table Manager.

This will drastically reduce the size of the db.

Again, Table 1 should have only the tables and the users should not have any access to it.
The setupproperties code should be in Table 2, just making sure.

ALWAYS MAKE A BACK-UP COPY.

Debug your codes as recommended by PHV.

 

[GriffG]

Yes I have two databases and I have links between them. one table has only got Tables in it and all the forms are in the other database. The project was half created by another guy who know nothing about good project design and modular was to build things.

Also because the project has been through two other peoples hands (who arn't programmers) there are lots of things wrong. There are some tables in the main DB that has the forms.

A major problem is that there are all these references throughout heaps of code that refer to the Tables Database as a .MDB file type.

Is there any way to lock the database down (even if only crudely) so that it can be put out into a couple of small places for beta testing.

this will not be a major concern in the future as the official release will be in .NET for the program using access or MSSQL as a data only database