How Access exports tables from a server

[gg80]

I am using Access 2003. I have a split data base with tables in a server. The system is sometimes annoyingly slow. I have googled the problem and implemented a lot of the recommendations, with small improvement. (With tables not split, system is fast). I have been told that the problem is Access.



According to my source, the criteria in a query does not restrict the number of records and fields exported from the server. Rather, the whole table is exported, then the query selects. So, as the tables get bigger, the whole system will be even slower. Supposedly, some other db programs don't do it this way.

Is this true? Hate to learn another db.

Thanks for any help or direction.

[June7]

The issue might not be entirely with Access. I have a split db with backend on file server and most of the time queries are plenty fast enough. I do have a couple of complex forms and reports that are a little slow to load but still acceptable.

How big is the backend? Mine is 155MB.

[ItsMe]

I am not sure what you mean by export. I believe building a query for a recordset and linking tables are two different things. Access allows you to link tables. All of the fields will be connected and available to the FE when you open the file.

If you like, you could use ODBC to connect. I think it is more convenient to link the entire table. There are ways to dynamically link a table. Maybe your FE does not need all of the tables all of the time. You could link to them when the demand arises.

Just throwing ideas out there. Also, place the FE file on the machine that is opening it. Storing it locally appears to improve performance.

[gg80]

The issue might not be entirely with Access. I have a split db with backend on file server and most of the time queries are plenty fast enough. I do have a couple of complex forms and reports that are a little slow to load but still acceptable.

How big is the backend? Mine is 155MB.

Mine is small by comparison-45mb. The times are acceptable, but annoying. My concern is that as the data grows, it might go beyond annoying. Thanks for input.

[NTC]

You state that un split (or both front and back on the same PC) that it is not slow. Therefore saying that it is Access caused is illogical. It is clearly the network/server or some combination of them.

Have been in the finger pointing scenario many times. Do side by side time tests. Begin with both on same PC (note RAM size). Then progressively move the Back End to various locations such as other PCs, then to Server. In the server put it at the top of the Root, then move progressively down into folder layers.

Don't get me wrong - a bad design with lots of search/sort and no indexing and bog down in multi user. If you do your side by side test during an off time where there is no other users - and you see some configs where it is fine and other figs that are really slow - - then you have proof to give to your network guys....sometimes a server NIC goes into insane chatter mode, things like that. Such things are not noticed when just storing Word/Excel docs as sometimes Access is the only true interactive application.....

[gg80]

Somehow, my original reply is not displaying, .

First, thanks for quick reply.

I assume FE means Front End of the file, the part on the user's computer that links to the tables in the server.

By export I meant that, when a query is opened, the server transmits the whole table to the FE., not just the data required by the query criteria. Ditto when opening a form or report with filter onOpen. I was wondering if this true and if that is the reason for the annoying slowness when using a split db with a server.

I don't know about ODBC, but will look up.

[gg80]

Thanks much for taking time to reply.-

The other traffic on the system is not interactive in same way as Access, mostly just call up the data once, do the calcs, then store. I have experimented with my own computers, using one as "server" and system is fast. I will try your suggestions ASAP.

[ssanfu]

I am using Access 2003. I have a split data base with tables in a server.

I am a little confused.
When you say "server", do you mean an Access BE (file) is on a Windows Server computer or the data is in tables in a database server ie SQL Server/MySQL?

[gg80]

I seem to be confusing everyone because I just don't use the right terminology. What I am trying to describe is a system with five computers on the shop floor. Separate from the computer is what we are calling the Server-a special computer for securely storing data, for shared programs etc.. All computers have access to the Server through the local network. My Access program is installed on each computer where users interact with forms etc.. The tables associated with the Access program file are stored in the Server and simply linked to the program in each computer.

[NTC]

Again - refer to my first post. If your database is not split - - it must be split for a correct multi user installation. Bing that. If you are indeed not split that can definitely explain the slowness; plus more importantly you are guaranteed to corrupt your database file - and so it must be split immediately.

The question you raise about the whole table being sent for a query is true, yet irrelevant. Todays LAN infrastructure carries 100x more volume for a typical YouTube video than any Access data table. It is not an issue.

I will add that you state that you are using Access 2003 but not the OS. I find that old XP machines are slow today as MS has over the years bulked up that OS due to security issues beyond the RAM size of older PCs. Not to say that XP doesn't work - but that it is noticeably slow. That is just an aside. Speed is relative. If it is fast enough when all on one single PC - then it should not slow down significantly when via a LAN because LANs are very high bandwidth.

[NTC]

.....and there is no need for SQL Server and definitely not MySQL which would only make things worse.......

[gg80]

The program is definitely split. I have 5 machines linked to tables in the server-as I understand it,- his is a splitdb Is there some other issue that I am missing.?

As you suspected, the computers on the floor are old, with XP. However, we tried using a newer laptop with windows7 and Access2007 and times for opening forms are about same. I did one other experiment at my place. I dug up the worse "computer" in the world, an Acer Aspire with W7 home version (I was planning to dump it so it wouldn't bother anyone else). Relatively slow, when it works. I used it as the BE for the program with my w7 laptop as FE. Speed is fine I guess this pins it down to the server and its communication system?

I very much appreciate the time you are taking with this. I have never gotten so much input for a problem.