Time to move on from access?

[enrico1982]

I'm currently running a multi user system, with a VB.net front and an MS Access back (mdb on networked server).

I've been working on some new sections on the front end which required some fairly complex and recursive data queries. I'm running these in background workers, and open a new connection to the db for the bw to do its work. However running these seriously affect other data queries. For example:

User opens form1, which gets small amount of data in main thread, then starts a background worker going to get the large complex data. The form loads and the bw carries on working. Then the user goes to open form2. Form2 also runs a quick query in the main thread on startup. Now what happens is that Form2 takes ages to load, and will do so while the bw of form1 is running.

Is this an issue with Access and multiple connections or do I need to look at my code? Is time to move to MS/MY Sql?

Generally the application is pretty quick, but the number of users is not huge 10-15.

TIA.

[jmcilhinney]

Is time to move to MS/MY Sql?

The very first line of your post answers that question:

I'm currently running a multi user system

That's a big "yes" right there.

As for this issue, we can't really do anything but guess without seeing some code and, even then, it's hard to say when the code is multi-threaded, even when you're debugging the code, never mind just reading it. I'd say that you should switch databases first and see what happens. It should be a relatively simple migration as you can simply switch all the OleDb types to SqlClient and then make whatever small adjustments to the SQL code as are required, which may even be none.

[enrico1982]

Thanks JM, I think I will set a test conversion up and see what happens.

So is access just not very good at handling multiple connections? Or is there more to it than that?

[ClintEastwood]

MS Access is not recommended when you have in excess of 5 users. It is also limited in security and backend processing so you will gain greater advantage with speed etc using SQL Server or the like.