UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Split Database Questions, Access 2016    
 
   
meyert
post Oct 14 2016, 01:34 PM
Post#1



Posts: 99
Joined: 22-January 08



I have a database that is used by a bunch of people. I recently split the database to allow users to personalize their forms/views

I have never split a database, so I have a couple of questions

If I make a change to the database (form change, macro change, whatever) I will need to delete the old back end data base then split the database that has the changes - correct?

Then I just send everyone a front end copy again - correct

Can I password protect the back end file?

When I send the updated front end file copies how do you ensure that everyone is using the most current version? Simple I know - just delete the old one. But I have found nothing is ever that simple - - people get confused just putting the front end on their desktop.

Do you rename the database so the old front end will no longer link to anything? (i.e. original called OpenOrders_be so then the revised could be OpenOrders2_be)

What do you guys do?

I appreciate your thoughts

Thanks
Go to the top of the page
 
theDBguy
post Oct 14 2016, 01:55 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,202
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Here's an overview of how I would "split" a database for a multi-user environment:

1. Create the BE file and create all the tables and relationships in it. You can password protect the BE (data) file, if you like.
2. Place the BE in a central location on the network. Give all users read, write, modify, and delete permissions to the BE folder.
3. Create the FE file and link all the tables from the BE
4. Create all the forms, queries, and reports in the FE
5. Give each user his/her own copy of the FE

To update the FE, you can do the following:

1. Update the master copy of the FE
2. Give each user a copy of the new FE

To ensure all users are using the latest copy of the FE:

1. You can implement one of the available "Auto-FE Updaters" to help you automate the process of distributing all FE changes to the users

Hope it helps...
Go to the top of the page
 
meyert
post Oct 14 2016, 07:52 PM
Post#3



Posts: 99
Joined: 22-January 08



Thanks. I guess I have some homework to do to learn about "Auto-FE Updaters"
Go to the top of the page
 
theDBguy
post Oct 14 2016, 10:30 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,202
Joined: 19-June 07
From: SunnySandyEggo


Hi,

One of the simplest approach to ensuring users are always using the latest copy of the FE is to have to use a batch file to copy the FE from a network location down to their local computer. In other words, the user will actually launch the batch file rather than the FE. The batch file will automatically launch the FE as soon as it is done downloading a fresh copy for the user.

Hope it helps...
Go to the top of the page
 
dmhzx
post Oct 15 2016, 06:53 AM
Post#5



Posts: 7,033
Joined: 22-December 10
From: England


Based on your original post you may like to consider a 'side' end
That is another Access file that holds all the individual configuration paths etc.
If you can get everyone to put that in the same path (C:\REF for example). then you can re-issued FE in the knowledge that they will not need to customisation doing all over again.
I'd also suggest not worrying about auto updating the FE till you have it all actually working.
It's easy enough to 'force' people to take the latest FE: and it may be worthwhile doing that relatively simple step first.

I think the way I split a db is easier than the one suggested by the DB guy.
Copy the existing database twice.
Rename one to have "FE", and the other to have "BE " in the name.
Delete everything except tables from BE.
and all (and only) tables from FE.
Open FE and use the external data wizard to link to all the tables in BE.


Based on my experience, I wouldn't bother with password protecting the Backend, unless you genuinely believe that your particular users are likely to go exploring.
You can for example distract people simply by changing the extension of the backend and hiding it.
Putting a password on the VBA maybe worthwhile though (IMO)
Go to the top of the page
 
gemmathehusky
post Oct 15 2016, 09:34 AM
Post#6


UtterAccess VIP
Posts: 4,432
Joined: 5-June 07
From: UK


generally -

the backend resides on a client's server to enable all client's users to see it
a copy/test version of the same backend can reside on the developers PC, so that changes to the data during testing do not contaminate the "real" data.

development of the front end generally does not require changes to the back end - but if it does, then you do need to update the backends of both the development copy AND the real data, with the changes - which may be adding new tables/fields, changing field types, setting default values and so on.

you can change backends in code, or manually. It's somewhat harder to do, than to relink a front end



when the developer releases a new version of the front end you have to

a) relink the tables to link to the clients backend, rather than the developers backend
b) ensure all users obtain this new copy

these last two are achieved by the auto-linkers mentioned above.
Go to the top of the page
 
ScottGem
post Oct 15 2016, 10:32 AM
Post#7


UtterAccess VIP / UA Clown
Posts: 32,181
Joined: 21-January 04
From: LI, NY


First, Allowing users to customize forms, etc. presents a major problem. If you deploy a new front end then you overwrite any changes made by the users.

Unless you make changes to the table structure (which is all that goes in the back end) you don't have to redistribute the back end.

As noted, they are many options to automatically refresh the front ends.
Go to the top of the page
 
meyert
post Oct 15 2016, 01:47 PM
Post#8



Posts: 99
Joined: 22-January 08



