Multiple Backends

[Interninneed]

Hello,

1. I need to know how to link multiple backends to one front end.
2. How to let a user select on a form which backend they want to put data in.

I have created a database for my department. It will be used for different projects. The tables and user interface will be the same for each project but the data will be project specific. Several projects will be worked on at the same time. I would like one backend for each project. What issues could I run into?


I know how to link the backends through the menu options, but I want a way to do it in a form for my users. A combo box would be preferred. This way they can open Access and choose which project ( backend) they want to work in.


Thank you for any help you can provide.

 

[The_Doc_Man]

Other than possible intended security issues, is there any immediate need - like expected size - to drive this desire for multiple back-ends?

In theory, having identical front-end forms (the User Interface) and tables (schema) that differ only in project ID (where project is not a part of the table's prime key) is a violation of normalization rules. Not to say it can't be done, but it generally should not be done that way.

Here is the REAL problem. By implication, if you allow someone the option to access any of several back-ends to keep things separate, how do you know that they got the "right" one? In order to do this, they will need file access privileges of MODIFY for ANY back-end they COULD touch, and there is nothing in the infrastructure to show them the difference (because you SAID these tables are all the same), so if they make a mistake in selecting their target, how will they even know? From an operational security viewpoint, this is an extremely risky design. To make it work and keep ALL of the non-table objects from instantly barfing all over you, even the table and field names referenced by the queries, forms, recordsets, reports, and any other infrastructure would have to be the same across the board. I see massive disaster in such a concept, not to mention confusion. Once you are connected, other than the .Connect strings that are involved, you won't know (or at least, won't be sure) WHERE you are operating.

Having offered a serious warning, here is a link that might show you the idea of how to do what you want, because there is no technical barrier to your request. The barriers are purely operational in nature.

Dynamically Relink tables on open DB - Microsoft: Access Modules (VBA Coding)

Hi, I am trying to dynamically link tables in my database based on the current file path. I have a front end with a separate backend DB. I would like to ensure when the front end is opened it relinks the tables based on the current file path. I am using the code below but getting an error:- "The...

www.tek-tips.com

To implement this with your drop-down, the only thing you need is a combo box and a table of valid projects that lists project name in one field and project file-spec in the other. So you just display the name and use VBA to pull the file spec from the 2nd column of the combo, which you then feed to the appropriate connect strings.

 

[GPGeorge]

Sometimes an idea that sounds good when it first comes to you doesn't stand up well to the harsh light of reality as the implications begin to manifest themselves. Here is a case in point. I agree 100% with The_Doc_Man; this is a highly risky approach. I'm intrigued, though, by the motivation for proposing it. You can't be concerned about data security because any user can select any project, it would seem. Nobody is restricted from seeing data in all of the projects. There must be something else that makes it worth considering.

 

[gemma-the-husky]

Take into account the above remarks and suggestions.

Add to the switchboard, or main form the details of the selected project, and make sure your users know what they are doing. Whatever system you have has the same problems. Clerks could administer the wrong target project.

You can re-point your database to alternative datasets relatively easily, and should be able to achieve what you want.

If you need to include user security you can restrict access to different projects to different users, although it adds some complexity to the design.

 

[The_Doc_Man]

Dave's comments represent even MORE valid concerns. One way to keep people from making the wrong choice is to change their access rights (file permissions). But then, unless you determine a login ID as a way to filter the projects to which they have access, you would be offering them files that in some cases could produce file access violations, which would make your job incredibly that much harder because of the need to intercept the file violations. You just opened up a major Sam's Club or COSTCO sized can of worms.

 

[Interninneed]

