Results 1 to 6 of 6
  1. #1
    aat is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    30

    splitting tables to manage their size

    Hello,

    I started using Access on my own without following any training so to all "pure" access minds reading this please apologise the amateurism.

    My main table of the database, containing 800++ records, weights 2gb. I have attached many documents to many records hence the weight.

    One of the fields of the records is the city. Basically, I know for the moment it is London, but in the future it could some other cities.

    In order for me to manage a database that has a more manageable size, I was thinking of splitting the table (I read somewhere on this forum this was usually done to allow multiple users working on the same database).

    That is, the tables would have exactly the same structure, but one would be for all records with the criteria "London", and one sub-table per city. Each of the tables would be an external file saved somwhere safe, and when I open my access file, I have all the tables linked to my interface.

    I have already linked to my form a table of cities, so basically, whenever I want to add a new city, I update my table "cities" then it appears in the drop-down list of my form when I create or update my record.

    Now, if I split my main table into several sub-tables in order to contain the size of each, I would need a way to make Access populating my record in the relevant table as soon as I choose or update the "city" in my drop-down list. Of course I would also need a way to forbid the creation of a new city in my table "cities" if there is not a corresponding table already created.



    Is this possible?

    thanks a lot

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The splitting you've heard about involves splitting the application, not individual tables (a back end containing data files, a front end with everything else). Splitting a table is not typically done and is not advisable. Personally, I've never used the Attachment data type. The way to manage size IMHO is not to store the attachment itself, just the path to it. Your database could hold millions of records in one table if you only stored the path.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    aat is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    30
    Hi pbaldy, thanks for your comment.

    I've used systems before where paths to documents where stored. I'm very reluctant to do that because what happens then when you've got to re-arrange your directory, folders names etc? all paths wrong. I had many bugs with that before (not least when the operating system starts naming a directory t:\ after having called it s:\ for so long, and whithout giving any clue why - it happens).

    Having everything in a same file, like access does, is really cool and convenient, sad it can't take the volume.

    anyway if you have got any further suggestions I'm glad to read them.

    thanks

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    for me, maintainning attachment inside database is much more painful than maintainning a single path.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I can't imagine the OS changing the drive mapping on its own. I'd suspect a network guy changing a group policy that maps drives for users. I'd use UNC anyway ("\\ServerName\..." instead of "S:\...").

    Big changes can be made by running an update query on the data. For instance, moving everything to a new server can be handled by updating "\\ServerA\" to "\\ServerB\".

    In any case, if you want to stay with the attachment you are probably going to have to split data into different tables and databases (the entire db is limited to 2Gb, not just a table). If London alone is 2Gb you may have to split down farther than city. As you mention, you then face the problem of changing form/report recordsources to point to the appropriate table, and creating new tables as needed. Queries would have to be dynamic, and it would be difficult to query multiple cities, which would be simple with one table. It will not be an easy project.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By the way, I'd be curious if a Compact/Repair helped the size much, though if it's already 2Gb it may not work. Only try it on a copy.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. What is after Splitting!!
    By watzmann in forum Access
    Replies: 3
    Last Post: 09-07-2010, 07:31 PM
  2. Splitting Data
    By thesaguy in forum Access
    Replies: 4
    Last Post: 08-19-2010, 02:43 PM
  3. Splitting an Excel File into Multiple Access Tables on Import
    By TheWolfster in forum Import/Export Data
    Replies: 4
    Last Post: 04-29-2010, 04:52 AM
  4. Database Splitting
    By Matthieu in forum Access
    Replies: 8
    Last Post: 02-08-2010, 03:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums