Am I doing this right? Scaling up database to cope with 35+ users

[Desmond_Fox]

Hi,

I was hoping someone knowledgeable could advise whether my solution below is the right one.

The short version: does using a MySQL back-end (hosted on internet) and Access front end overcome issues of 30+ users accessing/editing the database at same time?

The longer version...First some background:

I have just finished developing an Access database application that tracks student results for the local school.

I split my front-end from the database back-end.

The plan is to distribute copies of the front end to 35+ teachers, and save the back-end on a network drive.

Now I'm a bit worried that a large number of teachers might try updating the database at the same time with unpredictable results. At first, I thought the database would cope with 15 or so simultaneous users, but I'm guessing anything more could result in errors/problems for the teachers.

My proposed solution.

I tested a MySQL back-end database that was hosted on my internet site (I use a hosting provider with a cPanel setup that gives you MySQL). I'm using Access as the front end and connecting using a MySQL ODBC driver. My first test seemed to run very well (MySQL hosted on the internet was a bit slower of course).

I'm just wondering if this a good solution to my problem (i.e. multiuser access).

Grateful for anyone's advice or pointers to a better solution?

Regards
Des

 

[RainLover]

Are you saying that each user has a Front End on their C:\Drive and they are all connected to MySQL which resides on the internet.

 

[Desmond_Fox]

Yes, precisely. Front end on c:\ and connected to MySQL on internet.

If there is a problem with that approach or a better way, I'd be very glad for any advice.

Des.

 

[RainLover]

G'day Do you actually have this working. Namely you have the Front End residing on the C:\Drive and the back end is out there on the Internet. You open the C:\Drive make an entry eg a new record and this is automatically written to the Back End which is on the Internet. So if this is correct you should be able to email me the front end and I should have full working access to the back end. I don't actually want you to send it to me. I would be very interested if you could do this as I do not know how to do it myself. Assuming that this is a smallish database there is no need to move to MySQL. However if things are going to grow then now is as good a time as any to upsize. Looking forward to your reply.

 

[Desmond_Fox]

Hi, that is exactly what I have got working.

If you're interested in doing something similar, here are the steps

1. Using your MySQL interface, create a database. I used a cPanel hosting account which comes with an interface for MySQL.

2. Add a user for that database and create a password

3. Setup remote access to the MySQL database. I'm not sure if this is just a cPanel requirement, but to allow remote access you need to specify the IP addresses (or block of IP addresses using % wildcard) of the computers where the front end resides.

4. If you have an existing database backend, export it to an SQL dump file using Bullzip MS Access to MySQL. You can actually use that program to export from Access directly to MySQL but I prefer to create the dump file instead.

5. Import that dump in step 4 into your mysql database.

6. In Access, follow this guide https://www.youtube.com/watch?v=6kc5GgNnlsM

I replaced the table links previously referencing my c:\backend with links to the MySQL tables. So when I use the front end it works like this:

Modify data using front end on c:\ --> references tables residing in MySQL

And its done.

Hope that explanation suffices in place of a live example. If I were to share the front-end, I'd need to contact my hosting provider with your IP address to allow remote access.

After doing some more research, I think I will leave the MySQL solution for now and just see how an access backend will cope. From what I read, it should be able to handle around 30 users.

Des

 

[Acropolis]

I have a MS Access front end running on a MySQL backend and it works fantastically, the MySQL is hosted internally within the company not on the internet but same principal applies really.

I found it worked much better than when it was an MS Access backend much more reliable and quicker, and using views etc. has been useful.

I had to change a couple of parts of the code, when I was pulling back the ID for a record I had just created as it wouldn't work in the same way as the Access backend, but other than that no problem at all.

The most annoying part is having to install ODBC connections on each machine that wants to connect to it, which can be a pain in the backside on a domain controlled PC as typically the local user doesn't have admin rights to install software, or set up the connections, and trying to get IT to do it can be hit and miss.

 

[RainLover]

I have a MS Access front end running on a MySQL backend and it works fantastically, the MySQL is hosted internally within the company not on the internet but same principal applies really.

I found it worked much better than when it was an MS Access backend much more reliable and quicker, and using views etc. has been useful.

I had to change a couple of parts of the code, when I was pulling back the ID for a record I had just created as it wouldn't work in the same way as the Access backend, but other than that no problem at all.

The most annoying part is having to install ODBC connections on each machine that wants to connect to it, which can be a pain in the backside on a domain controlled PC as typically the local user doesn't have admin rights to install software, or set up the connections, and trying to get IT to do it can be hit and miss.

I think you got that right, all the way down to your IT Department. There are a few more differences between MySQL and Access but nothing too hard to handle. Thanks for jumping in and sharing your thoughts. MySQL on the server is what I have done in the past. I have never done this on the Internet. I have my reservations about this but I shall look further when I have the spare time.

 

[RainLover]

Acroplis If you are going to convert to MySQL rather than start afresh be careful about the data types. Many of these will be different and may require your intervention. If my memory serves me correctly AutoNumber is lost so you need to create a new one. Not 100% sure about this but I am sure some things do not convert properly.

 

[RainLover]

Yes, precisely. Front end on c:\ and connected to MySQL on internet.

If there is a problem with that approach or a better way, I'd be very glad for any advice.

Des.

After viewing the uTube tut it appears that you are NOT connecting to the internet but merely the local server. I thought that you had found a way to connect to the Back End using the Internet when you are no where near the office. You may be in a different country in a Motel room. This is where I want to connect to the MySQL back end. From uTube you mest be connected first to the Local Area Network not the Internet. Do you agree with this.