Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can I speed up the response an Access Database on a network? 2
- Thread starter Krash878
- Start date
I can't answer your question exactly, but I believe there's a way to split the database up such that the tables are held on a central fileshare or server, and the queries, reports, macros, etc. are installed on each user's computer. That way they're just hitting the server for table info instead of depending on the server CPU to run everything.
Like I said, I forget what's it's called. Access XP (2002) has something called a Database Splitter Wizard. I've never tried it. I can't say what it's called on earlier versions.
Hope this helps! Good luck!
Q-
Like I said, I forget what's it's called. Access XP (2002) has something called a Database Splitter Wizard. I've never tried it. I can't say what it's called on earlier versions.
Hope this helps! Good luck!
Q-
-
2
- #4
As Q says, you definately want to split your data (tables) and leave that on the server. Put everything else (forms, queries, code etc) in another database and put that on the client machines. This way only the data and not the forms and code gets pulled across your network.
Use queries for your forms so that you're not loading the entire recordset, particularly when you have large tables. Limit all queries to only the fields that you are actually using.
Turn OFF the SubdataSheet Expanded property (A2K and beyond) of all tables. This 'feature' is a big performance hit.
If you have commonly used forms load them on startup and hide them, then on your menus, open them, but never close them, just hide them. This will make 'open' almost instantaneous (you'll need some VBA code work here). "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
Use queries for your forms so that you're not loading the entire recordset, particularly when you have large tables. Limit all queries to only the fields that you are actually using.
Turn OFF the SubdataSheet Expanded property (A2K and beyond) of all tables. This 'feature' is a big performance hit.
If you have commonly used forms load them on startup and hide them, then on your menus, open them, but never close them, just hide them. This will make 'open' almost instantaneous (you'll need some VBA code work here). "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
gusbrunston
Programmer
Hi.
My two cents, endorsing what's gone before and...
BTW, you never said how many users you have. Based on database size and number of users you might think about putting the back end on a SQL server.
1) Definitely split your database, front end, back end. Users have the front end, computer/server the back end. Backup your database on a zip drive, or in another folder, and use the splitter. It won't bite. Back up before you do anything...before breakfast, after lunch, while you're waxing the cat. There are two kinds of Access users--those that have lost their data and those that are going to lose their data. I have daily backups on zip disks going back 60 days.
2) Get the backend on a dedicated hard drive on the server or serving computer. All by it's lonesome. I've had people say that the cpu speed on the server doesn't matter, but I'd want my fastest cpu on the server. This is just a preference without much understanding of why, other than common sense.
3) Don't run something like Quick Books, or other cpu heavy programs on the serving computer.
4) The better the boxes (PIV's, 256mb ram is plenty) the better the service.
5) Hide your hard-to-load forms and you're a hero. Also, if you have a form with a tab control, with lot's of controls, you can divide the form into separate forms for each tab, and substitute command buttons for the tabs. Any form with lot's and lot's of controls probably should be divided up. I've recently discovered the joy of pop up and modal forms to deal with some of the stuff I used to stuff in the main form.
6) Be sure that it is the database and not the network that is slowing things up. Verify the boxes are hitting on 100 mbs on the network. If not, check the network cards, maybe the router. Cables can be a pain too. I'm told that the new cables are much more reliable ("twisted pair?" I dunno...)
7) Make sure your boxes are virus protected. I spent mucho dinero on memory sticks, and other hardware, and all the while the server had a virus that was eating cpu time. I fixed it at Symantec's web site and now all boxes are protected. To find out, go to Taskmaster, processes, and see what process may be running at more than "99". It might be a virus. Click Taskmaster's Performance tab. After the intial spike for entering, you should see low cpu usage, with occasional little blips. Your memory usage is good at around 100,000, at the bottom of the screen. Lot's of info in there to check. Make sure the boss sees you doing all this. Leave the performance tab open a while...it's really hi-tech looking.
8) Compact and repair the back end on a regular basis.
Gus Brunston
An old PICKer, using Access2000. I manage Suggestions welcome. padregus@attbi.com
My two cents, endorsing what's gone before and...
BTW, you never said how many users you have. Based on database size and number of users you might think about putting the back end on a SQL server.
1) Definitely split your database, front end, back end. Users have the front end, computer/server the back end. Backup your database on a zip drive, or in another folder, and use the splitter. It won't bite. Back up before you do anything...before breakfast, after lunch, while you're waxing the cat. There are two kinds of Access users--those that have lost their data and those that are going to lose their data. I have daily backups on zip disks going back 60 days.
2) Get the backend on a dedicated hard drive on the server or serving computer. All by it's lonesome. I've had people say that the cpu speed on the server doesn't matter, but I'd want my fastest cpu on the server. This is just a preference without much understanding of why, other than common sense.
3) Don't run something like Quick Books, or other cpu heavy programs on the serving computer.
4) The better the boxes (PIV's, 256mb ram is plenty) the better the service.
5) Hide your hard-to-load forms and you're a hero. Also, if you have a form with a tab control, with lot's of controls, you can divide the form into separate forms for each tab, and substitute command buttons for the tabs. Any form with lot's and lot's of controls probably should be divided up. I've recently discovered the joy of pop up and modal forms to deal with some of the stuff I used to stuff in the main form.
6) Be sure that it is the database and not the network that is slowing things up. Verify the boxes are hitting on 100 mbs on the network. If not, check the network cards, maybe the router. Cables can be a pain too. I'm told that the new cables are much more reliable ("twisted pair?" I dunno...)
7) Make sure your boxes are virus protected. I spent mucho dinero on memory sticks, and other hardware, and all the while the server had a virus that was eating cpu time. I fixed it at Symantec's web site and now all boxes are protected. To find out, go to Taskmaster, processes, and see what process may be running at more than "99". It might be a virus. Click Taskmaster's Performance tab. After the intial spike for entering, you should see low cpu usage, with occasional little blips. Your memory usage is good at around 100,000, at the bottom of the screen. Lot's of info in there to check. Make sure the boss sees you doing all this. Leave the performance tab open a while...it's really hi-tech looking.
8) Compact and repair the back end on a regular basis.
Gus Brunston
An old PICKer, using Access2000. I manage Suggestions welcome. padregus@attbi.com
gusbrunston
Programmer
Dear 930driver:
Thanks for the info about the SubdataSheet Expanded property. If I turn that off ("None" will I have to do any revision in modules, SQL statements, etc. Do you think?
Thanks again, very much. Gus Brunston An old PICKer, using Access2000. I manage Suggestions welcome. padregus@attbi.com
Thanks for the info about the SubdataSheet Expanded property. If I turn that off ("None"
will I have to do any revision in modules, SQL statements, etc. Do you think?Thanks again, very much. Gus Brunston
An old PICKer, using Access2000. I manage Suggestions welcome. padregus@attbi.comGus,
By the way, I mis-spoke. It is the Subdatasheet Name that we want to get rid of, not the Expanded.
My databases are heavy on code and sql statements. When I turned it off I had no troubles, and I got about a 20% improvement in overall speed, particularly loading of forms. I suspect that when access loads a recordset it loads (or at least looks at) the related tables. In fact when I examine the logs on our NT server I see file access events, one for each record in the table. But with SubdataSheet Name set there are two for every record! Here's a function that I wrote to turn it off for every table in the database.
Function TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String
Dim propType As Integer
Dim propVal As String
Dim strS As String
Dim i As Integer
Dim intChangedTables As Integer
Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[NONE]"
On Error Resume Next
For i = 0 To MyDB.TableDefs.Count - 1
If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
If MyDB.TableDefs(i).Properties(propName).Value <> propVal Then
MyDB.TableDefs(i).Properties(propName).Value = propVal
intChangedTables = intChangedTables + 1
End If
If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
MyProperty.Type = propType
MyProperty.Value = propVal
MyDB.TableDefs(i).Properties.Append MyProperty
Else
If Err.Number <> 0 Then
MsgBox "Error: " & Err.Number & " on Table " _
& MyDB.TableDefs(i).Name & "."
MyDB.Close
Exit Function
End If
End If
End If
Next i
MsgBox "The " & propName & _
" value for all non-system tables has been updated to " & propVal & "."
MyDB.Close
End Function
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
By the way, I mis-spoke. It is the Subdatasheet Name that we want to get rid of, not the Expanded.
My databases are heavy on code and sql statements. When I turned it off I had no troubles, and I got about a 20% improvement in overall speed, particularly loading of forms. I suspect that when access loads a recordset it loads (or at least looks at) the related tables. In fact when I examine the logs on our NT server I see file access events, one for each record in the table. But with SubdataSheet Name set there are two for every record! Here's a function that I wrote to turn it off for every table in the database.
Function TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String
Dim propType As Integer
Dim propVal As String
Dim strS As String
Dim i As Integer
Dim intChangedTables As Integer
Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[NONE]"
On Error Resume Next
For i = 0 To MyDB.TableDefs.Count - 1
If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
If MyDB.TableDefs(i).Properties(propName).Value <> propVal Then
MyDB.TableDefs(i).Properties(propName).Value = propVal
intChangedTables = intChangedTables + 1
End If
If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
MyProperty.Type = propType
MyProperty.Value = propVal
MyDB.TableDefs(i).Properties.Append MyProperty
Else
If Err.Number <> 0 Then
MsgBox "Error: " & Err.Number & " on Table " _
& MyDB.TableDefs(i).Name & "."
MyDB.Close
Exit Function
End If
End If
End If
Next i
MsgBox "The " & propName & _
" value for all non-system tables has been updated to " & propVal & "."
MyDB.Close
End Function
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
- Thread starter
- #8
Another Possible Help...
I have run into this problem before. Where forms especially, take a very long time to load.
Check to see if your application is saved in a fully compiled state.
to do this go to the code screen and in the debug menu look to see if the compile and save option is available.
If it is then your app is not saved in a compiled state, which means it has to re-compile your code each time it opens.... Choose compile and save all modules, then open your forms, you'll notice quite a difference.
I have run into this problem before. Where forms especially, take a very long time to load.
Check to see if your application is saved in a fully compiled state.
to do this go to the code screen and in the debug menu look to see if the compile and save option is available.
If it is then your app is not saved in a compiled state, which means it has to re-compile your code each time it opens.... Choose compile and save all modules, then open your forms, you'll notice quite a difference.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question