[Solved]vb.Net front end, Access back end in Multi User App = bad idea?
-
Hello I did a stock control app in vb.net that uses an Access 2007 db. I connect using ACE.OLEDB.12.0 The Access db is located on the server, and the app is loaded on the workstations. They make a backup of the Access file every night. (Simply copy and paste to another location) There are about 10 users, reading and writing the same tables They have been running for a week so far, and up to now have already had one error where when trying to access data I got an exception " Unable to access the database" From comments on the net, Access is a really bad idea (Corruption, slow connections in multi user apps etc etc) They are a small company, so a licensed SQL2008 server is not practical financially. MySQL seems like a logical free option? My Questions: 1) As a temporary fix while migrating to a 'proper' db, I have read about creating a second access db, and linking all the tables in the 'back-end' db, to the 'front-end' db, so that if the first db gets corrupted, the 'back-end' db will remain in tact. Does this seem like a good idea? 2) Is MySQL the best choice for a db in this situation? 3) How easy it it to install and configure a MySQL database 4) Are there connection objects in .Net for MySQL (I saw an ODBC connector on the MySQL download page - is this the best way to connect?) 5) How do you back up a MySQL Db - do you need special agents? 6) Are there any other problems/differences I should be aware of when going the MySQL route? (I have noticed some differences in SQL syntax, parametrized queries etc, and also I use AutoNumber fields in Access to get unique Sales/Purchase Order Numbers I would be appreciative of any comments! Thanks in advance Richard
-
Hello I did a stock control app in vb.net that uses an Access 2007 db. I connect using ACE.OLEDB.12.0 The Access db is located on the server, and the app is loaded on the workstations. They make a backup of the Access file every night. (Simply copy and paste to another location) There are about 10 users, reading and writing the same tables They have been running for a week so far, and up to now have already had one error where when trying to access data I got an exception " Unable to access the database" From comments on the net, Access is a really bad idea (Corruption, slow connections in multi user apps etc etc) They are a small company, so a licensed SQL2008 server is not practical financially. MySQL seems like a logical free option? My Questions: 1) As a temporary fix while migrating to a 'proper' db, I have read about creating a second access db, and linking all the tables in the 'back-end' db, to the 'front-end' db, so that if the first db gets corrupted, the 'back-end' db will remain in tact. Does this seem like a good idea? 2) Is MySQL the best choice for a db in this situation? 3) How easy it it to install and configure a MySQL database 4) Are there connection objects in .Net for MySQL (I saw an ODBC connector on the MySQL download page - is this the best way to connect?) 5) How do you back up a MySQL Db - do you need special agents? 6) Are there any other problems/differences I should be aware of when going the MySQL route? (I have noticed some differences in SQL syntax, parametrized queries etc, and also I use AutoNumber fields in Access to get unique Sales/Purchase Order Numbers I would be appreciative of any comments! Thanks in advance Richard
Have you given any thoughts on using SQL Server Express? It can do everything Access can do better, integrates very well with .net, gives you powerful upgrade options and is for free. (until you upgrade that is)
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Hello I did a stock control app in vb.net that uses an Access 2007 db. I connect using ACE.OLEDB.12.0 The Access db is located on the server, and the app is loaded on the workstations. They make a backup of the Access file every night. (Simply copy and paste to another location) There are about 10 users, reading and writing the same tables They have been running for a week so far, and up to now have already had one error where when trying to access data I got an exception " Unable to access the database" From comments on the net, Access is a really bad idea (Corruption, slow connections in multi user apps etc etc) They are a small company, so a licensed SQL2008 server is not practical financially. MySQL seems like a logical free option? My Questions: 1) As a temporary fix while migrating to a 'proper' db, I have read about creating a second access db, and linking all the tables in the 'back-end' db, to the 'front-end' db, so that if the first db gets corrupted, the 'back-end' db will remain in tact. Does this seem like a good idea? 2) Is MySQL the best choice for a db in this situation? 3) How easy it it to install and configure a MySQL database 4) Are there connection objects in .Net for MySQL (I saw an ODBC connector on the MySQL download page - is this the best way to connect?) 5) How do you back up a MySQL Db - do you need special agents? 6) Are there any other problems/differences I should be aware of when going the MySQL route? (I have noticed some differences in SQL syntax, parametrized queries etc, and also I use AutoNumber fields in Access to get unique Sales/Purchase Order Numbers I would be appreciative of any comments! Thanks in advance Richard
- This is valid when you have both the UI and the data in Access, your UI is in VB.net so this scenario does not apply. 2) No - MySql has less support than SQL Server and Oracle (and there are other good options out there). I would recommend SQL Server Express (relevant limitation is 4gb database size) as there are a MUCH wider resource base for support and training. There is a valid upgrade path though you then need to pay. And there are many more human resources (potential employees) with SQL Server skills. 3) Very easy, the gotchas are minimal, case sensitivity (none) and collation are the one I hate, just accept the default. 4) Connection strings 5) No idea but there will be a MySQL support site dedicated to this type of question (I just don't know it) 6) SQL server used to provide an upgrade wizard for Access to SQL Server, not sure it MySQL does the same. As you may infer I am not a fan of MySQL (also no experience) and would urge you to use SQL Server.
Never underestimate the power of human stupidity RAH
-
Hello I did a stock control app in vb.net that uses an Access 2007 db. I connect using ACE.OLEDB.12.0 The Access db is located on the server, and the app is loaded on the workstations. They make a backup of the Access file every night. (Simply copy and paste to another location) There are about 10 users, reading and writing the same tables They have been running for a week so far, and up to now have already had one error where when trying to access data I got an exception " Unable to access the database" From comments on the net, Access is a really bad idea (Corruption, slow connections in multi user apps etc etc) They are a small company, so a licensed SQL2008 server is not practical financially. MySQL seems like a logical free option? My Questions: 1) As a temporary fix while migrating to a 'proper' db, I have read about creating a second access db, and linking all the tables in the 'back-end' db, to the 'front-end' db, so that if the first db gets corrupted, the 'back-end' db will remain in tact. Does this seem like a good idea? 2) Is MySQL the best choice for a db in this situation? 3) How easy it it to install and configure a MySQL database 4) Are there connection objects in .Net for MySQL (I saw an ODBC connector on the MySQL download page - is this the best way to connect?) 5) How do you back up a MySQL Db - do you need special agents? 6) Are there any other problems/differences I should be aware of when going the MySQL route? (I have noticed some differences in SQL syntax, parametrized queries etc, and also I use AutoNumber fields in Access to get unique Sales/Purchase Order Numbers I would be appreciative of any comments! Thanks in advance Richard
- No. I use MySQL only when I have to. For small configurations like yours I recommend SQL Server Express 2008R2; it works very well, much better than Access, and has excellent support in .NET :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Hello I did a stock control app in vb.net that uses an Access 2007 db. I connect using ACE.OLEDB.12.0 The Access db is located on the server, and the app is loaded on the workstations. They make a backup of the Access file every night. (Simply copy and paste to another location) There are about 10 users, reading and writing the same tables They have been running for a week so far, and up to now have already had one error where when trying to access data I got an exception " Unable to access the database" From comments on the net, Access is a really bad idea (Corruption, slow connections in multi user apps etc etc) They are a small company, so a licensed SQL2008 server is not practical financially. MySQL seems like a logical free option? My Questions: 1) As a temporary fix while migrating to a 'proper' db, I have read about creating a second access db, and linking all the tables in the 'back-end' db, to the 'front-end' db, so that if the first db gets corrupted, the 'back-end' db will remain in tact. Does this seem like a good idea? 2) Is MySQL the best choice for a db in this situation? 3) How easy it it to install and configure a MySQL database 4) Are there connection objects in .Net for MySQL (I saw an ODBC connector on the MySQL download page - is this the best way to connect?) 5) How do you back up a MySQL Db - do you need special agents? 6) Are there any other problems/differences I should be aware of when going the MySQL route? (I have noticed some differences in SQL syntax, parametrized queries etc, and also I use AutoNumber fields in Access to get unique Sales/Purchase Order Numbers I would be appreciative of any comments! Thanks in advance Richard
- No. SQL Server Express. 4) An ADO.net connector (not ODBC) for MySQL is downloadable; I use it to read MySQL databases created by others when I really need to.
-
Hello I did a stock control app in vb.net that uses an Access 2007 db. I connect using ACE.OLEDB.12.0 The Access db is located on the server, and the app is loaded on the workstations. They make a backup of the Access file every night. (Simply copy and paste to another location) There are about 10 users, reading and writing the same tables They have been running for a week so far, and up to now have already had one error where when trying to access data I got an exception " Unable to access the database" From comments on the net, Access is a really bad idea (Corruption, slow connections in multi user apps etc etc) They are a small company, so a licensed SQL2008 server is not practical financially. MySQL seems like a logical free option? My Questions: 1) As a temporary fix while migrating to a 'proper' db, I have read about creating a second access db, and linking all the tables in the 'back-end' db, to the 'front-end' db, so that if the first db gets corrupted, the 'back-end' db will remain in tact. Does this seem like a good idea? 2) Is MySQL the best choice for a db in this situation? 3) How easy it it to install and configure a MySQL database 4) Are there connection objects in .Net for MySQL (I saw an ODBC connector on the MySQL download page - is this the best way to connect?) 5) How do you back up a MySQL Db - do you need special agents? 6) Are there any other problems/differences I should be aware of when going the MySQL route? (I have noticed some differences in SQL syntax, parametrized queries etc, and also I use AutoNumber fields in Access to get unique Sales/Purchase Order Numbers I would be appreciative of any comments! Thanks in advance Richard
Just like the rest, Use SQL Server Express 2008 R2 with management tools. It has a 10GB limit per database. Other then taht it sure beats the hell out of Access. If you really wanna go for open source then I reccomend PostGreSql over MySQL. Still, I highly recommend SQL Server Express 2008R2 for the simple fact that your app is in .net.
All the best, Dan
-
Hello I did a stock control app in vb.net that uses an Access 2007 db. I connect using ACE.OLEDB.12.0 The Access db is located on the server, and the app is loaded on the workstations. They make a backup of the Access file every night. (Simply copy and paste to another location) There are about 10 users, reading and writing the same tables They have been running for a week so far, and up to now have already had one error where when trying to access data I got an exception " Unable to access the database" From comments on the net, Access is a really bad idea (Corruption, slow connections in multi user apps etc etc) They are a small company, so a licensed SQL2008 server is not practical financially. MySQL seems like a logical free option? My Questions: 1) As a temporary fix while migrating to a 'proper' db, I have read about creating a second access db, and linking all the tables in the 'back-end' db, to the 'front-end' db, so that if the first db gets corrupted, the 'back-end' db will remain in tact. Does this seem like a good idea? 2) Is MySQL the best choice for a db in this situation? 3) How easy it it to install and configure a MySQL database 4) Are there connection objects in .Net for MySQL (I saw an ODBC connector on the MySQL download page - is this the best way to connect?) 5) How do you back up a MySQL Db - do you need special agents? 6) Are there any other problems/differences I should be aware of when going the MySQL route? (I have noticed some differences in SQL syntax, parametrized queries etc, and also I use AutoNumber fields in Access to get unique Sales/Purchase Order Numbers I would be appreciative of any comments! Thanks in advance Richard
Long time since I used Access so this may not apply. When backups were done we always did a compact of the database (it's a menu choice IIRC). In a similar situation to what you describe never had any real problems with Access in 5 years or so. The front end was VB6 and the back end was just tables and queries on the server. So may be something in your set up you can tweak. However, I'd support the suggestion of looking at SQL Server Express. I've had a little experience with MySQL (on Linux) and found no problems with it. So I would not rule it out completely. I think however that SSE is probably a better bet. There are others like SQLite around(though I don't think that's appropriate for what you describe) so may be worth investigating.
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis The only valid measurement of code quality: WTFs/minute.