In this seminar, you will learn how to set up a database using Microsoft Access with multiple back-end (table) files: one that every database user can access, and another for sensitive information, such as financial data, that only a small, select group of users can access. You will then learn how to use Windows file and folder sharing to secure the sensitive back-end database file so that only the users you select can read that data. This seminar will cover:

1. Set up multiple customer tables, one for normal data, one for sensitive data2. Build two customer forms; the sensitive form requiring a password to open3. Split your database into 3 files: front end and two back-end files for tables4. Create shared folders on your server or Windows PC5. Use Windows-level file & folder security to prevent access to the sensitive data6. Move the back-end files to the server and relink with the Linked Table Manager7. Encrypt your front-end database and create an ACCDE file

I am also interested in this.[See below]Currently I split a database and about 5 employees are using the one FE.accde file. We haven't had any issues, but let me know if you are aware of any. Did you ever make this seminar? That might work better for me. THANKS!

The mini seminar you mentioned here:[However, if you have the Front End distributed to lots of different workstations, then it's really up to you to distribute those changes. You can make it as simple as creating shortcuts to their database folders and they copying and pasting the new FE file yourself. Or you can email them a link and say "here, download this." I even went so far with one client to program an automated solution that would:

1. Check for a new version (number stored in a table)2. If it was new, quit the database and launch a BATch file3. Batch file copies new ACCDB file from server to local workstation4. Batch file then re-launches database

It's completely up to you.

Richard

P.S. If enough people want to see how I created that automated solution, let me know. If I get a handful of people interested, I'll make a mini seminar out of it.]

They are in a folder outside of database but on same computer as database folder. I wanted to put copy of front end on different computer's and the backend on network/server. Everything works except this linked pdf. I really don't know vba coding that's why the seminars are so great! If front and backend stay on network/server with the pdf folder everything works great! I guess I could make a copy of front end for each person on network/server?

Reply from Alex Hedley:

Do the machines have access to this folder? If you try and navigate to that folder from the machine is it accessible?The front end would be copied to the individuals machine and the back end stay on the serverAre you using the full server name and not a mapped letter?

Rick, I used your seminar on workorders to make my database. I've split the database now but I can't open my linked PDF's in my workorder file that was already there. But I can add a linked PDF.Can you help me!Mary

Thanks for the thorough seminar. I've checked several forums and in general people don't recommend copying the backend database to a shared folder on a NAS server. I've just learned that Synology can manage mySQL. Could it be an appropriate solution for a small company (with 5 client computers in various locations) to use the Synology server as SQL server.

Thanks For your help!

Gergely

Reply from Richard Rost:

I don't generally recommend an Access-only solution like this for high-traffic databases. However, I've successfully set up 20- and even 50-user databases with JUST split Access databases, so it depends on the thruput and network speed.

I've never used Synology, so I cannot comment on their product. However, I recommend you take a look at SQL SERVER EXPRESS. It's a free version of Microsoft's powerhouse SQL Server database, and it's a GREAT back-end solution. The Express edition is FREE and handles most small- and mid-sized business needs.

Great, practical tutorial! But I have one question. Pardon me if this has already been asked:

At 9:20 you purposely try to input the wrong password to check the code. But pw is visible. Is there a way to add an input mask, like "****" to prevent anyone looking over the users shoulder from reading the pw they just typed in? Thanks!

Reply from Richard Rost:

Not in an InputBox, but you can create your own form with a textbox control. Set the input mask on that to Password and it will provide the desired results.

There was no error generated when the Command Button is clickedI don't understand your instruction to add Dim X as a string - - - where do i actually add it?sorry, no idea re: code building

>>>>>>Lesson3 CustomerForms of AccessSplitDatabase: I followed the steps to the letter in the CodeBuilder (clock 8:51mark) - used apple too as my password; could not get the command button to prompt a passwordwhat did I do wrong?

Lesson3 CustomerForms of AccessSplitDatabase: I followed the steps to the letter in the CodeBuilder (clock 8:51mark) - used apple too as my password; could not get the command button to prompt a passwordwhat did I do wrong?

Rick,I had an intersting thought about splitting data from the front end. Is it possible to have your data files reside in "the cloud" i.e., on an online data backup location? That way each user would just have to have the front end on thier machines and transmit data to and from the backup system? Just thinking outside the box and trying to avoid having to buy and set up an LAN/server for my ambulance service.

Thank you for your split database seminar; it was very helpful. I plan on taking your security seminar as what I really want to know how to do is to lock down the navigation bar together - even keeping users from holding 'shift' and bypassing the startup.

I am using Access 2007 and created the ACCDE file version hoping that it would hide the design of all of the objects. While this was successful in hiding form designs, I noticed that the queries were still accessible to the users. I wanted to know what your thoughts are on designing the queries using either SQL or the QBE grid and then feeding the control source with the SQL code. This will keep the query from being created and will keep the user from accessing both the form and query design. Of course, this is a temporary solution that I would use until I take your security seminar.

Thank you for all of your excellent videos!

Reply from Richard Rost:

For advanced users (and databases that I create for myself and for clients) I use as FEW queries as possible. I prefer writing SQL in my forms and reports unless the query is complex.

An ACCDE will not hide the table data or structure, or query structure. It just locks down your forms, reports, macros, and modules.

Hi,I just need to confirm something with you: the back end resides on my computer but not on the shared file and the front end is on my computer's shared file and on the users shared file. Is that correct?

Reply from Richard Rost:

If you have two computers (PC1 and PC2) you can set up a SHARED FOLDER on PC1. In that folder, put your BACK-END database (tables). It doesn't matter where the FRONT-END files go. You can put them on the Desktops of PC1 and PC2 if you like. Each computer should have its own copy. Now, just link to the tables in your SHARED FOLDER, and you're all set.

Q- After I've split my database, if the fornt and backend are on different computers how do I deliver changes for forms, reports and tables especially if the (Front End) can be on several different computers?

Reply from Richard Rost:

Well, you CAN keep a working copy of the Front End database on the SERVER for people to use. If your database isn't that big (a few MB perhaps) and your network is fast, it's OK to just have multiple clients running that file directly.

However, if you have the Front End distributed to lots of different workstations, then it's really up to you to distribute those changes. You can make it as simple as creating shortcuts to their database folders and they copying and pasting the new FE file yourself. Or you can email them a link and say "here, download this." I even went so far with one client to program an automated solution that would:

1. Check for a new version (number stored in a table)2. If it was new, quit the database and launch a BATch file3. Batch file copies new ACCDB file from server to local workstation4. Batch file then re-launches database

It's completely up to you.

Richard

P.S. If enough people want to see how I created that automated solution, let me know. If I get a handful of people interested, I'll make a mini seminar out of it.

Actually, the backslash is together with the pipe symbol as you mention above. When i press this key, i get # and if press the key together with the SHIFT key, i get ~.On number 3 of my key board is the # key. When i press SHIFT+3, i get £.

I bought this computer from the Netherlands and i remember configuring Windows to English- United Kingdom.

At 6:57. You opened the CustomerSecureF showing all secure information. You did this without using a password. I thought the purpose of this course was to show how to protect secure information? Please don't tell me that I can take another course. This one failled in its' stated objective. What gives?

Reply from Richard Rost:

Jerry, if your users have access to the NAVIGATION PANE they can open up any forms they want. You can HIDE the Navigation Pane on database startup (which is in Access Options > Current Database) so they can't. The password only pops up if you use the button to open the form. You COULD put the inputbox code in the OnLoad event of the secure form instead of the button, and then it wouldn't matter how you opened the form.

REGARDLESS of whether they can open the FORM or not, if they don't have security rights to the SECURED BACK-END TABLE database (which you set up at the WINDOWS level), then they won't see any data in the form or will get an error message. *I* have access to that back-end table, so the form opened.

For some strange reason, i can't find the back slash on my keyboard! Usually, i have to open Excel, type =CHAR(92) in a cell, then copy the resultant back slash into the VBA editor. Is there something missing on my keyboard? Or is there a key combination that i have not yet learnt to utilise?

MICAH

Reply from Richard Rost:

Wow. I've never heard of that. Unless you have a foreign-language keyboard I don't know what the problem is. The backslash on ALL of my keyboards is on the same key as the PIPE symbol: |

Can you snap a picture of your keyboard and email it to me? amicron@gmail.com.

If you just want to
subscribe to get email updates when
the News
is updated, then
enter your name and email address and check the Notify Me box
below. If you would optionally like
to add your comments below to be posted, they are welcome.

Your Name:

Required

Your Email:

NOT Public

Subject:

Comments:

Sorry about this step. It's just to keep the spam bots away:

Verify:

What is 3+3:

Notify me when the News is updated.

Remember Me for my next comments

Please do not use this form for
Customer Service inquiries! If you have questions about your
account, shipping info, courses you've ordered, need passwords, etc.
please use the Customer Service Center instead.

I value your comments. They will be
displayed on this page (above). Your name will
be displayed, but your email address will not be. As
always, I promise to never give away your personal information to
anyone else, ever.

NOTE:
If you don't leave your name and email address, DON'T
expect a reply. I can't promise a personal reply to everyone who posts
here. I TRY my best, but I cannot guarantee it. If you don't
leave your real name and email address, I won't even bother. I usually
just hit DELETE. -Richard