Ioannis Syrenidis
asked on
Allow Multiple Users to Work on the Same Access DB through an excel file
Hello there,
I have a question in regards to Access Database. At the moment I am working on a project where: the user interface is an excel spreadsheet and the back end is an Access database. I am using DAO objects etc. to query the database (SELECT/INSERT/UPDATE) and everything is working fine.
Now because the project I have been working on it is going to be used by multiple users the database is saved onto OneDrive and I have synced it to my desktop (all users will have to sync it). While I was testing the database I noticed that if more than one users using the excel file at the same time, when it comes the time to insert or update or delete something from the database, a copy of the database is being created with the same name plus the computer name in the name of the copied database. For example, "WPQTPBDB.accdb" is the name of the original database, if more than one user working on it at the same time a copy of the database with name "WPQTPBDB - computer name.accdb" is created. As a result I am ending up having multiple databases, one for each user.
Is there any solution to my problem, to allow users to be working on the original database without a copy to be created?
Thank you in advance.
I have a question in regards to Access Database. At the moment I am working on a project where: the user interface is an excel spreadsheet and the back end is an Access database. I am using DAO objects etc. to query the database (SELECT/INSERT/UPDATE) and everything is working fine.
Now because the project I have been working on it is going to be used by multiple users the database is saved onto OneDrive and I have synced it to my desktop (all users will have to sync it). While I was testing the database I noticed that if more than one users using the excel file at the same time, when it comes the time to insert or update or delete something from the database, a copy of the database is being created with the same name plus the computer name in the name of the copied database. For example, "WPQTPBDB.accdb" is the name of the original database, if more than one user working on it at the same time a copy of the database with name "WPQTPBDB - computer name.accdb" is created. As a result I am ending up having multiple databases, one for each user.
Is there any solution to my problem, to allow users to be working on the original database without a copy to be created?
Thank you in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ioannis,
Yes that will solve the multi-user problem, but you will step into another problem.
With an Access DB (a 'JET' or 'ACE' DB - .mdb/.accdb), you can basically use it only on a LAN (Local Area Network) and not on a WAN (Wide Area Network) or you will have problems with database corruption.
It's not the type of network per say, but the performance of the network. If you have a very high end WAN that acts more like a LAN, then you can get away with it. An example of that would be a dedicated point to point between two buildings over something like fiber optic. By the same token, you could have a LAN with poor wireless connectivity and still have issues.
JET/ACE was designed to work on a LAN and because of that, it can't handle much more than 30ms of network latency and needs a speed of 40 mb/sec minimum to give decent performance. Latency is the critical factor and you would not get that over the internet.
If you want something out in the "cloud" on the internet, you would need to be using a SQL Server Database (either running on a hosted server or using Azure) to store the data and that is something you would need to take into consideration when developing it. With such an app, you would strive to minimize the amount of data going over the network.
The only way to use an Access database in the cloud is to have an RD (Remote Desktop) server which has the app and the database on it, and then have the user(s) login to a session on that. By doing so, the app runs locally and only the Keyboard, Video, and Mouse data runs over the network connection.
Jim.
If I move the database to a server, will I resolve my issue?
Yes that will solve the multi-user problem, but you will step into another problem.
With an Access DB (a 'JET' or 'ACE' DB - .mdb/.accdb), you can basically use it only on a LAN (Local Area Network) and not on a WAN (Wide Area Network) or you will have problems with database corruption.
It's not the type of network per say, but the performance of the network. If you have a very high end WAN that acts more like a LAN, then you can get away with it. An example of that would be a dedicated point to point between two buildings over something like fiber optic. By the same token, you could have a LAN with poor wireless connectivity and still have issues.
JET/ACE was designed to work on a LAN and because of that, it can't handle much more than 30ms of network latency and needs a speed of 40 mb/sec minimum to give decent performance. Latency is the critical factor and you would not get that over the internet.
If you want something out in the "cloud" on the internet, you would need to be using a SQL Server Database (either running on a hosted server or using Azure) to store the data and that is something you would need to take into consideration when developing it. With such an app, you would strive to minimize the amount of data going over the network.
The only way to use an Access database in the cloud is to have an RD (Remote Desktop) server which has the app and the database on it, and then have the user(s) login to a session on that. By doing so, the app runs locally and only the Keyboard, Video, and Mouse data runs over the network connection.
Jim.
ASKER
Hello Jim,
I can't explain how helpful is your answer. Unfortunately, we have a lot of WAN high end, as a result using a server is not an option. Realistically speaking Azure SQL database is the only option for that matter and I have already started looking at it.
Just want to confirm that
1. Using the Azure SQL I will have resolved the multi-user access to the database?
2. I can still use Excel spreadsheet to query the Azure SQL Database?
Thank you very much.
Regards,
Ioannis
I can't explain how helpful is your answer. Unfortunately, we have a lot of WAN high end, as a result using a server is not an option. Realistically speaking Azure SQL database is the only option for that matter and I have already started looking at it.
Just want to confirm that
1. Using the Azure SQL I will have resolved the multi-user access to the database?
2. I can still use Excel spreadsheet to query the Azure SQL Database?
Thank you very much.
Regards,
Ioannis
Yes and Yes
Jim
Jim
Access is a Rapid Application Development Tool (RAD). It is NOT a database engine. The desktop database engines that are frequently confused with Access are Jet (.mdb) and ACE (.accdb). Access creates your FE. It creates forms and reports and query and runs your code and queries and is the glue. Data can be held in ANY ODBC compliant database such as Jet/ACE (which are "native" to Access), SQL Server, DB2, etc. Access is NOT a database engine and Jet/ACE, Azure, and SQL Server cannot create an appication interface.
The operative word here is "desktop". The implication is that id you are using Jet or ACE to hold your data, you are running the application on your desktop as a stand-alone application or over a LAN where you are sharing the data with other users.
If your FE is not Access, then your BE should NOT be Jet/ACE. If you need to run an application over the internet, Access is simply a non-starter unless you are willing to use RD or Citrix. From what you are saying, you are not using Access at all. You want a shared database engine. Therefore Azure would work for that. However, Excel is a seriously poor choice as the FE for a database application.
The operative word here is "desktop". The implication is that id you are using Jet or ACE to hold your data, you are running the application on your desktop as a stand-alone application or over a LAN where you are sharing the data with other users.
If your FE is not Access, then your BE should NOT be Jet/ACE. If you need to run an application over the internet, Access is simply a non-starter unless you are willing to use RD or Citrix. From what you are saying, you are not using Access at all. You want a shared database engine. Therefore Azure would work for that. However, Excel is a seriously poor choice as the FE for a database application.
ASKER
Thank you all for your help. I have started with Azure SQL Database and hopefully I won't have any other issue.
One thing I want to mention, is that using Excel as A Front-End wasn't my choice. This was and ongoing project that had already started in Excel and must be delivered in two months. So, yes unfortunately must be implemented in Excel. Ideally, that should have been a Web app.
Thank you once again. Have a nice day.
Regards,
Ioannis
One thing I want to mention, is that using Excel as A Front-End wasn't my choice. This was and ongoing project that had already started in Excel and must be delivered in two months. So, yes unfortunately must be implemented in Excel. Ideally, that should have been a Web app.
Thank you once again. Have a nice day.
Regards,
Ioannis
You can always redo your FE to Access and resolve a lot of problems ...in a flash..
ASKER
Thank you for your comment. If I move the database to a server, will I resolve my issue? And what do you mean by server, database server?
Regards,
Ioannis Syrenidis