Search code examples
mysqlms-accessodbc

Access Database using linked tables maximum size

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

Solution

  • 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.