0

My current team of 20 people use an Access database front end on their desktop. The backend of the access database is on a network drive. I have been asked to create a Access database front end with MySQL as the back end. I have installed the MySQL workbench and the ODBC connector on my computer. I have created the schema and tables and I have connected the front end of the database connected to the MySQL table I created in workbench.

My questions are:

  1. How do I deploy this for the team to use. I believe I can move the front end of the Access database to the network drive and the team can copy it to their desktop. But what do I do about the MySQL backend? Where should it be placed?
  2. Does the team need to have the ODBC connector installed in their computers?
  3. Should I move the MySQL workbench to the network drive?

PS: I am a new developer and just learning about databases and I am only familiar with Access databases.

3

Usually, when you want to keep the Access front end, you migrate from Access DB to SQL Server (Express).

But what do I do about the MySQL backend? Where should it be placed?

Database servers are usually located on a dedicated "server". Now days, that server can be Virtualized. Many regulations require limited physical access to that server (eg physically placed in a locked "server room").

  1. Should I move the mysql workbench to the network drive?

It looks like MySQL Workbench can be used as an Admin tool. Only developers and DBAs need it installed. Placing it on a network drive may not be the best method when you are in a disaster situation.

  1. does the team need to have the ODBC connector installed in their computers?

Everyone using the Access front-end will probably need the ODBC driver for the backend (DB Server). This can make deployment (and maintenance) a small nightmare.

  1. how do I deploy this for the team to use.

I don't know.

PS: I am a new developer and just learning about databases

The first commandment of a Database Administrator (DBA) is Thou shall not lose data. Make backups and test them on a regular basis.

Work with the Business to find out how frequently you need to do backup, how often you should test, and how long to keep the backups. There are Financial commitments that need to be made and probably some Legal ones too. These decisions are "above your pay grade".

Try to document everything. "Security" should be one of the first. (Who can do What to Which pieces of data). This information is usually broken down into ROLEs.

Backup/Recovery procedures will also need to be well documented. This includes the Business Requirements gather from above.

Use a Code Repository to store your CREATE scripts, etc.

| improve this answer | |
1

You are creating a client-server application, which is conceptually different from storing a database file on a shared file system. To your questions:

  1. MySQL should run on its own server ideally.
  2. Yes.
  3. Depends on why you need it.
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.