Database protection question?

[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

 

[FancyPrairie]

Yes you can password protect your code and give users whatever access you want them to have. Access has a security wizard to help you. In addition, the are many articles in tek-tips on how to go about protecting your code.

However, if your problem is that you want the user's to be able to create their own reports with out messing with your tables, forms, etc., I would have them create their own database and link to your tables. Then they can do whatever they want and not effect your database.

Another option is what I use. Sometimes my user's don't know what reports they will need in the future. My concern is, when they do decide they needed a report, I may be too busy to get to it when they need it. So, I created a Report Criteria form from which the user can select the criteria they want. They then press a button to save the criteria as a query (which I build on the fly (basically append the WHERE clause I build from the criteria selected to a SQL Select statement.))

The user can then go into Microsoft Word, design their own report and do a Mail-Merge against the query created above.

 

[Tofias1]

FancyPrairie,

Thanks for the response. After doing more research I found out how to protect the db using that option. On the other hand you brought up a great point about having the user create their own reports. Where would I find info on implementing code like that? I have a lot of VBA code in my database so I would say I am an Intermediate programer. If you could point me in that direction it would be more than greatly appreciated.

Tofias1

 

[FancyPrairie]

Step 1:
Create a form that contains the information the user may want to query on. For example,

1. You might have 2 text boxes that represent a date range (i.e. represents patients admitted between these dates).

2. You might have one or more multi-select list boxes (i.e. User selects which departments he/she wants included in their reports)

3. Whatever else you think the user may want

Step 2:
After the user selects their criteria, they will either press a button to print the report or save the criteria as a query. Either way, you will then build a SQL statement based on the items selected. For example, suppose the user enters a date range in the 2 text boxes (txtBeginDate, txtEndDate). Your code would build the Where clause for the SQL select statement assigned to the report. Your Where clause might look something like this:

Dim strWhere as string
Dim strSQL as string

strWhere = vbNullString

if (not isnull(txtBeginDate) and (not isnull(txtEndDate)) then
strWhere = "Where dteAdmitted Between #" & txtBeginDate & "# AND #" & txtEndDate & "#"
End if

If (reportselected = "this report&quot then
strSQL = "Select * From tblPatient " & strWhere & ";"
end if

Now that you have the SQL Select statement created, save it as a query (user could assign a name to the query if you like). There are several posts explaining how to build a Where clause using multi-select list boxes and saving them as queries. Here's one of them thread181-324343. (Lot of mistakes made in the thread I recommended, but it gets the job done).

Step 3
Open Microsoft Word, create a new document, and select Mail-Merge. From Mail-Merge, select the database that contains the query you just created. Now build your report.

Hint: When creating SQL select statements via code, I find it a lot easier to user the Query builder to build it for me, then copy and paste it in my code. This way I know the SQL statement works prior to inserting it into my code.

 

[Tofias1]

FancyPrairie,

Thanks again. I will test this out and let you know how it worked.

Tofias1