As always I would like to thank you

[Tofias1]

As always I would like to thank you to everyone who takes a look at my question in advance.

I have designed a database and it is finished in the aspects of data entry through the use of forms. However, in the future I forsee the users wanting to generate more reports than what I already have. So here is my question. How can I make the database password protected for the design view only? Is that possible? I just want the people who have the password to be able to gain access to the code and tables etc...

Tofias1

 

[neillovell]

Try going to Security->User and Group Permissions and selecting the tables, forms etc on the right and using the check boxes to mark which parts (the design for example) certain users can access.

Does that help?

 

[Guest_imported]

Are you sure thats a good idea?? Why Not write an export routine so that the users can export the tables to a separate database and write their own querys in the safety of their own environments. Security is there for a reason and I think that a good principal would be to hand people duplicates of the data, but not allow them access to break the system in the process.

I know Im being critical, but prevention is better than cure.

If you need any ideas about how to do this, I'll find the post that I sent the code for doing so, and pass it on.

Regards,
Mr Big

 

[Tofias1]

Hi guys,

Thanks for the reply.

neillovell,

I am going to try that right now.

Mr. Big,

After reading my question again I guess I was not clear in I wanted to say. The only pepole who will have the password to make changes is myself and one other IT person. The dept. that the database is for, really do not have a clue on how it works all they know is how to use it. I just fear that they may delete something so I am mainly the one who will be making any changes what so ever. On the other hand, thanks for being critical and those post will be helpfull if you would still like to send them along. I wouldn't mind testing them out.

Tofias1

 

[aaahhhrrggghhhh]

Hello Tofias

Heres the code. Its basically a form with a progressbar, and a "GO/EXPORT" button. When the user clicks the "GO/EXPORT" button, the system opens a "open file" dialog box, filtering only .mdb files. The user selects a .mdb file. The system then goes through a table called "tblExport" (Layout below), and exports to the Access Database that the user selected, the tables in srcTable, but renames them in the new database to the entry given in destTable. The function is a global module and should be fairly self explanatory. I placed the code on another post in this forum, but am unable to find it! Therefore, if you have problems implementing a test version of the code, then let me know on nicholas_large@hotmail.com, but leave the heading (Further to question on Tek-Tips). Here goes :

ExpPath is the export path (the database to export the data to eg. "C:\My Documents\MyDatabaseExp.mdb"

ExternalCall is False if you dont want the system to prompt you with stuff (See Code)

Public Function ExportData(ByVal ExpPath As String, externalcall As Boolean) As Boolean
On Error GoTo usrexit

'exports the actual data from this database to another
'ExpPath = full path and filename of the database to export to
'external call. If False, then do try and change form controls (Progress bar), otherwise dont.
' used so that a call from a different database can still use this function to export the data

Dim chkdb As Database

Set chkdb = DBEngine.OpenDatabase(ExpPath, True, False)
'if user wishes to export, then do so
'A) go through all tables in the source (This) database
Dim totnorecs As Double
totnorecs = DBEngine.Workspaces(0).Databases(0).OpenRecordset("Select count(*) from TblExport&quot.Fields(0)
Dim myRS As Recordset
Set myRS = DBEngine.Workspaces(0).Databases(0).OpenRecordset("Select srcTbl, destTbl from TblExport&quot
myRS.MoveFirst
If externalcall = False Then
Forms!frmAdmin!ProgressBar1.Max = totnorecs
Forms!frmAdmin!ProgressBar1.Visible = True
Forms!frmAdmin!btnGoToMainMenu.Enabled = False
Forms!frmAdmin!Sttime.SetFocus
Forms!frmAdmin!Sttime = Now()
Forms!frmAdmin!btnAllDataExport.Enabled = False
End If

For tmpLp = 0 To totnorecs - 1

'then go through each table in the destination database ...
For Each tbdfdest In chkdb.TableDefs
' ... and see if it already exists there
If tbdfdest.Name = myRS.Fields(1) Then
'erase the destination table if it exists
chkdb.Execute ("drop table " & myRS.Fields(1))
'and close and reopen the database in order to refresh it.
chkdb.Close
Set chkdb = DBEngine.OpenDatabase(ExpPath, True, False)
Exit For
End If
Next tbdfdest
'okay, so export the data
mysql = " select * into " & Trim(myRS.Fields(1)) & " in " & Chr(34) & Trim(ExpPath) & Chr(34) & " from " & myRS.Fields(0)
DBEngine.Workspaces(0).Databases(0).Execute (mysql)
myRS.MoveNext
If externalcall = False Then Forms!frmAdmin!ProgressBar1.Value = tmpLp + 1
Next

'now close the destination database for the last time.
If externalcall = False Then
Forms!frmAdmin!btnGoToMainMenu.Enabled = True
Forms!frmAdmin!btnAllDataExport.Enabled = True
Forms!frmAdmin!Edtime = Now()
Forms!frmAdmin!ProgressBar1.Visible = False
End If
chkdb.Close

If externalcall = False Then MsgBox ("Exported&quot
ExportData = True
Exit Function

usrexit:
If Err.Number = 32755 Then
MsgBox ("Data Not Exported&quot
ElseIf Err.Number = 3356 Then
MsgBox ("Destination database is not ready, please ensure that noone is using it and export again.&quot
' Else
' MsgBox (Err.Number & " " & Err.Description)
End If

ExportData = False
Exit Function



End Function

The point to this is to prevent unauthorised changes to the database, but allow people access to its data without giving them access to the database window. It works really well and is well worth checking out.

Please let me know if you found my article interesting, anyway.

Regards,
Mr Big.

 

[aaahhhrrggghhhh]

Hi Tofias, Sorry, Heres the structure of the tblExport table :

srcTable Text(30)
destTable Text(30)

An example would be

srcTable destTable
-------- ---------
CustDets Customers
OrderItems Items
... ...

So when the system runs it finds the "CustDets" table and exports it as "Customers", similarly with "OrderItems" is exported as "Items" ... etc

Regards,
Mr Big.

 

[Tofias1]

Mr Big,

Thanks again for the code, however, I have no clue on how to go about implementing it. I have made the tbl and the required fields though.

Tofias1

 

[GDGarth]

Just use the User Level Security Wizard to secure the database (after following the instructions to create a new workgroup information file, etc.).

You can set permissions for users or groups to accomplish exactly what you're trying to do. There's no need to export data, etc. - especially since that will force the users to create their own queries, etc.

If done properly, Access security is pretty good, and quite flexible. It's more than good enough for what you describe.

 

[Tofias1]

GDGarth,

Thanks, thats the way that I have gone about that now.

Tofias1