Question 20838 in Mysql Question posted in
The official MySQL documentation can be found here.

Access Database using linked tables maximum size – Mysql

I created an *.mdb file (Access 2002 – 2003 file format) and I added in Access (from External Data tab – Excel) an ODBC DataBase
(that I configured in ODBC DataSource Administrator 32-bit to point to a MySQL database server) using "Link to the datasource by creating a linked table".

I know that an Access file can be max. 2 GB.

Question:

  1. In this situation with the linked tables, can I bypass the Access maximum threshold?
  2. What will be the max. threshold, that from Access or from MySQL server?

Thanks

Tags:

3

Answers


  1. 0

    Since the linked data is not stored in Access, this limit does not apply.

    There are, however, other limits as listed in Access specifications. E.g. number of fields in a table (255) or the number of columns and bytes per rows returned in a query.

    Login or Signup to reply.
  2. 0

    The maximum file size of an Access (*.mdb) file is 2 GB. Linking tables to a MySQL database does not change this limit. The maximum size of a MySQL database depends on its configuration, but a linked table in Access will still be limited to 2 GB. If you’re dealing with a large amount of data, it’s recommended to split the database into smaller databases or consider using a more robust database management system like Microsoft SQL Server or MySQL.

    Login or Signup to reply.
  3. 0

    As noted, since Access is not the database anymore, then such limits don’t exist.

    EVEN if you bind the form directly to a linked table, the limit does not exist.

    So, here is a form based on a linked table, and the linked table has 10 million rows, (and SQL server shows that the ONE table is more then 2 gigs in size.

    Note how the speed is also instant, and the form is bound DIRECTLY to that linked table with 10 million rows. There are no views, no stored procedures or anything. Just a simple linked table is used here.

    Note how the form with the "filter" opens instant, and does so without any special code, but a "common" used "where" clause of the open form command.

    the code behind the button is this:

    Private Sub Command5_Click()

   DoCmd.OpenForm "frmBigHistory", , , "id = " & Text2
      
   
End Sub

    Now, if I open that form bound to the table without a filter?

    It loads instant, but if I decide to "move to" the last reocrd, then a BIG dealy occures, but the form (in about 25 seconds) does and will load the whole data set.

    so, here is this in effect
    (the form starts out with the WHOLE 10 million data set loaded, and I navigate back to 1st record, then last.

    Then I show the same form (with 10 million rows) being launched with a filter – and Access then ONLY pulls the ONE record from SQL server with above code example.

    enter image description here

    So, in this example, the database is more then 2 gigs (it is about 7 gigs in size).

    And that one table is over 2 gigs in size.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search