My aplication goes very slow in windows server 2008

In my company we use a quite old program made in vb6 with access 2000 database.
We use dao 3.6 to work with the database
Our server was windows 2000 and the computers were xp until some months ago when we changed to windows server 2008 64 and windows 7.
Now everything goes very slow, the program needs 30 sec. to open when it was less than 10 sec. before.
I don´t know if the problem is we have changed the operative systems in the server and the other computers or we are using WIFI in most of the computers or we should change code in the program ...
We work 8 persons with this program, I know the solution would be to change from access to sql but this would be very complex as we use dao and we have many code to change.
The database size is now 75 mb

I would apreciate any help to get the program goes faster.

these are some parts of the code of the program:


Set db = Workspaces(0).OpenDatabase(BASEpath$ & "SECRE.MDB")
Set ds = db.OpenRecordset("SELECT * FROM clients", dbOpenDynaset)


If ds.RecordCount <> 0 Then
ds.MoveFirst
Do Until ds.EOF
if not isnull(ds!code) then
combo1.additem ds!name
end if
ds.MoveNext
Loop
End If
ds.close



thanks
LVL 10
aplimediaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BRetiredCommented:
No code changes to upsize the db to use sql as the backend.
WiFi will be unreliable and slow. This is likely the major issue id you were wired before.
Still using Access 2000? Move to 2003 if you can.

Chris B
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Does the VB6 app use bound controls (i.e. the ADO control) or are you using "client/server" techniques to manage this? If you're using client/server techniques then switching to SQL Server will definitely provide benefits. If you're not, then you'll probably realize some gains but not as significant. As with most things, the only way to tell is to test-test-test.

aplimediaAuthor Commented:
I am using "client/server" techniques to manage the database as I Explained in the code example. I use DAO , not ADO.
To go from one record to other I use code like, ds.movenext, ds.movelast, ds.findfirst ....
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't know that there is any way to get this to run faster, especially considering that you're doing this over WiFi. Jet is a file-server type database, and therefore your client (i.e your VB6 program) must manipulate all data, so if your program asks for all Customers with a last name of Smith, Jet just sends over the Customers table and lets your app do the rest. Compare that with a true server, which would actually parse that table and send only those record back that matched the criteria.

aplimediaAuthor Commented:
Is there an easy way to change the database to sql server but using only to store the information but keeping the structure in access  ? I mean, can I export to sql my access database but work (without programing changes) in access working with virtual tables which are in sql?
Is this faster?

Thanks
Chris BRetiredCommented:
The upsize wizard does just that - http://support.microsoft.com/kb/325017

Chris B
aplimediaAuthor Commented:
I will try to use sql because this is what I think best solution.
I changed to access 2003 and it goes bether but not enought.
I have changed some computers which were using wifi and now they use cable, it goes really faster but the best solution will be to migrate to sql.

I will make this change in 3-4 weeks and will explain my experience
jlucking99Commented:
For 25ish users and a 75MB database MS Access is fine!
Option A:
1. run the be & fe MDBs local on the server (I assume it's broken up into a Frontend / Backend)
2. is the performance ok?
3. if so then it's the network / connection between the desktop PC's and the Server

Option B:
1. Install both the MS Access frontend and backend on a single desktop
2 test the performance, is it ok?
3. If so then something's going on with the server...what else is running on the server?

I would start here and determine if running locally on a server and a pc works.

Don't waste your time upgrading the code to ADO / upsizing to SQL Server just yet...besides for the size of database you are dealing with it shouldn't be necessary!!!

I would next look at the references that your MS Access VBA code uses...the new dll's might be slowing down your old DAO code...the only way to figure it out is to try the old Dlls...
aplimediaAuthor Commented:
thanks, I will try your recomendations and will be back soon.
MascoloCommented:
I have exactly the same problem as you have described! I see this trail has gone cold a bit. I have spent a lot of time investigating and have discounted network issues. I run my app on identical Dell Optiplex 380s but one has Windows 7 and the other XP. and data is local and the XP is 40% quicker!
aplimediaAuthor Commented:
I have made some changes in the code and now the program goes a litle faster (not enought)
-I have created some memory tables and load some access tables in memory, with this starting the program is more slowly but it goes faster.
-I have deleted all the select * and have put only the fields I am using: select, surname, firstname...
-I have indexed the tables by most of the fields
-I have changed thinks like "open * from clients where code = 23" it opens the table many times by "open * from clients" "find first code = 23" open the table once ...

Anyway, if somebody knows other things it will be apreciated...
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To be fair, "fast enough" is a matter of user impressions. In other words, what is "fast enough" for one set of users might not be fast enough for another set of users.

It would seem that your next step would be to move to SQL Server, as has been suggested several times in this very old thread. You can convert your Access data to SQL Server using several different methods; the SQL Server Migration Assistanct is a very good tool for this:

http://www.microsoft.com/sqlserver/2005/en/us/migration.aspx

Make sure to get the correct one for your server and such.

After that, you'd begin moving processing to the server. This will require you to move away from bound controls (I'm assuming you're using bound controls, given your coment on "ds.MoveNext" and such) and begin to work with class objects and such to handle your data. Views (more or less a "query") on the server can be very performant, if they suit your needs, and you can use Stored Procedures to work with the data.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MascoloCommented:
I have resolved my issue and it turns out to not be Access / VB6 but MSFlexGrid and the fact it is really slow redrawing in Vista / Win 7. There is an autoredraw property which if set to false whilst populating the grid increases the speed dramatically.
Something worth pointing out with Access in a multi user environment is the MaxLocksPerFile setting in Jet4.0 registry, for any networked systems we set this to 30000 (Decimal and don't ask how we got to that figure either), there are quite a few settings in there and many are undocumented and not worth the risk but this setting makes a real dfference.
Finally I agree and disagree with the SQL Server solution, it may be that you have many users, in which case SQL is definately better but at 75mb (after compression?) is not necessarily that big. I guess what I'm saying is SQL could well be the answer but don't rush into it.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The issue is not really the size of the database but rather the number of concurrent users. Access seems to peter out somewhere around 20 users or so (assuming the application is properly built, etc) whereas SQL Server can support a LOT more than that.

If Access is sufficient for your needs then by all means stick with it, and I certainly agree about not rushing into something without fully researching it first.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.