Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 

News      User Comments     History     Notify Me

6/30/2012 11:44:45 AM
Access Split Database Seminar
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
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 data
2. Build two customer forms; the sensitive form requiring a password to open
3. Split your database into 3 files: front end and two back-end files for tables
4. Create shared folders on your server or Windows PC
5. Use Windows-level file & folder security to prevent access to the sensitive data
6. Move the back-end files to the server and relink with the Linked Table Manager
7. Encrypt your front-end database and create an ACCDE file

You can learn more about this seminar here: Microsoft Access Split Database Security Seminar

Permanent Link
Course Link: Access Split Database Seminar
Keywords: access split database security seminar
Page Tag: whatsnew
Post Reply

Access Split Database Seminar Comment from Joya @ 2/10/2018
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 file
3. Batch file copies new ACCDB file from server to local workstation
4. 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.]
Show Just This Thread        Post Reply
Topic Interest Comment from Ravi Chandiran @ 11/12/2016
Richard,

I am interested on this topic.

Thank You,

Ravi Chandiran


Reply from Alex Hedley:

Which topic?
Show Just This Thread        Post Reply
Linked PDFs Comment from Fran @ 3/5/2016
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 server
Are you using the full server name and not a mapped letter?
Show Just This Thread        Post Reply
Linked PDFs Comment from Mary Franklin @ 2/18/2016
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


Reply from Alex Hedley:

Are they stored in the db or outside?
Show Just This Thread        Post Reply
Shared Folders on the Cloud Comment from Carolyn @ 6/8/2015
Can you set up the shared folders on the cloud?  For example, Box or Dropbox?


Reply from Alex Hedley:

Yes there is usually a right click option on the folder or a share button link.
Show Just This Thread        Post Reply
Synology server as SQL server Comment from Gergely Papp @ 11/17/2014
Thanks a lot! So what would you combine Access with in this situation?
Show Just This Thread        Post Reply
Synology server as SQL server Comment from Gergely Papp @ 10/31/2014
Hi Richard!

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.

Show Just This Thread        Post Reply
When it comes to setting up windows share properti Comment from David Koehler @ 6/21/2014
When it comes to setting up windows share properties, arent you also interest in turning off offline caching


Reply from Richard Rost:

That's a good idea, yes. I don't believe "offline caching" is the default setting though - but I could be wrong.
Show Just This Thread        Post Reply
excellent excellent presentation Comment from David Koehler @ 6/21/2014
excellent, excellent presentation


Reply from Richard Rost:

Thanks. :)
Show Just This Thread        Post Reply
input mask Comment from Robert Baxter @ 4/23/2014
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.
Show Just This Thread        Post Reply
Lesson3 CustomerForms of AccessSplitDatabase Comment from lauro c @ 2/15/2014
There was no error generated when the Command Button is clicked
I 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 password
what did I do wrong?

Option Compare Database
Private Sub Command31_Click()
    X = InputBox("Password:")
    If X <> "apple" Then Exit Sub
    DoCmd.OpenForm "CustomerSecureF", , , "CustomerID=" & CustomerID
End Sub

Reply from Richard Rost:

Your code looks fine to me. Perhaps add a:

Dim X as String

in there and see if it helps. Are you getting an error?
Show Just This Thread        Post Reply
Comment from John Miller @ 2/10/2014
By using the technics in this seminar does this change the 2GB limit of Access or do need to add the 2 or more back ends together?
Show Just This Thread        Post Reply
Lesson3 CustomerForms of AccessSplitDatabase Comment from Lauro Cecilio @ 2/1/2014
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 password
what did I do wrong?

Option Compare Database
Private Sub Command31_Click()
    X = InputBox("Password:")
    If X <> "apple" Then Exit Sub
    DoCmd.OpenForm "CustomerSecureF", , , "CustomerID=" & CustomerID
End Sub


Reply from Richard Rost:

Your code looks fine to me. Perhaps add a:

Dim X as String

in there and see if it helps. Are you getting an error?
Show Just This Thread        Post Reply
Comment from James Gray @ 1/15/2014
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.
Show Just This Thread        Post Reply
Hiding Queries Comment from Amar @ 6/4/2013
Hi Richard,

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.
Show Just This Thread        Post Reply
front end back end shares Comment from Brauna Rosen @ 5/17/2013
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.
Show Just This Thread        Post Reply
Distributing Changes Comment from Deon Riley @ 3/26/2013
Hi Richard,

Yea bring it on. I'll definately go for this as a mini seminar. You know me by now - anything Access.
Show Just This Thread        Post Reply
Distributing Changes Comment from Ricardo Tolliver @ 3/25/2013
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 file
3. Batch file copies new ACCDB file from server to local workstation
4. 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.
Show Just This Thread        Post Reply
Missing Backslash on Keyboard Comment from Mubeezi Micah @ 7/8/2012
Dear Richard,

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.

Are there some additional settings that i may have to adjust?

MICAH


Reply from Richard Rost:

Try setting Windows to English-US.
Show Just This Thread        Post Reply
Protect Secure Information Comment from  Jerry @ 7/7/2012
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.

Objective SUCCEEDED. :)
Show Just This Thread        Post Reply
Missing Backslash on Keyboard Comment from MUBEEZI M @ 7/3/2012
Thank you Richard! I really enjoyed it!

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.

Show Just This Thread        Post Reply
Comment from Richard @ 6/30/2012
Now that you're finished with this seminar, make sure to check out my ACCESS SECURITY SEMINAR and ACCESS DATA ENCRYPTION SEMINAR for even MORE ways to protect your Access databases.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
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

 

 

You may want to read these articles from the 599CD News:

8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled
8/15/2018Access Dev 9 is ONLINE
7/31/2018Microsoft Access Developer 8
 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP