Network Speed.... Can you test it

[homer2002]

Hi


I'm having problems with a split database.
The tables are linked using Link Tables and it is starting to run a bit slow.
I thought it would be handy if you could test the speed of the network to see.
Maybe somthing like a Ping where you can test how long it takes to run a set of instructions and time it .

I dunno really. I just thought it would be nice.
Besides wouldnt it just be nice to push a button and say 'See Told you it was the network'

 

[FoFa]

Actually you could create a timing test in Access, but let me say I think this path is a bad one to take. Let me list why.
MS Access is more resource intensive than say something like SqlServer. With the FE/BE design, access does all the processing on the PC running the FE, rather than on the BE. With SqlServer as an example, you can run a query on the server, and only return the data across the network you want (sure some times it is all the data). Access will bring all the data across the network, then apply the criteria and whittle it down. So you have more data flowing on the network. Is that a network issue, depends who you talk too. Some I.T. depts. limit MSAccess because of that rather than spend the money on network upgrades. One other thing you can't test (seperated from the network), is if the server the BE is loaded on is the problem also, so is it the server or is it the network. A file server runnning at it's limit (or the network cards limit) can also prove to be a bottle neck. One way to test to verify the infrastructure is part of the problem, is to load both BE and FE on one PC and run the app. This has always proved faster in my expreience, but is not practical from a user standpoint. You can also load the FE on all the users PC's and improve performance (you don't have to load the application across the network), but does nothing for the amount of data (not application) flowing.
Just my 2 cents worth.

 

[Mile-O]

Actually you could create a timing test in Access, but let me say I think this path is a bad one to take.

I know nothing about servers and SQL Server and all that stuff so I'll just pass comment on making a timer in Access.

Don't use a form's Timer property. There's an API function in my Computer Class that gives you the time in milliseconds since Windows was started. There's a post about making a timer on the thread.

 

[homer2002]

Cheers for the info, a handly insight into one of my many grey areas.

At the moment I use a BE on a shared drive on a server.
On the server I also keep a hidden copy of the FE (I rename it to .DAT to confuse people more )

On top of the FE and BE I also code an installer that copies the .DAT files to C:\WINNT\TEMP and executes from there so all the users do is run the 80k installer (makes it very easy to email around without maxing peoples inboxes)
One other handy thing the installer allows is, when you copy over the .DAT file with a new version, everyone gets the update automaticly. A handy way to make sure people are using the correct version

 

[FoFa]

Interesting take, and not a bad idea I must add. What do you use to do this copy function (what you are calling the installer)?

 

[homer2002]

I Write a little stand alone database (cause I cant use vb and obviously I have access )

All I do is have one form on startup to run the code and then it quits.

Make a form

Add a label named lblstatus
Add a button named cmdclose (and make it invisible)
(The testpath and ssleep functions can be found in these forums just in case you dont know)
___________________________________________________
Option Compare Database
Option Explicit
Const NameOfDatabase = "MyDatabaseName.MDE"
Const CheckAccess = "\\MyServerAtWork\ASharedPartition"
Const SourceFile = ""\\MyServerAtWork\ASharedPartition\MyBackends\MyDatabase\mbr.dat"

Private Sub Form_Load()
lblStatus.Caption = "Check Access Rights"
Select Case TestPath(CheckAccess)
Case True
lblStatus.Caption = "Access ok...Installing Database"
Me.Repaint
sSleep 1000
FileCopy SourceFile, "C:\temp\" & NameOfDatabase
lblStatus.Caption = "Executing Database"
Me.Repaint
sSleep 1000
Shell "C:\program files\microsoft office\office\msaccess.exe C:\TEMP\" & NameOfDatabase, vbNormalFocus
Application.Quit
Case False
lblStatus.Caption = "Incorrect Access. Please see your Administrator guy/girl/horse"
cmdclose.Visible = True
End Select
End If
End Sub
___________________________________________________

 

[Kevin_S]

Besides wouldnt it just be nice to push a button and say 'See Told you it was the network'

But are you so sure your app isn't the one causing network problems...

The best way to combat split db slowdown is to make sure your app is optimised (no forms based on tables, non-streamlined code, etc...) by having forms based on querydefs with strict where clauses to make sure your only passing 1 or a handful of records over the network, streamlined code (using calls instead of reproducing the same code over and over/ using recordsets instead of DLookUps and what not), etc...

Also having a robust BE like SQL Server, Oracle, DB2 etc... will result in HUGE performance improvements on networked Access apps with MS Access BE's. If your not ready to make the financial leap to a large RDBMS try testing on something intermediary like MySQL... Its more robust then MS Access, you can still use your Access FE with it, and its FREE!

Should help with your speed issue...

Also - kudos on the installer setup - sounds like it works a treat!

HTH,
Kev