Couple of reasons why this way should work best for us.
1. Only 2-3 people will have access to the database. These people will have intimate knowledge of both the database and each project they are working on. Only they would be putting in data. The data base is being developed in stages and a future stage will require others to use it with restricted login access and no ability to input data. ( @The_Doc_Man how do I avoid that can of worms? )
2. All of the tables contain the same type of information, equipment, conduits, room, etc... The Frontend/UI has all of the input forms and reports/ Queries that would be needed for every project. That said, the actual data is unique to each project. If I open the wrong project backend I'll know pretty quickly that I'm not in the right project by looking at the "names". Even if I made whole separate Access files I don't know how entering the wrong one can be avoided.
*I could make a pop up that asks if they are sure they want to "enter" Project ABC*
3. @GPGeorge Projects go on for a long time and the timelines overlap. While I may be working on the beginning of project A my coworker may need to pull reports from Project B that was started last year. The method we are trying to use will allow one tool and prevent the current file folder nightmare that is currently being used.

I am open to any alternate suggestions but also realistically this might need to blow up in our faces inorder to motiviate my bosses to try another way.

 

[The_Doc_Man]

A part of any project is a capacity analysis. Look at the records you are contemplating. How much data would you wish to store for a single project? If you are looking at 10 Mb per completed project, an Access back end can handle 100 such projects before it reaches 1 GB.