Thanks for all of the information.... I have no doubt that I am just a baby in terms of this database setup. You are all far more advanced than I am

one question - is the split database function that Access provides not good enough? Sounds like some of you are splitting the database different than using that utility

I really did not think about the back end being redistributed - I was just thinking of a way to force the users to use the NEW front end
Go to the top of the page
 
ScottGem
post Oct 15 2016, 03:45 PM
Post#9


UtterAccess VIP / UA Clown
Posts: 32,181
Joined: 21-January 04
From: LI, NY


The Database splitter wizard is fine. I use it all the time.
Go to the top of the page
 
gemmathehusky
post Oct 15 2016, 04:04 PM
Post#10


UtterAccess VIP
Posts: 4,432
Joined: 5-June 07
From: UK


@ meyert


you generally do not have to re-distribute the backend. That's why you split the database, so the data remains intact somewhere on a server.
splitting the database is not complex either

the thing about relinking the front end to the backend, is that using it requires your users to have some expertise is managing connections.
If you provide this facility in code, all you need to do is give them a button to click.

Users will try to do it wrongly, without meaning to.

I have the latest version on a server for users to use when they need to update their version. They still try and open the network version.

I have spreadsheets that I want users to import, with the header row containing the columns. They resort the data, including the header row, and then complain that the spreadsheet won't import.

The easier you make it for users to manage and use the database, the less likely they are to trash valuable company data.
Go to the top of the page
 
JonSmith
post Oct 15 2016, 05:05 PM
Post#11



Posts: 3,158
Joined: 19-October 10



Agreed with Dave, its amazing to find out how people are able to mess up some processes.
Try working in healthcare where there are loads of people who are experts in their clinical roles but fools with computers which are integral to their job nowadays. Its amazing the trouble they can create!!

On the topic of splitting and distributing, there are loads of methods some have advantages over others and vice versa.

My preference is my FE is launched from a vbscript. This creates some local folders, copies the front end and opens it passing the path on the BE via a command line switch which then relinks the tables.
If you open the fe without the switch you are kicked out

It works really well to force updates and deter nosey users but could be argued that the Brute Force method of copying the fe each time is wasteful. I might modify it to read/write a value in the registry with the current version.
Go to the top of the page
 
tina t
post Oct 15 2016, 06:15 PM
Post#12



Posts: 5,195
Joined: 11-November 10
From: SoCal, USA


QUOTE
Based on my experience, I wouldn't bother with password protecting the Backend, unless you genuinely believe that your particular users are likely to go exploring.
You can for example distract people simply by changing the extension of the backend and hiding it.
Putting a password on the VBA maybe worthwhile though (IMO)

we all bring different experience to the table. based on my experience, i would recommend the opposite. a password-protected backend prevents users - or anyone else - from opening the backend db and editing table data directly. if you're distributing the frontend as an mde or accde file, there is no code in it, so a VBA password really serves no purpose.

a frontend db that is not "locked" at all offers no protection to the table data, which is easily accessed from the table links - regardless of whether the frontend is mdb/accdb or mde/accde. if you "lock" your frontend db by disabling the AllowBypass option, the linked tables, queries, and macros are safe from changes by casual users, but not from anyone who makes a serious, informed attempt to bypass your precautions. usually, safe-from-casual-users is enough; if it's not, you should consider moving the database into a safer environment than Access.

i wouldn't recommend distributing a mdb/accdb frontend db to users. leaving the user interface completely exposed to changes by any user also leaves your data completely unprotected - might as well give the users access to edit data directly in tables and call it a day.

just more food for thought, meyert. :)

hth
tina
Go to the top of the page
 
dmhzx
post Oct 15 2016, 07:04 PM
Post#13



Posts: 7,033
Joined: 22-December 10
From: England


I can't help thinking that I'm glad I'm not the OP on this one.
********

Here I am struggling to split my first database, and I'm being told about accde, about vbscript, and about protecting my database from people who know more about access than I do, and moving it all away from access just in case.
*********


Clearly I have different experiences from the rest of you, because I've found that most of my users are responsible adult who just want a system that does what it's supposed to. Rather than being spotty oiks whose idea of fun is to break the system
They have no interest in poking around in the data. They get uncomfortable just seeing a from in design view, and if they did see the actual data tables, probably wouldn't be able to make head or tail of them because they've never learnt, nor want to learn about relational databases.
They have no idea how to bypass anything, or of any of the access 'special keys'.

I've never needed to put in any security other than hiding the Navigation pane. or occasionally using runtime. But then to me the data is not mine it's the users'

Surely we need to get the thing split and working before worrying about things that may never be needed

There are some very easy ways to 'force' users to get a new front end, that don't need vbscript or batch files, just a field in a couple of tables, and a bit of VBA, that could be used to do what needs doing.
Go to the top of the page
 
ScottGem
post Oct 15 2016, 07:22 PM
Post#14


UtterAccess VIP / UA Clown
Posts: 32,181
Joined: 21-January 04
From: LI, NY


