locked
SQL Server back end file size limit? RRS feed

  • Question

  • Greetings - - I wanted to clarify the scenario of an SQL Server back end becoming larger that the 2G file size limit of Access' front end file.

    There are no pass thru queries nor SQL Server views - the SQL Server is just holding tables.  

    Does / will the front lock up and if not, why not .....  ?

    It is not clear to me as to how much file size transfers into the front and why.  For instance we do have 1 table that itself is going to be approaching 2G in the not too distant future.

    No one, other than the developer - opens a table itself - via the front end.  All the users are working in 'accounts' that they've selected which are all form/report objects bound to a query so that they are only consuming a fraction of the overall back end data set. (most users are with runtime).

    Part 2: if Access is going to be the choke point - what Windows development environment would next be used that has a higher file size capability?  (staying Windows - not web....)

    Thursday, April 26, 2018 12:25 PM

Answers

  • Hi,

    I think, in theory, you should be able to use Access to link to an airline reservation system as long as you're not asking for "too much" data than it can handle at a time. The file size limit is only affected if you're pulling data into local tables. If you merely pull data into a Recordset object, then the size is only limited by the amount of computer memory you have available.

    Just my 2 cents...

    Thursday, April 26, 2018 2:58 PM

All replies

  • SQL Server's limitations are far, far superior to that of Access, see: https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-2017 for all the details.  As long as the Access file itself isn't approaching the 2GB range there is little to concern yourself with.

    Regardless, when I see dbs, any db, in such size ranges, I always like to review why it is so large in the first place.  In some cases, it is all good, but in many cases it is due to poor design, duplication of data, insertion of attachments, ... so this may be an opportunity in disguise to improve the overall design.

    "There are no pass thru queries nor SQL Server views - the SQL Server is just holding tables."

    Why not?  You are always best to do as much of the processing on the server rather than pulling the information over the network and doing the processing locally.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, April 26, 2018 12:50 PM
  • No attachments.  It is large because it has been running a long time and has accumulated a lot of data.  Thought is being given to carving out to an archive copy and removing data out of the production database.

    No pass thru queries because it began as pure Access front / back - - and then the tables moved to an SQL Server ~5 years ago.....and has never been a compelling cause to rewrite things.

    But my question is somewhat theoretical & generic.  I am assuming you cannot front end with Access to a mainframe airline reservation system (to use an extreme example)..... that the data volume flows from back to front and will exceed the 2G limit.  So am trying to get an outline understanding of the back end file size impact / limit relative to the Access front end limit.....

    Thursday, April 26, 2018 1:10 PM
  • Hi,

    I think, in theory, you should be able to use Access to link to an airline reservation system as long as you're not asking for "too much" data than it can handle at a time. The file size limit is only affected if you're pulling data into local tables. If you merely pull data into a Recordset object, then the size is only limited by the amount of computer memory you have available.

    Just my 2 cents...

    Thursday, April 26, 2018 2:58 PM
  • If you are talking about SQL Server Express (free version) the limit is 4 GB the last time I checked. If you mean the full version then there is no limit. I have a few that far exceed 2 GB. Once is 36 GB. You do have to consider when you pull records if you need to limit how many you display only because of performance issues.

    The linked tables themselves do not add much weight to the Access front end. It's mainly the other objects like forms and reports that take up the space.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Thursday, April 26, 2018 2:59 PM
  • ah so dbguy - that is very interesting in that the record set object size limit is a point of hardware rather than the 2G Access file size.

    along these same lines then; it seemed like once it was that the entire table flowed into the front recordset object if it was a regular Access query - - but only the query's resulting record set flowed into the front if it was a pass thru query....  essentially the whole reason to make pass thru queries by limiting the load going to the front...... but then I thought I remember seeing an explanation by someone (Kallai?) stating this was not correct..... so now I'm not sure on this point......

    Thursday, April 26, 2018 4:43 PM
  • Hi,

    With an Access front end connected to a SQL Server back end, I could be mistaken but, I think the "flow" goes something like this:

    Let's say you have a table in the back end with 100 records and let's also say we want to execute a query with the following SQL statement:

    SELECT * FROM TableName WHERE SomeField=SomeCriteria

    Assuming the final result of the above query is only 50 records returned, then when you execute the above query in Access, then it's possible all 100 records will flow through to the front end first before Access can discard the 50 records you don't need. Whereas, if you execute the same query as a passthru, then SQL Server will discard the other 50 records and only "flow" the 50 records needed to the front end.

    I could be wrong in the above assumption, but I'm sure someone else can clarify it for us.

    Just my 2 cents...

    Thursday, April 26, 2018 4:52 PM
  • that has been my understanding as well - - - - I should have saved the link to the explanation that this actually was not so.  But it was several years past.  They put a sniffer on the line and only the query results were being sent.  But that begs the question of why the pass thru ability is still out there......It might have been in the era of Access Web forms.....  It was by one of the MVP guys but I don't trust my memory on this......
    Thursday, April 26, 2018 5:08 PM
  • Hi,

    If that's the case, then it's good to know. However, there's still a need for pass thru queries in cases where you need to execute a stored procedure, which is not available/possible from the front end.

    Cheers!

    Thursday, April 26, 2018 5:16 PM
  • The size of the back end database is almost irrelevant. Unless you are storing this data in Access as well, such as in temporary tables, then there shouldn't be an issue. In most instances the data retrieved from SQL Server would be in-memory and not physically stored in the database. I am assuming here that you are either linking to tables in SQL Server or querying the data through DAO or ADO (or both).

    If you want to use a different development platform for the UI, then Visual Studio .NET would be the next step.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, April 26, 2018 5:37 PM
  • yes thanks PPCIV...... we were taking it a step further and trying to figure out how much memory was being affected....... depending on whether the entire table flows into the front memory or whether just the query result records flow into the front memory

    I muddied the waters with an old memory of a Q/A here that stated that the whole table was an urban myth....even though that was always my believe....

    the memory affect is a key issue when you have 1M+ records in a table and some of the fields are long text / memo fields....

    Thursday, April 26, 2018 5:43 PM
  • Querying 2GB of data at one time is rather unusual. Typically you only bring back a small portion of that amount if it is being displayed in a UI. Retrieving that much data at one time would be horribly inefficient and time consuming.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, April 26, 2018 6:05 PM
  • Actually, the limit for sql express was bumped up some years ago.

    It is now 10 gigs - so it quite large.

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Friday, April 27, 2018 9:25 PM
  • AK - does a regular Access select query to the linked SQL Server table - move the entire table contents to the front end?   or does it behave the same as a pass thru query and move only the query results contents to the front end?

    Friday, April 27, 2018 10:08 PM
  • Actually, the limit for sql express was bumped up some years ago.

    It is now 10 gigs - so it quite large.

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Thanks, Albert. I need to start confirming my "facts" before I post.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, April 30, 2018 1:53 PM
  • AK - does a regular Access select query to the linked SQL Server table - move the entire table contents to the front end?   or does it behave the same as a pass thru query and move only the query results contents to the front end?

    A query on a linked table just fills a buffer with the result set. Same as a pass-through.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, April 30, 2018 1:54 PM