IN THEORY, the ideal storage is to take ALL of your project data that has identical structure and infrastructure and add a "project" field to the tables. Then you store EVERYTHING in the tables. Each record contains the ID of the project to which it belongs. "Translation" fields might not need project qualifiers because you said the structures were symmetrical to each other. When you want to focus on one project, you have to create ONCE what we call a single-table query that has a WHERE clause to select a specific project number. The query gets that number from your combo box OR from a global structure such as a TempVars collection. (There are other options, that's the simplest global case.) Then the form merely uses the selective query that works for ALL of the projects but only requires a simple selection of the project ID to isolate the desired records. IF you restrict the front-end structures to work only with the forms that have the project-selection combo, this is about as seamless as it gets. You DO have to secure the database. (See below.)

As far as long-term storage, if you ever have a completed project, THAT could be something you archive. And if so, you can use the same method of selectivity to copy the data to an archiving back-end or an external file. You can do an INSERT INTO of a subset of a table where the source records are qualified by that project ID and no other records are selected. Then, once the archiving is complete, you could delete the records from the main table.

You asked about the can of worms brought into play by having users of unequal duties. The "restricted user" problem is based on the concept of "roles" that a user plays. At database login time, you would have a table of authorized users AND that table would indicate the role - or roles - played by each person. If you do some searches in the forum for "Securing a Database" and "User Roles" you will find ample reading and much discussion on these concepts. You CAN create your own login or, if you have a formal domain with user logins, there is a way to ask Windows who you are and "trust" the Windows Domain login. And trust ME when I tell you that a Domain login, if properly interrogated, is far more secure than any form you would ever devise. If it gets to that point, ask. Our member Isladogs (a.k.a. Colin) has published many articles on that particular subject.

IF this idea of "separate files" is coming from your bosses, they need to see the discussions here and understand that the issue is RISK/REWARD. There is a HUGE risk in dynamically repointing file pointers because an Access "crash" during one of these "repoint" operations will potentially corrupt whatever it is currently sharing a connection. The difficulty in recovering a corrupted DB can be anywhere from trivial to impossible, so any strategy that minimizes points of operational risk would be a good strategy.

Adding another BE file adds another step to the maintenance of the data files because of something called "bloat." Databases grow disproportionately to the amount of data you add if you also do updates or other types of maintenance like "data scrubbing" to "clean up" what you've got. The compact & repair (C&R) issues ideally involve manual operations on each separate BE file and if you have a lot of updating then you WILL have bloat. It is inescapable. Again, if it gets to that, we can discuss it more for you. Just ask.

Can this multi-BE layout be done technically? Sure, if you are determined enough and careful enough. But... what are the risk factors? The labor costs to minimize risks is where this problem will start to bite you... hard. AND, because this is essentially a symmetric multi-file solution with limited sources of verification (i.e. knowing absolutely that you are pointing where you should be pointing), you could do a LOT of damage if your point at the wrong place and start editing data.

By implication, the data files will be symmetrical, nearly indistiguishable, with respect to each other because if not, you have a LOT of queries, forms, reports, and modules to update for each project. And each time you add a new project, it gets worse because of the overhead involved. Keeping it all together in a single file with a single set of tables qualified by project ID means to add a new project you add ONE ENTRY to the project table and you are on your way. You will have to document anything you do including a full description of the projects for which files have been created. Adding a new project means you add a new project ID and file spec to the combo box AND you have to replicate the tables - and of course, you have to test that you got it right for the new table AND that you didn't break any of the old tables. Which over time becomes a problem in factorial math for the workload - particularly if these project EVER share data. No, I'm not kidding about the factorials. In fact, if it ever occurs that projects DO have to share data, they had better not touch more than 14 other projects because of the limits involved in having multiple BE files open at the same time.

Also, switching from one file to another in the same session will RAPIDLY consume system resources (in the form of file handles and dynamic scratchpad memory for the Windows Operating System.) You will be INVITING the Windows System Error "Out of resources" - which is one that you cannot apply a meaningful Access error trap to prevent. Windows won't even BOTHER to let you back your way out of that gracefully. It will be BANG ZOOM CRASH right then and there.

 

[CJ_London]

I maintain a database for a client. When a new project is set up the main project table contains a 'createdBy' field automatically populated by the winuser name (the name the user uses to log into windows).

Any user can see and explore any project, but only the user whose name matches the createdBy field can edit data. As a visual indicator, the form background has a very pale green colour when in 'edit' mode. Simplistically the code to set the edit property is set in the form open event

me.edit=dcount("*","tblProjects","ProjectPK=" & me.projectFK & " AND CreatedBy='" & winuser & "'")


Users do not have direct access to tables or queiries

 

[gemma-the-husky]

Just to be clear.

I would have data for multiple companies each stored in separate databases, and at startup, or at some other time, allow users to choose a company and then close the current company, and open a different company. If you are familiar with Sage, that's a similar process to the way Sage works.

I don't think I would have separate project databases within a company, as there's no real need. Having separate databases is spreadsheet thinking. You don't need different project databases, and different monthly or annual databases. You just need a single database, and your queries select the data you need from the active project. Users should never ever see the data tables - they just see the information the database presents to them.

But if you are really insistent of keeping these projects separate there's no reason why you can't. It's just data management.

If someone accidentally loads project 1 with data intended for project 2 - well that can happen however you try to control the data, and it's going to be a pain to correct it when you realise it's happened. If you want some users to be able to interact with project 1, but not with project 2, then you need a way to manage that. It's going to be more complicated to do this with multiple separate project databases, than with just one database, as you would need to set up and maintain distinct user management tables in each separate database.

 

[Cotswold]

I developed something similar to Internineed's requirement for structural engineers.
This type of company has many uniquely numbered contracts on the go at the same time. With new ones added all the time and completed contracts closing down. The amount of data to store drawings, material, phases, revisions, contract documentation, work in progress values, delivery notes and other information is considerable. Resulting in huge databases. For instance, each 200 tonne contract will contain a massive amount of data. Plus once a contract has been completed it is of little interest and rarely if ever referred to later. Although the summaries always remain in the Master file for management purposes. Merging all contracts into one database was not a practical option as the main database would have been massive after five years or so. This design is manageable and easily supported, whilst remaining quick and easy to use on the local network or remote desktop.

The system consisted of a Master file that contained the summaries including material weights and surface areas. Also drawing office and manufacturing hours expended for each contract. Each contract has an individual contract database. It also contained all of the standard data settings, steel section types and other materials that may be specified. Also, the drawing office and shop floor staff names to record hours spent from timesheets. Plus client names, site addresses and other standing data. There is in-built security to prevent unauthorised staff from accessing some contract details. Typically these are timesheet data, drawing office or shop floor hours etc. etc.. There is obviously a degree of confidential data in the system that a company would not want to be seen by anyone outside of the company. Records of the staff on data entry or changes is saved by date, time and user number. Deletes are prevented, with all deletes made simply being a hidden record. A view of all deleted items is available to designated management.

When a new contract was created, basic details were entered in the Master and a contact database was created by copying a blank standard contract database to a new one with the contact number as it's name. The user simply opened the Master file and selected the contract to work on from a dropdown list, then opened the contract database required. The contract number and the name it is referred to as is clearly shown across the Master file. When a user opened a contract database, that remained the default option after closing down. Whenever they opened the Master file, the last selected contract database would also be opened automatically. For an overview of one or many contracts, that was found in the Master. As drawings and material are entered into the contract database, totals were transferred or updated into the Master. At any point in time, the Master would show the current state over all contracts, complete with all work in progress details, as well as the summary details on individual contracts. Reports and screens are used in weekly contract management meetings.

It has a complete snapshot of the whole business and the completion percentage of all contracts. With some overhead exclusions it shows the current work in progress figure. Without this type of system a company doesn't know the actual costs, weights surface areas and hours on a contract until after it has been completed. In my system you could see things going wrong, deliveries running late and cost over-runs in real time. Far better to know if you're losing money whilst it is happening than a month or two later.

So one main controlling database that can link to many and any contract database. The contract databases are opened as and when required.

On the lighter side, a contract's six stages:
Anticipation and confidence with a quotation
Euphoria and praise on receiving the order
Excitement and enthusiasm in pushing it through
Relief and pride as it is completed
Realisation of mistakes and losses
Search for the guilty

 

[NauticalGent]

On the lighter side, a contract's six stages:
Anticipation and confidence with a quotation
Euphoria and praise on receiving the order
Excitement and enthusiasm in pushing it through
Relief and pride as it is completed
Realisation of mistakes and losses
Search for the guilty

Reminds of this article I read this morning...

 

[Pat Hartman]

Couple of reasons why this way should work best for us.

I don't see any compelling reason to split the data. One thing that would convince me is if you have to share the database with the client. That would cause a security issue and that would convince me that splitting was the best option.

From what you have described as your requirements, I would not create a separate BE for each project. Since it is quite easy using queries to control what data you are working with, splitting doesn't get you anything. But it does loose something. If you want to make summary reports for management, it would be far easier with all the data in a single BE than if you had to try to wrangle data from all the active BE's.

 

[Solo712]

This is one instance where I have to disagree with the majority opinion expressed here. Creating a "switchboard" that connects a single FEs to different BEs poses no added security issues (per se) that I can see. It can be gainfully used for managing multiple projects or business accounts (I have done it on two commercial projects with success), or to create separate development, test and production environments. Whatever access security issues exist for this setup, they do not originate from using multiple back ends and be resolved by a custom user security management.

I developed my BE switchboard using an excellent and robust technique published by Andrew Couch in "Access 2010 VBA Programming Inside Out". Andrew's relinking engine is featured in chapter 12, "Linking Access Tables", subsection "Automating Relinking". The book proved well worth buying.

 

[NauticalGent]

This is one instance where I have to disagree with the majority opinion expressed here.

One of many! Not saying that is a bad thing, but you are definitely a "against the grain" type of guy...!

 

[Pat Hartman]

This is one instance where I have to disagree with the majority opinion expressed here

I guess no one has ever asked you to do consolidated reporting That becomes ever so much harder when you are dealing with multiple BE's. Not that you can't do it. You just have to code your way into creating a union query with n embedded queries. It's still easier than trying to do it with a bunch of spreadsheets.

 

[Cotswold]

I guess no one has ever asked you to do consolidated reporting That becomes ever so much harder when you are dealing with multiple BE's. Not that you can't do it. You just have to code your way into creating a union query with n embedded queries. It's still easier than trying to do it with a bunch of spreadsheets.

The system I described had no need for consolidated reporting.

Overview
MasterFE program database with objects to access data to MasterBE and ContractBE
MasterBE database
Multiple ContractBE databases

All main reporting is from a MasterFE database that is attached to the MasterBE database where all of the contracts' summary data is held.
When a ContractBE is loaded and amended the changes are updated to the MasterBE database. All day to day reporting on cutting lists, loading lists, delivery notes etc, are taken from the ContactBE database. Each separate ContractBE database is opened from the MasterFE. So at any one time the MasterFE has two databases open per user. (MasterBE and a ContractBE) Once opened these two operating together are indistinguishable from a single BE. As data is added (or imported from CAD) to the ContractBE, the hours, phase, despatched and erection tonnages are updated into the MasterBE real time and not batched.

All management reporting is from the MasterBE database where the weekly and daily totals for different aspects of production are held, after being updated as described during amendments in the ContractBE.

In this type of company the amount of data in a contract can be huge. Even smaller contracts of 50 tonnes will contain many thousands of records, let alone those of 200, 300 or 1,000 tonnes. In quantity these contracts will range from many dozens to small hundreds in one year, depending upon company size. The space required will rapidly burst through 2GB. To store all contracts in a single Access database would simply not work in practical terms. Additionally, whilst a contract may be live and in use for 12 to 18 months from inception to completion, once it has been finished it is no longer of interest to anyone and is unlikely to be opened again.

However all contract summary data is available instantly through the MasterFE from the MasterBE data. In addition to contract analysis it is then simple to provide month on month, or year on year management totals. Plus if required any ContractBE is easily accessed if required for reference purposes. So why keep all of that redundant data sloshing about in a huge database? Data that may never be looked at again. If it was in a single database before long you will need a complex archiving system and then need to provide access to various archived data. Which can be a job in itself.

The system is easily managed. It works very well and has done for many years.

 

[Pat Hartman]

The system I described had no need for consolidated reporting.

Then it doesn't matter if you want to keep separate files.

 

[LarryE]

I make no judgement as to whether your company should allow or not allow users to connect to different Backend files but here is a Public Function I have used to do that:

Public Function SelectBEFile()
On Error GoTo SelectBEFile_Error
Dim ConnectDataFileDialog As FileDialog
Set ConnectDataFileDialog = Application.FileDialog(msoFileDialogFilePicker)
Dim SelectedFile As Variant
Dim tdf As DAO.TableDef
Dim BackEnd As String
Dim db As DAO.Database
Set db = CurrentDb
With ConnectDataFileDialog
    .AllowMultiSelect = False
    .Title = "Select A Backend File"
    .ButtonName = "Connect"
    .Filters.Clear
    .Filters.Add "Access Files", "*.accdb", 1
    .FilterIndex = 1
    If .Show = -1 Then 'If user selected a file
        For Each SelectedFile In .SelectedItems
            BackEnd = ";Database=" & SelectedFile & ""
            If Len(BackEnd) > 1 Then
                For Each tdf In db.TableDefs
                    Set tdf = db.TableDefs(tdf.Name)
                    If Not tdf.Name Like "MS*" Then
                        'MsgBox tdf.Name
                        tdf.Connect = BackEnd
                        tdf.RefreshLink
                    End If
                Next 'tdf
            End If
        Next 'Selected File
    End If 'Show
End With
Exit Function
SelectBEFile_Error:
DoCmd.CancelEvent
'MsgBox Err.DESCRIPTION
Resume Next
Exit Function
End Function

I would just call it from a command button On Click event. It uses FilePicker instead of a combobox so your computers will require the ACCESS Objects Library included in their references.

 

[gemma-the-husky]

^^^
Basically that's what I do.
One catch - after I connect the first table, I create a permanent connection to that table, and then connect the rest. a 30 table back end will connect in a couple of seconds this way, as opposed to 30-60 seconds if each connection has to pass windows "tests".

 

[NauticalGent]

I create a permanent connection to that table, and then connect the rest.

How do you go about that Dave?