access database question

[wigs]

got a database set up in Access. It is a purchase order program. The forms in it use lots of queries and tables. Not to mention the fact that we are using it across a lan. It is horribly slow. Is Acess visual basic any different that the database? Is there anything I can do to speed things up?

[ThePoor]

if your database on the LAN and everyone try to access it at the same time, it would be slow...

[sdkfz]

Got some idea that may help:

Tables should be deep and not wide, meaning that a table should be very specific (name and contact info, orders etc.) Basically there should be no duplicate info in multiple tables except for a primary key to link the tables (SSN-cust ID # etc)

Try using look up tables for fixed data sets (Gender - male or female or a state code list for example) and put these look up tables in the front end (see below for front end idea)

Set up the relationships between tables in the relationship window, use referential integrity where possible. Also use cascade delete to avoid orphan sub record sets

If queries are too slow try starting with a make table query for the initail rough data sort and then use this temp table for subsequenbt refinements to get the answer you want

Access is not very good at releasing memory/space, as you work with a database you will see it grow and grow at a rate that does not account for new data. First try a repair and then a compact of the database, these functions are in the tools menu and require exclusive log on. This is a maintanence item, meaning you have to keep doing it on a schedule. You may find that your 100 meg DB shrinks down to 10 megs after compaction, keep an eye on the size and when it hits say 50 megs compact again. While you CAN compact and repair into the exisiting databse I'd suggest letting it compact into a new one- just in case you have a power loss etc during the process so you are not corrupting the exisiting DB, you can always rename the new and delete the old after you know the compact worked.

If this does not yeild enough speed and the database is mature (meaning you are not weaking it all the time try splitting the database into a front and back end. The back end would have nothing but the data core tables. Any lookup/reporting/ temporary tables remain in the front end. Then give every user the front end on their local machine. This reduces the network traffic down a lot and can gain some more speed.

Of course the speed of the local machine and the RAM available is another limitier, Access is greedy and giving everyone more RAM if they are on the low end would help too.

As to you original question - admittedly I am not well versed in the VB portion so I will leave that to others.

[doctorgonzo]

How are you using it across the LAN? Is it split up into front end/back end, or do you have one database file on a network server and people are opening that file? If it is the latter, switch it to the former. Opening an Access database from a network share is painfully slow and there is no way around that. In fact, in the database I have created I put code in that will not allow people to open the database from the network. It's just too slow.

Have you normalized your tables? If you don't know what that means, you probably haven't done it. Check out this article (or do a Google search on "database normal forms"). The foundation of a database is the table structure. If that isn't optimized, you can forget about everything else. I learned this the hard way.

Don't offer the user more data than they need to do their work. For example, if you have a form for entering new orders, don't use the orders table as the source for the form, because then the form will load all of the data in that table. Not only is that slow, there's a great data integrity risk. Set it up so that the form only load the one record that needs editing. The way I do this is to create a search form that lets users search for information in the database. When they find what they want, they click a button to load just that record into another form, where they can edit.

[wigs]

it's one big database that is shared from a server. We have two buildings connected via a dry loop and a network extender. If you are at the other building that does not house the server with the database, it is a bear. So more than one person is sharing the same database file. I assume that an Access book will have info about the front end/back end you guys mentioned?

[doctorgonzo]

Yes, a good Access book would include that information (among other books, I have the Access 2000 Developer's Handbook two-volume set). Basically, you would put the back end containing only the data tables on the server. Users would have front ends on their own PCs, which would be used to access the data. This is a bit faster because only data has to be sent over the networik, instead of everything (forms, reports, you name it).

However, it is still more important to normalize the tables and use good design practices to ensure that as little data as possible is being sent over the network. Using a split architecture will help some, but it won't come anywhere close to instantly speeding things up to the point where lag is gone. It takes a lot of thinking and smart design to get the best out of Access. Unfortunately, Access just doesn't work well in an environment with multiple users in multiple areas. It can work, don't get me wrong, but it takes a lot of huffing and puffing to get it to work acceptably.

[sdkfz]

Setting up a back is pretty easy, First you need to be doing this outside of production time, you can not do this with people in the database.

I'd do this with a complete separate non production copy first, just in case......

First you copy the entire database. One copy will be the front end and the other the back. In the front end you delete all of the tables that have customer infomation, you can leave the source tables for drop downs (for example a table called gender that supplies a drop down box in a form with the choice of male or female).

In the back end database delete all of the forms, quieries, reports etc and the tables that you are leaving in the front end. Now all you should have left is the customer information tables in the back end.

Back in the front end you use File -Get External Data- Link Tables. You point to the back end copy and you select all of the tables that are left and link them to the front end. They will reappear in the front end's table view window except that there will be an arrow in front, this indicates a linked table.

That is it, I'd make sure that you have the back end database in the final network location because if you move the back end to another location Access will NOT be able to see them and you will have to delete and re link the tables. Access uses absolute referencing so all users have to have the same network name and letter for the back end location for this to work. In other words if one location mapped to the drive and called it the V drive while in another location they mapped it as the S drive, both can not use it. You may need IT to create a new partition that is unique to all user locations.

As the doctor mentioned, you should have the front end loaded locally on all the users machines, this will be speedier than having both the front and beck on a network, the problem with this is that if you make a design change to the front end then you have to get the users to replace their front end with the new one. A good way to force this is to change the back end name with the new version, this way old database copies point to a bad location and will not function. This will require you to delete and relink all the tables.(of course this does not always go over well with the end users.....)

Getting more performance out of the database requires the good set up. I learned a lot from setting up a database that now is way too big and needs to be converted to SQL (I'll be opening a new thread to ask about this as I know no SQL). Unfortunatley there are some features in my database that are from my early learning experience so it will require some work to make it a better planned DB.

Good luck....

[sdkfz]

I forgot that there is also a tool in access for making a back end. It is called database splitter, you might want to hve a look at that option too.