tabel capasity

iraidotel@yahoo.com

Registered User.
Local time
Today, 10:17
Joined
Dec 17, 2014
Messages
36
Dear all,
How limits the size of the capacity of a table in ms access if a table contains thousands of data and each data contains the attachment.
if there is no limit how many gigabytes?
and what can we setting to enlarge its capacity
How to do,

Thank you

Ira
 
also a row in a table is limited to an absolute maximum of 4000 bytes,

therefore at least 250 rows per Mb, 250,000 records per Gb. In practice many more.
 
and what can we setting to enlarge its capacity

Well, technically you can't expand the capacity of an access database beyond 2 Gb per DB file whether it is ACCDB or MDB if you are using a 32-bit version of Office. I searched but might not have asked the right quest regarding database sizes under 64-bit Access. The link from Marlan is valid but points to a 32-bit version of Office.

The good news is that even for 32-bit Office you can split databases if you need to do so, and if your "split" is a split inter-table (not intra-table). No one table will ever be able to exceed 2 Gb total data, but by judicious splitting, you can break the 2 Gb total size barrier.

First (simplest) split - take all of your tables that need to be shared among users and put them in a single back-end (BE) file. Then all else (query definitions, forms, reports, macros, modules) stays in the front-end (FE) file. If you use temporary tables that apply only to one user, they can (in fact, SHOULD) stay in the FE file, though you should empty them before allowing the FE to exit. You can also set auto-compaction on exit for the FE file to keep its size down.

Now, if you are keeping images, workbooks, text files, and other documents inside the database using OLE methods, consider that you might instead wish to keep the "other documents" part as separate files as a sub-folder of the folder holding the BE file. You can easily generate numeric file names to keep each file unique and if you also use "relative" file parsing, you can do something so simple as looking up the folder in which a particular table is located (HINT: Pick a table name - it is located in a folder that you can find by CurrentDB.TableDefs("name of BE table").Connect and parsing out the name of the BE file. What is left is the folder, and you can then tack on the sub-folder name and file name to refer to the external file.)

E.g. if the connect string of a particular BE database table is "I:\SHARE$\XDB\XYZ_BE.MDB" then you can to an InStrRev to find the right-most "\" marker. If you take that position as "PX" then Left$(connectstring, PX) is - for this example - I:\SHARE$\XDB" as the partial path. Let's say that you had an image called IMG_001_005.JPG in the \IMAGES folder under the \XDB folder. You could just add the two strings together to produce the concatenated string "I:\SHARE$\XDB\IMAGES\IMG_001_005.JPB" - which would be a fully qualified file specification that lets you keep a short string rather than a huge image file in the database.

The third thing you can do is split the BE file into two files of approximately the same size (each < 2Gb) and do some linked-table management to see both BE files. No one table could cross from one BE file to the other one, but if you had two large tables, you could keep one large table in each of two BE files.

There is also the possibility that if you HAD to stay with Access and HAD to have one table that exceeded 2 Gb, you could do a UNION query where you split that one table into two parts (perhaps based on age of the entry) and then used a UNION query to recover the contents of both tables as though they were a single table.

Finally, if you reach the limit for simultaneous open database files or cannot manage that many files, consider making the BE database not a separate Access file but instead use an SQL-based server. Literally, you could the SQL Server, but you can also use MySQL to manage larger BE files. Or if you have an ORACLE database with the OLDB modules, you could use that, too.
 
Hi,

I have fine experience splitting data to a few BEs and linking to them once at a time, this structure suites my clients' needs, even though offering BI is an issue...
Searching this topic, i came across this post, he dose not recommend Processing 2GB of data, even from multiple DBs, in a single Access file.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom