Fair questions.
Access connected to an SQL back-end via ODBC methods can create tables. Or you can look up something called Data Definition Language (DDL), a variant part of SQL that defines the characteristics of tables, fields, and their attributes. In which case, entering SQL-like DDL commands to the SQL server or MySQL server will also be possible.
I am glad that you are persistent. You will need it.
It did not came to my mind that this forum cannot assist me on this simple problem of mine especially with our MVP's around.
Being an MVP isn't a guarantee that we can help you if you want to do something not within the easy parts of Access. You have admitted more than once that you are not strong in VBA. In order to tackle this problem, you will need to be. Security isn't about encryption. It isn't about making something obscure. The truth is, a determined hacker will eventually break into anything s/he wants to.
The only question is, can you make it tough enough to persuade the hacker to try something else? This is the "Theory of Low-Hanging Fruit." A hacker with no specific agenda will look for web sites or computer systems that have not implemented normal security. If you have implemented reasonable security and other sites around you have done less, hackers will pass you by. Unless... they have a grudge against your company or think your company has information that is actually worth a lot of effort.
You can do best for yourself in terms of security by implementing all security guidelines in accordance with your site's security policies. This includes patching everything that can be patched, painful though that can be to get "caught up" with an ever-mounting load of security patches. According to several security surveys in 2005, the most common reason that people got hacked or virally infected or Trojan Horsed or whatever is that they failed to patch known faults that had been published among the Microsoft patches for at least SIX MONTHS. A simple Windows Update would have saved them tons of recovery effort.
Browse some web sites, not for access, but for Windows security, to get some ideas about how to secure your system that hosts your database. Notice carefully that I did not say to secure your database. Secure your host system.
Between ULS and doing special code in VBA in each form's class module, that is where you have the best chance of hiding things that need to be hidden. Adding an extra layer of encryption beyond that or outside of the database does NOTHING. Listen carefully. It DOES NOTHING. Because your greatest risk isn't that someone will brute-force attack your system and get into your disk. It is that someone will steal an authorized password and get in, at which time the encryption is meaningless. Systems are designed to let in folks who appear to present proper credentials. That's why I commented about "smart cards" and other biometric methods in an earlier post.
Even if you use Windows directory-level encryption, once someone has the password you are a goner unless you use physical-token or biometric authentication.
I don't know if I'm making that fact clear or you just don't want to believe me. I have no solution to either part of that problem.
A very large part of your problem IS NOT PROGRAMMABLE. It is fixed by education of users and careful review of your site's security practices. It is fixed by yearly "booster" lectures or Power Point reminders of why you secure your terminal when you walk away from it. It is fixed by establishing an environment of security awareness. Static safeguards are ONE SMALL PART of a security solution. It takes literally hundreds or often THOUSANDS of person-days of work to do reasonable security assurance. (I sense that English is not your first language, so... 1 person-day is one person working solidly for one eight-hour day.)
Marianne, I'll say it again. If you want to improve your security you need to do certain things. Study VBA. Look at ways to prevent people from seeing your database window. Product MDE files for distribution. Put code in your class modules that causes the database engine to QUIT if they detect a flaw in whatever they see. Make it a point to work with your site security group to make the system secure as well as the particular database. More detailed data than that DOES NOT EXIST because we don't know your specific problem, and probably never will.
You mourn that we cannot help you. But first, we cannot do your job for you unless you are going to pay us for it, and many of us have employment contracts that limit our ability to do outside work. Second, this is a volunteer site. The magnitude of your problem as you have tried to describe it is so much that it asks too much of volunteers. If you had specific, narrowly-tailored questions, we can help. If you had very broad-brush questions about a concept, we can help. But you sound very much as though you think we should pull magic items out of a hat, and trust me, my magic hat went dry years ago. Yet you should not take that as a statement of non-caring. Just non-ability.
