Printable View
Hi,
I am using VB6 which query (select) to FE Access via Jet4.0. The FE Access is linked to BE Access via the Linked Table Manager. However, the speed is very very slow. I have change the BE Access Subdatasheet Name to [None] for all tables and save the file with the Trak Name Auto-Correct Info [Off] as suggested by most users. However it does not improve the speed. Can anyone suggest any solution? Thanks.
Irwan
Welcome to VBForums :wave:
Can you show us the code you are using? (including the declarations of the database-related variables)
Is there a particular reason that you are using a Front End database, rather than connecting directly to the Back End one?
Hi,
Initially I did that with 5 users but always there is runtime error that occurs at random which I suspect due to table being use by 2 or more users at the same time. From the forums, it was suggested that FE/BE approach should be the way for multiple user therefore I made the change. From what I read, this method will ensure that collision from accessing the same table will be eliminated as there is the link manager that coordinates the data updates.
Another thing that was observes was that the data sometimes gets corrupted. When the data was saved, and when re-open, another data appear. However, this error is much less in occurrence as compared to the runtime.
After making the FE/BE change, the software creeps. What use to take a 1-2s now takes more than 10s.
The application software that was developed is pretty big, using MDI with 33 forms, about 50 adodc and 30 tables. Actually, if there is a way to go direct to BE and resolve the runtime issue, I am OK with that. BTW, I am using VB6 with Access 2003/2007.
Please advice would be the best option for me to ensure that the application runs smoothly.
Thank you.
Irwan
I've only heard of the front-end/back-end approach being recommended if the actual application is in Access too, and that is not the case here. The 'improvements' you have got are presumably due to the extra workload that you have imposed, which brings its own serious side-effects.
With the kind of situation you have, there are two things I would do to improve the situation drastically.
First of all I would replace the ADODC's (which are designed for single-user systems, and very limited programmer control) with ADO code.
This is likely to eliminate the error you were having (depending on what it was), and the magical appearance of data. There is also a decent chance of it improving the speed.
You can find an example that is reasonably similar to ADODC's in the ADO Tutorial from our Database Development FAQs/Tutorials (at the top of this forum)
Due to your program having 50 data controls it could take a while to implement, but due to the long-term gains I would definitely do it.
The other thing is to change the database system from Access to a server based one like SQL Server Express (which is free, and better than Access in several ways).
As Access is file-based, all of the work is done by the users computers, which leads to problems if there are multiple simultaneous users (particularly if there are 5+ at the same time, and/or the code is not designed for it). This leads to a high level of errors and corruption, requiring a regular Compact+Repair etc just to keep things running.
Server based systems do not have those issues because the work is done centrally. The speed also tends to be higher, because there is less data transferred across the network.
Hi si_the_geek,
Thank you very much for the advice. I will look into the ADO code and try to implement other databse such as SQL Server Express as you suggested.
2 questions:
1) Will mySQL work for me as well?
2) The PC that hold the database, must it be running on windows server or can a normal PC (XP OS) do the job?
Thanks.
Irwan
1) MySQL would work too, but you will get less help with that side of things from VB based sites (including this one), because the majority of VB users with database experience use SQL Server or Oracle.
2) You can use SQL Server Express on a server or desktop version of Windows - including XP and Vista.
The main concern will be ensuring that the computer is turned on when people use the database.