To split or not? (1 Viewer)

PWORTHY · Aug 4, 2014

To split or not? / how to make improvements during use

I have finished my database and am wanting a way to make improvements to the database while not interferring with another user using the database. Due to the content of the database, there will only be one user at a time, so there is no need for multiple users. The database will be located on a network drive. With that being said, would it even be necessary to split the database? Is there another way to make changes that can be incorporated into the database without completely covering up the existing and wiping all existing data? Also, again considering the content of the database, the user will not be able to generate their own reports or queries, because of content integrety and I understand the split allows for that.

Any suggestions? solutions? ideas?

thanks!

ButtonMoon · Aug 4, 2014

Re: To split or not? / how to make improvements during use

My opinion: The idea of putting data and application into a single file is just barmy. For the reasons you have stated, among many others, it is illogical, pointless and highly inconvenient for everyone. Hope that answers the question

Steve R. · Aug 5, 2014

To reiterate the points so far, splitting is a must. Also, do not use MS Access as a networked back-end. I would encourage the use a free open source database as your back-end (MYSQL, Maria, Firebird, or HSQLDB. Another suggestion SQL Server Express.

Note: Concerning the databases mentioned above, I do not have sufficient knowledge for making any recommendations.

PWORTHY · Aug 5, 2014

AccessBlaster said:
Even if I am the only user, I still split and put the BE on SQL server just for maintenance reasons not to mention security. Also I can log in at remote locations.

AccessBlaster - We can still login from remote locations to our network drives, so having remote access is not an issue. I understand the security of the BE, but it only puts the tables on the BE. The queries I have in place are just as much in need of security as the tables. Someone messing with the queries will destroy the database's integrity. Is there a way to split the queries too? Or is there a way to build a FE that just links to the entire database?

ButtonMoon · Aug 5, 2014

PWORTHY said:
The queries I have in place are just as much in need of security as the tables. Someone messing with the queries will destroy the database's integrity. Is there a way to split the queries too? Or is there a way to build a FE that just links to the entire database?

If your data is an Access database (meaning Jet/Ace) then you cannot secure it from users who have read or write access to that database file. So if security is any part of your requirement then you need to move your database to another platform.

Assuming you do that, I think your concerns about securing queries ought to go away. A query doesn't alter data, so messing with the queries won't alter the database's integrity. Unauthorised access to the queries might have revealed things that you didn't want people to see but that won't happen if you have secured the database so that only authorised users can run the queries against it.

ButtonMoon · Aug 5, 2014

AccessBlaster said:
So if i am a authorized user and I write a action query that won't alter data? What am i missing here?

If you don't want users to delete some piece of data then you have to deny them permission to delete that data. You clearly can't stop users from writing SQL but that doesn't mean you should give them the opportunity to do harmful things with it.

PWORTHY · Aug 5, 2014

Steve R. said:
To reiterate the points so far, splitting is a must. Also, do not use MS Access as a networked back-end. I would encourage the use a free open source database as your back-end (MYSQL, Maria, Firebird, or HSQLDB. Another suggestion SQL Server Express.)

Note: Concerning the databases mentioned above, I do not have sufficient knowledge for making any recommendations.

Steve, what I have is a quantity comparison reporting tool that is based on a widget's ID number and compares 3 separate requirements (estimate qty, design qty, and issued for construction qtys); takes the greatest requirement from all three and then compares to what has been requisitioned for purchase. It is used to determine if we are on schedule, short, or over with our ordering to meet constructions timeline. It takes multiple reports exported from our material management system and compiles, compares and generates various tables, forms, and reports. The users uploads the reports via macros, reviews the data for accuracy, and then generates a pre-determined report. The user exports and uploads at this point, until we can configure the direct links to the system. Which is the need for being able to make improvements to the database without interrupting the user. Since the data is not live, we don't want just anyone going in and pulling a report without having the uploads updated, thus the reason for single user. Hope that helps

If not, let me know.

gemma-the-husky · Aug 7, 2014

Splitting the dbs makes virtually no difference operationally.

It makes a lot of difference to ease of administration, as you can change the function of the database independently of the data, which is what you are trying to do.

Data security is a different issue, and not really germane to this point.

Split the database.

max1 · Oct 10, 2014

At what stage should a database be split? The database I am writing will continue to develop whilst being used. Will splitting the database impose any limitations on what I can change?

anyone care to post a list of pros and cons?

ButtonMoon · Oct 10, 2014

max1 said:
At what stage should a database be split?

If you already have a database that contains both data and application then split it as soon as you are able. When you are starting a new project keep the database and application separate right from the start and then you will never have a need to split it.

The database I am writing will continue to develop whilst being used. Will splitting the database impose any limitations on what I can change?

No. It's easier to modify, test and deploy things if you keep the application and data separate.

spikepl · Oct 10, 2014

When operating on recordsets, you cannot use in any simple manner the Seek method on a linked table, so cannot simply take advantage of indexing. Othar than this I m not aware of any other consequences of splitting.

I have one operational application deliberately kept unsplit for now 2 years, to simplify configuration management of the many changes and extensions, together with the live data files processed. But this one is now heading for a major rewrite and a split too.

Rabbie · Oct 10, 2014

I would agree with all those who recommend splitting the database. It makes changing the front end so much easier without the risk of inadvertently corrupting the data

max1 · Oct 12, 2014

What happens when I want to add a new table? does it automatically end up in the back end?

If I find that I need to change the design of an existing table what hoops will I have to jump through?

fat controller · Oct 12, 2014

Split. Definitely split.

I am little more than a novice at this stuff, but I can hand on heart tell you from my own experience that splitting is definitely the way to go, especially so if you have more than one user - the database will run quicker, and your data is far better protected when it is split.

Adding a new table is easy - simply open your back end and create a table, then open your front end and link the front end to the new table. Just remember that you will need to ensure that all your users has an up to date copy of the front end, else they won't be linked to your new table.

Did I say you should split the database?

max1 · Oct 13, 2014

So a piece of CAD software (Vulcan) links utilises an ODBC link to the database. From what I can make out the CAD software runs a Query stored in the db then filters the results of that query.

I suppose this will continue to work so long as the ODBC link looks at the front end.

At some point in the future i want to write a script that sits within CAD software and contains the relevant SQL. Presumably I could point this at the back end?

rbh1090 · Aug 5, 2015

When an application requires to be deployed in a multi-user environment, it is very convenient to split the database into two groups. The first contains the client application: database with forms, queries, reports, macros, and modules. The second contains only one or more database tables. The client application is distributed among all the workstations and connects to one or more databases stored on the server.

It is important to clarify that the term server is not very suitable for "Jet Engine and Access Database Engine" databases (mdb, accdb, etc) and it only refers to a shared folder on the network because no data processing task is executed on the server, but on the client.

By implementing the client – server design:

All users share the same information.
Maintenance tasks may be carried out in the application objects (forms, reports, etc,) without placing the database offline.

Perhaps the easiest method to implement this design consists of attaching all tables of the database server in the client application. For security and performance reasons, this method is not recommended for real server databases like a SQL.

To split or not? (1 Viewer)

PWORTHY

New member

ButtonMoon

Registered User.

Steve R.

Retired

PWORTHY

New member

ButtonMoon

Registered User.

ButtonMoon

Registered User.

PWORTHY

New member

gemma-the-husky

Super Moderator

max1

Registered User.

ButtonMoon

Registered User.

spikepl

Eledittingent Beliped

Rabbie

Super Moderator

max1

Registered User.

fat controller

Slightly round the bend..

max1

Registered User.

rbh1090

Registered User.

Similar threads

Users who are viewing this thread