Best solution to multiusers

[jqfranco]

Good afternoon
I need to redo a file in excel + vba to a new file with MySQL.
At the moment it doesn't work because I can't make it multiuser and we have a lot of problems with Onedrive synchronization, where the file is. It's almost unfeasible.

I would like to know if choosing to have a MySQL database and using Excel as a frontoffice on each of our personal computers, can we have a reliable multiuser system?.

In order not to have difficulties with synchronization with Onedrive, where could the database be? We don't have any kind of personal server.

Thank you for your help

 

[starl]

Are you counting on OneDrive because no one has access to a central Network? My experience with clients using OneDrive (for shared usage) has not been the greatest and I try to avoid it. I also instruct clients to not share workbooks (horrors!). What I usually do is have Excel be the interface (each user gets a copy) and, when there is a central network, setup an (Access) database. For clients who require a cloud database - then that's MySQL. But I've only had one of those, recently.
I'm not fully knowledgeable on the options, but for cloud - said client is using Amazon's Digital Ocean. But there are cheap services available, if you want more control over your cloud server.
The biggest issue for that project was finding a free ODBC driver.
I like the Access option because, if they're using Windows - no additional drivers needed. But it's limited to a shared network, size, number of active users.

So, the simple answer to your one question is Yes, you can use Excel as the UI to a proper database. As to where it could be - if users have access to a local network that works, else, there are various cloud database options. If your company already has a website, they may already have the option for a database server.

 

[jqfranco]

Are you counting on OneDrive because no one has access to a central Network? My experience with clients using OneDrive (for shared usage) has not been the greatest and I try to avoid it. I also instruct clients to not share workbooks (horrors!). What I usually do is have Excel be the interface (each user gets a copy) and, when there is a central network, setup an (Access) database. For clients who require a cloud database - then that's MySQL. But I've only had one of those, recently.
I'm not fully knowledgeable on the options, but for cloud - said client is using Amazon's Digital Ocean. But there are cheap services available, if you want more control over your cloud server.
The biggest issue for that project was finding a free ODBC driver.
I like the Access option because, if they're using Windows - no additional drivers needed. But it's limited to a shared network, size, number of active users.

So, the simple answer to your one question is Yes, you can use Excel as the UI to a proper database. As to where it could be - if users have access to a local network that works, else, there are various cloud database options. If your company already has a website, they may already have the option for a database server.

hello starl
You already managed to clarify some important things for me, so thanks for the help.
Actually, using Onedrive is not having time to sleep at night .
In the company we have a network between the computers. The problem is that some people need to access the database from their homes or construction sites where they are working.
I don't know if just for this reason, it requires us to have a Cloud service.
The number of users will be around 6 people and the volume of data will not be very extensive. Right now our tables have about 1500 rows of data.
We also have unlimited access to MYSQL where our website is located. I don't understand the OBDC issue. I thought it was included in Excel, but apparently not. Thanks again for your help.

 

[starl]

I'm far (so far) from being an expert on this, but this is my experience: if you want to use Excel's built-in tools (Get Data, Other Sources, from ODBC), you have to provide the DSN. Which you configure through the ODBC Data Source Admin tool (Windows?). The default drivers there are dBase, Excel, and Access. I had to find and add my own ODBC driver. Then, after configuring the DSN, Excel could use it to connect to the database.
For code, you still have to install the ODBC driver, but then you have to figure out the connection string.

I do have a client that has, in Excel, successfully connected to a database and run a Power Query. I was trying to figure out the connection string for the database, but the db provider is no help and I just couldn't figure it out. But, the workbook does have a successful Power Query link, and I figured out I could rewrite the query and use it. Not as snazzy as pulling the results into memory (they're on a sheet), but it's better than nothing (the original Power Query returned 3 tables which he then did stuff with.. lots of work. My solution - a single query across all 3 tables!)
Note that the database I was trying to connect came from another company (software my client bought). Another client had a cloud-based database at Amazon and that connectionstring wasn't that much trouble.
I'd imagine since you control your website and configure it the way you want, you should be able to do the connectstring method. But everyone will need an ODBC driver installed (personally, i use a free one, it's a slightly manual process; easy enough to do, but wouldn't want my clients trying to do it themselves.)