Speed increase

[Ben]

Hi,

Have a mdb database on server with BE and FE, used by approx 40 users and
even though, the size is not huge, it works slower than it should.

Would an upgrade to SQL increase the speed? Or what are my options. We just
bought a new server which increased the speed a bit but not enough.

Biggest problem is the impatience of the users, as usual. If they have to
wait 3 seconds they click 123 times and then the FE closes...

The programming is not the best, so perhaps that could also improve the speed.
Any thoughts?

Thanks

 

[Douglas J. Steele]

Unless properly done, changing to SQL Server can actually make things
slower.

Take a look at the Microsoft Access Performance FAQ Tony Toews has at
http://www.granite.ab.ca/access/performancefaq.htm

Make sure you have appropriate indexes on your tables.

 

[John Spencer MVP]

Also make sure you are not loading all the records at one time, but are
limiting the number of records that the user is working with.

I have a table with more than 2.3 million records. If I try to let the user
access all those records at one time then performance is abysmal. On the
other hand if I load 200 or those records using a query to limit the records
to the set the user needs to work on right then performance is blazingly fast.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Hi,

Have a mdb database on server with BE and FE, used by approx 40 users and
even though, the size is not huge, it works slower than it should.

Would an upgrade to SQL increase the speed? Or what are my options. We just
bought a new server which increased the speed a bit but not enough.

Biggest problem is the impatience of the users, as usual. If they have to
wait 3 seconds they click 123 times and then the FE closes...

The programming is not the best, so perhaps that could also improve the speed.
Any thoughts?

Thanks

See http://www.granite.ab.ca/access - Tony has a good performance FAQ and a
good overview of the pluses and minuses of SQL.

 

[Jeff Boyce]

I'll extend John S's comments a bit further...

I limit the number of records users are working with at any one time to ...
one! That way, Access only needs to find/load/process a single record at a
time.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Toews [MVP]]

Biggest problem is the impatience of the users, as usual. If they have to
wait 3 seconds they click 123 times and then the FE closes...

Users are always impatient. <smile>

Is this on startup or when going from menu to form or previewing
reports?

Tony

 

[Ben]

Hi All,

Thanks for your answers.

In general, loading is rather slow but the biggest problem is when I am
linking a record to another record. e.g.
In a Person record, I have subforms with an overview on which projects this
person participated in. When adding a new project, which creates a new record
on the table that links the person to the project, the system is very slow
and crashes when impatient clicking. I believe this is because it needs to go
through too much info because it opens a subform, refreshes, adds some info
to the person record, and a few other small events that should run smooth.

The limitation query could be a good idea, if possible. Can I do that by
inserting something like "Current ID" in the SQL or how can I do this best?

Thanks again

B

 

[Ben]

The biggest problem arises with this event:

Private Sub Kommandoknapp74_Click()
If Me.Kommandoknapp74.Caption = "New Call" Then
Me.Underobjekt73.Visible = True
Me.Underobjekt73.Form.DataEntry = True
Me.Kommandoknapp74.Caption = "Save"
Else
If ([Underobjekt73].Form![Project ID] = Null) Then
Me.Underobjekt73.Visible = False
Me.Refresh
Me.Kommandoknapp74.Caption = "New Call"
Else

If ([Underobjekt73].Form![Answer ID]) = "13" Then
Me.Action = "3"
Me.Actionforproject = [Underobjekt73].Form![Project ID]
Me.Answer = [Underobjekt73].Form![Answer ID]
Me.Actionby = CurrentUser() & " " & Now()
Me.Underobjekt73.Visible = False
Me.Refresh
Me.Kommandoknapp74.Caption = "New Call"
Else

If ([Underobjekt73].Form![Answer ID]) = "12" Then
Me.Action = "2"
Me.Actionforproject = [Underobjekt73].Form![Project ID]
Me.Answer = [Underobjekt73].Form![Answer ID]
Me.Actionby = CurrentUser() & " " & Now()
Me.Underobjekt73.Visible = False
Me.Refresh
Me.Kommandoknapp74.Caption = "New Call"
Else
If ([Underobjekt73].Form![Answer ID]) = "6" Then
Me.Action = "1"
Me.Actionforproject = [Underobjekt73].Form![Project ID]
Me.Answer = [Underobjekt73].Form![Answer ID]
Me.Actionby = CurrentUser() & " " & Now()
Me.Underobjekt73.Visible = False
Me.Note = "Doctor Retired/Deceased (Previous contact information:
tel. " & Me.Phonenumber & " -fax. " & Me.Faxnumber & " -Email. " &
Me.E_mail_address
Me.Faxnumber = ""
Me.Phonenumber = ""
Me.E_mail_address = ""
Me.[Dokter info ID] = "1"
Me.Refresh
Me.Kommandoknapp74.Caption = "New Call"
Else
If ([Underobjekt73].Form![Answer ID]) = "14" Then
Me.Action = "1"
Me.Actionforproject = [Underobjekt73].Form![Project ID]
Me.Answer = [Underobjekt73].Form![Answer ID]
Me.Actionby = CurrentUser() & " " & Now()
Me.Underobjekt73.Visible = False
Me.Note = "Doctor Moved (Previous address: " & Me.Street & " - " &
Me.[Postal Code + City] & " - tel. " & Me.Phonenumber & " -fax. " &
Me.Faxnumber & " -Email. " & Me.E_mail_address
Me.Faxnumber = ""
Me.Phonenumber = ""