QUOTE
Clearly I have different experiences from the rest of you, because I've found that most of my users are responsible adult who just want a system that does what it's supposed to. Rather than being spotty oiks whose idea of fun is to break the system
They have no interest in poking around in the data. They get uncomfortable just seeing a from in design view, and if they did see the actual data tables, probably wouldn't be able to make head or tail of them because they've never learnt, nor want to learn about relational databases.
They have no idea how to bypass anything, or of any of the access 'special keys'.


I'm with you. I have the same experience. Even more my users know not to fool with my apps because they risk their jobs.

I'm a little more conscious about security. I do present a controlled user interface mostly because there are tasks and data that I want to control access to.
Go to the top of the page
 
JonSmith
post Oct 16 2016, 12:21 AM
Post#15



Posts: 3,158
Joined: 19-October 10



I also concur.
I say this too at work about people locking things like VBA code in Excel. I say we should expect our staff to be responsible and not poke around and change things.

I have done some poking around myself in an Access based solution that was bought in my old job at my hospital used to manage discharges. The cost approached 6 figures and I was learning MSAccess so thought I could pick up tricks. I concluded the coding which wasnt compiled, was rather sloppy and bad but what was worse is they had a faux user login which if I bypassed I could view all the data as the connection string and password was saved in the file.
Whats worse is that the faux user login stored all its passwords in a regular table I could see!! Alot of people use the same one so I could hack their accounts if I wanted. All this could be done even without a windows user login using the guest account.
I reported it as a serious security breach and it got fixed. So point is security is important in certain context.


I would argue that my simple vbscript is simpler than any of the other FE updaters I have seen with perhaps the exception of the command line switch table relinking you need nothing in the VBA and not extra fields.
Go to the top of the page
 
dmhzx
post Oct 16 2016, 03:46 AM
Post#16



Posts: 7,033
Joined: 22-December 10
From: England


Hi Jon:
We all have our preferences and things we think are easier than others.
I just feel that for someone who knows a little VBA, and how to set up tables who is just finding out about database splitting it is 'easier' to add a bit of code and a couple of fields than learning about vb script and command line switches.

I sort of feel it's a bit like someone asking how to stop his car from being stolen.
An easy solution is to lock it and take the key away with you. - That's perfectly adequate for most of the time.
You can move up to a steering wheel lock
Fit a wheel clamp every time you leave the car.
But the only real way is to dump the car altogether and take a taxi every where.

On a car I had a while ago, I had a hidden switch in the power line to the starting relay that was simple and very effective. Provided you didn't know the switch was the technique, and where it was, w
Go to the top of the page
 
meyert
post Oct 16 2016, 08:52 AM
Post#17



Posts: 99
Joined: 22-January 08



QUOTE
There are some very easy ways to 'force' users to get a new front end, that don't need vbscript or batch files, just a field in a couple of tables, and a bit of VBA, that could be used to do what needs doing.


dmhzx - can you expand on this thought?

I have learned that every user is different, some are nosey and cause problems and some just have no idea what they are doing and cause problems.... I can solve any problems they cause. I just want to find the best way for "my situation" to handle the new front end files
Go to the top of the page
 
ScottGem
post Oct 16 2016, 09:41 AM
Post#18


UtterAccess VIP / UA Clown
Posts: 32,181
Joined: 21-January 04
From: LI, NY


One of the advantages of local front ends is that, if you have a nosy user who screws something up, its confined to their front end and you can catch them. But nosy users should not be tolerated. They need to understand that this is corporate data and not a place for poking around.
Go to the top of the page
 
dmhzx
post Oct 16 2016, 12:59 PM
Post#19



Posts: 7,033
Joined: 22-December 10
From: England


One way I've used is
have a table with one record for certain 'system' values. in both the FE and the BE. I tend to call mine FEParms, and BEParms
Imagine you have a field called'version' in both..
When you have a new version of the FE ready update the version in both your new FE and the existing BE.
In your FE start up process check the the version numbers are the same in both FE and BE.
If they're not, issue a message (your system is out if date, please update it)
and quit access.

That's far from being unbreakable, but was perfectly adequate for my 95 user system. And I found it very simple to implement.
But it does depend on how childish your users are.
Mine accepted that if I said they needed a new FE, then they did.
And it was a question of replacing their current FE with the new one, that was always in the same place.
And they all knew how to copy a file from one place to another.

If you want to tighten it a bit it's easy enough to hide the Nav pane, and disable the F11 key, and password protect the VBA.
It all depends how far you actually want to go. (or NEED to go)
Go to the top of the page
 
JonSmith
post Oct 16 2016, 05:56 PM
Post#20



Posts: 3,158
Joined: 19-October 10



Phew wow, if thats simpler than my simple vbscript then our definitions of simple differ dmhzx.

My code was design to just brute force update every time and open the db.
I can't see how you can get simpler than that...
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 11:25 PM