Help needed on Database

[gear]

While searching for Access help through Google, I found your forum which has greatly helped me in building my database in Access. I got all answers through your forum for many of my doubts and ‘How To’s. I thank all the members for their excellent and simple way of explaining for a novice like me.

As to the Database I built, it is almost complete and working fine. I have built a switchboard with menu choices. I have distributed this to all my colleagues and they are using fine.

I have three problems still to be addressed. I have tried to protect my database tables, forms etc through the security wizard, user permissions etc. But nothing is working. I have hidden all tables, forms etc. and unclicked the ‘Display database window’ in Startup so that only the switchboard is visible. I have also changed the switchboard properties to cover the entire screen and no maximize/minimize/close button. When a user double-clicks the desktop icon the database file opens with the switchboard menu. There is an Exit button in the switchboard which the user can click to he can click the close button of the Access window. Apart from this, the user can do nothing to corrupt the tables, forms etc., so I believed. However, one colleague clicked the ‘View – Design View’ in the Access menu bar and the Switchboard became ready for modification. Is there a method to block the View – Design View’ option in the Access menu bar?

My second problem is that we want a new database file first of January every year. Is there a method to create a new DB file with the same table, form and settings from within Switchboard? (The only option I found was to copy the DB file to another location, delete all records and rename it for the new year). Is there any other procedure?

My third problem is that I designed a Crosstab Query as given in the Help menu with criteria LIKE “*” & [Enter any character to search by: ] & “*” but when I execute the query it displays all records containing the character entered. This is not what I want. My search criteria everytime changes (it is text based). So I am unable to save Query form with a specific criteria as it will execute only defined parameters. Is there a way to make Query form where any user input (i.e. any word or number) is taken for search?

Shall be grateful for reply.

 

[The_Doc_Man]

Your first question describes an incorrectly secured database, I think. Search the forum for "Securing a Database" and "Workgroup Security." There is also the topic "Hiding the Database Window" which might help here.

I see two issues in your second question. First, is it critical to really have a new database? Or is it just that you want to save last year's data and start over empty? In other words, does it make a whole hill of beans which file is which when the shooting stops? Because you can also search the forum for the topic of "Archiving" - which would let you save the old data externally and then flush anything that was saved so it is no longer in the working DB.

Your third question regards syntax, I think. Look carefully for the extra quote marks here and read up on the syntax of quote usage inside a quoted string.

.... LIKE "'*" & Trim$([{your input here}] & "*'" AND ...

or

.... LIKE """*" & Trim$([{input}]) & "*""" AND ....

 

[Wiz47]

Your first question describes an incorrectly secured database, I think. Search the forum for "Securing a Database" and "Workgroup Security." There is also the topic "Hiding the Database Window" which might help here.

I think if he wants to do a "lite" version of secure for casual users (not programmers) - he can set the startup options to not allow:

Display of the database window
Full menus
Default shortcuts
Built in toolbars/Menu changes
Access special keys

Then he can keep an "unsecured" version for his use if he needs to change anything later.

 

[gear]

Thanks

Thank you Wiz47. This is exactly what I wanted. I was able to distribute the secured version.

Thanks to The Doc Man also for fast response. But Sir, the syntax you mentioned is also not working. I searched the Forum for Archiving but of no help. As you said, I just want to save the old data separately and flush out the DB file so that it is new every year. Could you pl help?

One more query. How do I prevent deletion of records? I have set up the Form properties in Allow Deletions to No. In spite of this, data when I delete a record, it really gets deleted. How can I protect the records from deletion?

 

[John_W]

My third problem is that I designed a Crosstab Query as given in the Help menu with criteria LIKE “*” & [Enter any character to search by: ] & “*” but when I execute the query it displays all records containing the character entered. This is not what I want. Shall be grateful for reply.

Have you actually entered your criteria above into the query grid literally, including the brackets and the text within? That's how it's supposed to work. It will then prompt the user to enter a criterion when the query runs.

 

[GaryPanic]

what about mde files - you cannot amend much in these ????

g

With regards to archiving - this is something I am looking at as well

My view is upon a given time frame either copy the d/base and empty - and start again or move data to an archive - given that this is a once a year routine - I am thinking of both make a copy of say 2005 data and keep in in its own d/base but also have a archive d/base
which will have 2001,2002,2003,2004 and 2005 data

now the problem with this is
any updates on the programme will need to be done on the archive whre as copying the database completly and starting anew - you don't have this problem

both have positives and negatives

doing both then I know even if it goes tits up I will still have the data in one form or another