Me.Refresh

Me.Kommandoknapp74.Caption = "New Call"
Else

Me.Action = "1"
Me.Actionforproject = [Underobjekt73].Form![Project ID]
Me.Answer = [Underobjekt73].Form![Answer ID]
Me.Actionby = CurrentUser() & " " & Now()
Me.Underobjekt73.Visible = False

Me.Refresh
Me.Kommandoknapp74.Caption = "New Call"

End If
End If
End If
End If
End If
End If
End Sub


This event will open a new Add record subform when clicking a button. When
clicking again, it will refresh and update the Action fields in the current
record. There are a few conditions that define the info put in Action etc…
It all works great in a small database without too many records but in the
large active one,… it crashes and takes about 10-15 seconds…

I know, not a disaster but I’m sure this could go faster.
PS: I started off wrong and never gave clear names to the buttons/forms etc…
I know which is what but I know it doesn’t look very clear like this.

Thanks

 

[Ben]

Thanks for your answers.

The limitation query could be a good idea, if possible. Can I do that by
inserting something like "Current ID" in the SQL or how can I do this best?

The biggest problem arises with this very important event:

Private Sub Kommandoknapp83_Click()
If Me.Kommandoknapp83.Caption = "New Booking" Then
Me.SubformNewBookings1.Visible = True
Me.SubformNewBookings1.Form.DataEntry = True
Me.Kommandoknapp83.Caption = "Save"
Else
Me.Action = "4"
Me.Actionforproject = [SubformNewBookings1].Form![Project ID]
Me.Answer = ""
Me.Actionby = CurrentUser() & Now()
Me.SubformNewBookings1.Visible = False

If Me.[Dokter info ID] = "0" Then
Me.[Dokter info ID] = "7"
Else
If Me.[Dokter info ID] = "1" Then
Me.[Dokter info ID] = "7"
End If
End If

Me.Refresh
Me.Kommandoknapp83.Caption = "New Booking"
End If
End Sub

This event will open a new Add record subform when clicking a button. When
clicking again, it will refresh and update the Action fields in the current
record. There are a few conditions that define the info put in Action etc…
It all works great in a small database without too many records but in the
large active one,… it crashes and takes about 10-15 seconds…

I know, not a disaster but I’m sure this could go faster.
PS: I started off wrong and never gave clear names to the buttons/forms etc…
I know which is what but I know it doesn’t look very clear like this.

Could the me.refresh be the problem?

Thanks

Ben

 

[±«ÈÙÈÙ]

±«ÈÙÈÙÐÂÎÅ×éÐÅÏ¢·¢²¼

Thanks for your answers.

The limitation query could be a good idea, if possible. Can I do that by
inserting something like "Current ID" in the SQL or how can I do this
best?

The biggest problem arises with this very important event:

Private Sub Kommandoknapp83_Click()
If Me.Kommandoknapp83.Caption = "New Booking" Then
Me.SubformNewBookings1.Visible = True
Me.SubformNewBookings1.Form.DataEntry = True
Me.Kommandoknapp83.Caption = "Save"
Else
Me.Action = "4"
Me.Actionforproject = [SubformNewBookings1].Form![Project ID]
Me.Answer = ""
Me.Actionby = CurrentUser() & Now()
Me.SubformNewBookings1.Visible = False

If Me.[Dokter info ID] = "0" Then
Me.[Dokter info ID] = "7"
Else
If Me.[Dokter info ID] = "1" Then
Me.[Dokter info ID] = "7"
End If
End If

Me.Refresh
Me.Kommandoknapp83.Caption = "New Booking"
End If
End Sub

This event will open a new Add record subform when clicking a button. When
clicking again, it will refresh and update the Action fields in the
current
record. There are a few conditions that define the info put in Action
etc¡­
It all works great in a small database without too many records but in the
large active one,¡­ it crashes and takes about 10-15 seconds¡­

I know, not a disaster but I¡¯m sure this could go faster.
PS: I started off wrong and never gave clear names to the buttons/forms
etc¡­
I know which is what but I know it doesn¡¯t look very clear like this.

Could the me.refresh be the problem?

Thanks

Ben

I'll extend John S's comments a bit further...

I limit the number of records users are working with at any one time to
...
one! That way, Access only needs to find/load/process a single record at
a
time.

Regards

Jeff Boyce
Microsoft Office/Access MVP