1

I have a Microsoft Access .accdb database on a company server. If someone opens the database over the network, and runs a query, where does the query run? Does it:

  • run on the server (as it should, and as I thought it did), and only the results are passed over to the client through the slow network connection
  • or run on the client, which means the full 1.5 GB database is loaded over the network to the client's machine, where the query runs, and produces the result

If it is the latter (which would be truly horrible and baffling), is there a way around this? The weak link is always the network, can I have queries run at the server somehow?

(Reason for asking is the database is unbelievably slow when used over network.)

vacip
  • 5,246
  • 2
  • 26
  • 54

2 Answers2

3

The query is processed on the client, but that does not mean that the entire 1.5 GB database needs to be pulled over the network before a particular query can be processed. Even a given table will not necessarily be retrieved in its entirety if the query can use indexes to determine the relevant rows in that table.

For more information, see the answers to the related questions:

ODBC access over network to *.mdb

C# program querying an Access database in a network folder takes longer than querying a local copy

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thanks, that is a bit better. I'll re-check my indexes, and try to improve the preformance that way. – vacip Nov 17 '15 at 20:53
0

It is the latter, the 1.5 GB database is loaded over the network

The "server" in your case is a server only in the sense that it serves the file, it is not a database engine.

You're in a bad spot:

The good thing about access is that it's easy to create forms and reports and things by people who are not developers. The bad is everything else about it. Particularly 2 things:

  • People wind up using it for small projects that grow and grow and grow, and wind up in your shoes.
  • It sucks for multiple users, and it really sucks over a network when it gets big

I always convert them to a web-based app with SQL server or something, but I'm a developer. That costs money to do, but that's what happens when you use a tool that does not scale.

mikeb
  • 10,578
  • 7
  • 62
  • 120
  • Oh damn. :( Thanks for the quick answer. Is there a way around this? – vacip Nov 17 '15 at 19:34
  • Or should I tell them to use a real database? They might have an MSSQL database, but the IT is very secretive about it... – vacip Nov 17 '15 at 19:34
  • See my edits, it's not trivial to move to SQL from Access if they use forms and such – mikeb Nov 17 '15 at 19:40
  • 1
    ( cc: @vacip ) - *"the 1.5 GB database is loaded over the network"* - That is a common misconception which is almost never true. The only time that Access would have to transfer the complete 1.5 GB database is if it had to to a complete table scan of every table in the database. For more details see my other answer [here](http://stackoverflow.com/a/31998888/2144390). – Gord Thompson Nov 17 '15 at 19:56
  • Well, I'm actually a developer, and I was specifically asked for an Access database. This is actually a relatively small DB with only 2 users. They told me the company did not want to buy or install a real DBMS. Funny thing is, when development was 80% done, an IT guy told me they already had an MSSQL server, but nobody knows about it. I love working with over-sized companies... Anyway, thanks for all the help, guys! – vacip Nov 17 '15 at 20:57