Distributing a databases

[benny7]

I've created a database, along with forms, reports, and querys. I want to distribute these, how do I do it so they can only view the database via the forms?

Basically I just want them to be able to run the databases and see the switchboard, nothing else. They can then select the forms, reports or queries via the switchboard. I don't them to see the names of the forms, queries or tables and not to be able to view design view.

Thanks!!!!!

 

[willir]

There are several ways of doing this. A typical approach is to...
- Backup the database
- Create a backend database with only the data (tables)
- Create a frontend database with the forms, reports, coding, etc.
- Use the Link manager to link the front end database to the backend database. Now the forms and reports will access the data.
- Backup the frontend database.
- Convert the frontend database to an MDE file. User will now not be able to see the coding, etc.

Hints:
- Use the database splitter (Tools -> Database Utilities -> Database splitter). The wizard is a good start. One type of table we do distribute with the frontend is the static table, tables where the data does not change. Example, Names of States and Provences.
- The "Make MDE file" option is located from Tools -> Database Utilities. You have to first convert the database to the native version. For example, if you are running the Access XP application but the database is running as Access 2000, you have to convert the database to Access XP / 2002 before creating the MDE file. (Yep, the database convertion feature is also found under the "Database Utilities".
- Whenever you upgrade your applications, the changes have to be made on the MDB file since the forms, etc are not accessible on the MDE file. The MDB frontend database is considered the "development" database.
- Using linked files to the backend database is used just about everywhere, but there are two things to "cope" with -- a) You have to relink the frontend database to the backend database when you move things around. For example, you take your database home to work on developlement -- you will have to relink the database at home, and again when you take the database back to work; b) With the MDE, if you can not provide a consistent environment where drive mappings differs, you may want to develop a method for relinking the frontend.

Richard


Richard

 

[PPJOSEPH]

Hi Benny7:
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.

 

[penndro]

Hi PPJoseph,

I am an intermediate access user/developer and I have also created an access database that I would like to copy to CD's and distribute commercially. Would the procedure you list above be sufficient for this type of distibution?


Penndro

 

[PPJOSEPH]

Yes, Penndro, Well did you say commercially!! I guess check is in the mail. Just kidding. It will work.

 

[willir]

Penndro

The "gotcha" with using a CD is that Access needs to update system tables. When you open the database on a CD, it will be in Read-Only mode, and generate a whole bunch of errors. Likewise, if you copy the database from the CD to the hard drive, it will still be in Read-Only mode until you change the file attributes.

 

[PPJOSEPH]

Good point willir!!

But changing attributes should be the easiest thing to do. Right click on the file after its saved to your hard drive, click on properties and uncheck Read only which is under the general tab.