Making changes to a split database

[ChrisSedgwick]

Hi,

I'm working on a database that's been split (front end/back end). The database is being used by numerous people in my workplace and I want to make some changes.

My question is, if I make changes to the front end, i.e design changes to the forms, or adding some command button etc. Will it affect the user that are using the database and if so, is there a way I can work on the database without it affecting the people using it?

Thanks,
Chris.

 

[Galaxiom]

Work on a copy of the front end then distribute the modified version to the users.

Each user should have a separate copy of the front end.

 

[ChrisSedgwick]

Hi Galaxiom,

Thank you for your quick and helpful response. Does this also apply if I'm adding new fields to a table and also If I re-design a report i.e layout etc? Will it affect other users copies too, even if they have their own copy?

My questions are specific as these are the immediate changes I will be making.

Thanks once again for your help.

Chris.

 

[Galaxiom]

The table would usually be in the Back End. You would not want anyone in the system while that was changed.

You could work with a copy of the back end then append any new records that had been created in the live system while you were working on the new back end. You will still need a short outage while you swap the back ends over and append the new records.

Changing your copy of the front end won't affect any others.

 

[Rx_]

This is something that I did once.
Took front-end and used SQL Server Migration Manager for Access to create the SQL Server back end. Keep the script.
Create a new front-end project and use the linked table to connect to SQL Server get rid of local tables or Access linked tables.

Get your front-end applicaiton working with SQL Server links.
Keep track of every change made in SQL during this process.
During this time, your users are still using the Access DB (adding data).

Once your new front-end works, over a weekend with nobody using the DB, re-do the migrate of Access to SQL Server making all of the changes noted.
Test it over the weekend. The fall-back is going back to the same Access.

Be sure to heed the advice above of Each User should have a copy of the Front End.

The SQL Server Migration Wizard for Access will also allow the queries to be converted into Views. It is something to consider. The SQL views may not sort the same as an Access Query. But, the performance could be better with views.

One last thing, if you have any Access tables with no Primary Key (typically something like an auto-counter) they may not migrate to SQL. Or if they do, they may not be updateable.