Need some clarification on split Access database (1 Viewer)

Hi guys

Just need a little bit of clarification. So presently I have this database that I split up and just to be clear, I know that when you split a database, Access adds "_be" meaning back end but is this where developers work or users interact with? Because when I open up _be.accdb all I see are tables that are linked to the original accdb copy. If this isn't the developer's file, and its actually the user's side, how will they interact with other things I've created? such as reports and forms since all there is in the _be.accdb are the linked tables? Any thoughts are welcome!

the BE is where your actual tables is
stored.

on the FE, only link to the table
are left. also the forms, reports,
query, modules and macros.

arnelgp said:

the BE is where your actual tables is
stored.

on the FE, only link to the table
are left. also the forms, reports,
query, modules and macros.

Click to expand...

Okay so If the user wants to use this database to enter new records and at the same time, I can make changes to the database, which copy does each person use?

Your Backend database file should only have the tables in it, this is shared on a network location that everyone should be able to see and have read write access to.

The front end database has everything else in it except the tables - which should be linked to the BE.

Each user has a local copy of the FE on their machine.

When you do updates to the front end, you use a method to update the users copy of that Front End. There are lots of ways of managing this, search on here for AutoFE updaters.

you will distribute the FE.

but before you do that, make sure
to put your BE on a shared folder
or network.

then open the FE. and on the Ribbon,
External Data->Linked Table Manager, relink
your tables to point to the new location
of the BE on the shared/network folder.

then you may distribute copies of your
FE.

you will also need to
modify the security settings
of the shared/network folder
to allow Read/Write/Modify.

Minty said:

Your Backend database file should only have the tables in it, this is shared on a network location that everyone should be able to see and have read write access to.

The front end database has everything else in it except the tables - which should be linked to the BE.

Each user has a local copy of the FE on their machine.

When you do updates to the front end, you use a method to update the users copy of that Front End. There are lots of ways of managing this, search on here for AutoFE updaters.

Click to expand...

Thank you. Sorry for being dense but the FE is used by both front end users and also developers? And that is simply the databasename.accdb file, correct?

Not really - Normally you keep the distribute-able working version of the front end on a read only folder on your network that your users can get to, to allow them to reload a copy if required. You also have a backup of it safely tucked somewhere else.

On your development machine you have your "working on it" copy. Personally I would recommend this is linked to a offline backup of your online data. That way if you inadvertently run the Delete * from table you haven't mullered your live data.

When you are happy that your latest version of your masterpiece is ready for distribution, you keep a back up copy of the current working one (in case you need to roll back) and put the new version on to the safe location for everyone to upload to their local drive.

In fact I keep dozens of previous versions because storage is cheap, and clean then out occasionally.

In fact Ridders has just posted his automated FE updating tool here:
https://www.access-programmers.co.uk/forums/showpost.php?p=1548509&postcount=10

I would look at how it works for a neat method.

The developer always works on his own copy of the FE and his own copy of the BE. He needs to maintain discipline in creating appropriate backups to keep from loosing work in progress. Once a change is complete and tested, the development copy of the FE is moved to the production backup folder and relinked to the the production BE and the final move is to the distribution folder that holds the current master copy.

Making changes to the BE are much more involved depending on what you have to do. The developer cannot modify the design of the BE during normal work hours when users might be updating data so all BE changes should be done in off hours or by first getting all users to log out of the application. If you are adding a new table or adding a column to an existing table, it is relatively safe to make those changes to the BE (when no one is in it) and then copy the BE to the developer's personal folder. Then the changes to the FE can be made to work with the new table/field. After testing, the FE can be moved and relinked and since the fields/tables have already been modified, the code should still work - it is always wise to test at this stage just in case.

More extensive changes to the schema are more difficult. The problem is that you will be making structural changes to the schema and will need to get those structural changes merged into the production database and since the BE is only off line and available for updating during limited periods, you need to be very organized.

I have applications that are sold to the public so unlike inhouse apps where I have direct access to the BE, I need to create programmed processes to update databases at client sites. The simplest way I have found to do this involves converting the original BE to SQL Server and then making a copy which I make the schema changes to. I have a software tool (~$400) that will compare two databases and generate SQL DDL to alter structure A to match structure B. The SQL DDL is the same as what I need for Access except for minor changes that can be made with a global change using a text editor. Of course if I could find a tool that worked directly with Access to generate the DDL, that would be easier but we work with what we have and I trust software to identify even the minutest difference so I don't miss anything. Then I create an Access app to run the DDL and send it to the clients who want to upgrade.

Pat Hartman said:

I have applications that are sold to the public so unlike inhouse apps where I have direct access to the BE, I need to create programmed processes to update databases at client sites. The simplest way I have found to do this involves converting the original BE to SQL Server and then making a copy which I make the schema changes to. I have a software tool (~$400) that will compare two databases and generate SQL DDL to alter structure A to match structure B. The SQL DDL is the same as what I need for Access except for minor changes that can be made with a global change using a text editor. Of course if I could find a tool that worked directly with Access to generate the DDL, that would be easier but we work with what we have and I trust software to identify even the minutest difference so I don't miss anything. Then I create an Access app to run the DDL and send it to the clients who want to upgrade.

Click to expand...

I have a similar approach to that described by Pat for modifying the database table structure.
My commercial databases use Access FE with SQL Server BE.
Changes are only made when a new version is released.

The version update installation file will typically include a SQL server script which runs automatically as part of the update. This is used to add/modify or occasionally delete SQL tables. It also updates version info in the registry.
The script includes safeguards for preventing errors if the script is repeated for any reason. I also include an undo script in case something goes wrong (which it never has ... so far!).

Once the SQL script has completed, the updated FE opens and runs any other changes specific to the FE
e.g. changes to the list of linked SQL tables.

After that the updated FE will be placed in a designated network folder & is copied automatically to users' own computers using the utility in the link given by Minty

For info I'm attaching a typical SQL server script file - I've added a .txt suffix so I could post it here.
This is a standard text file so can be viewed in Notepad