Make a splitted database, to host few files (like 3000)

[brazoayeye]

Hello, i'm new in this forum and in access too so please be patient

I'm trying to set up a database using access 2010 but i can't reach a satisfactory solution.
The main goal is to overcome the limitation of 2GB/file splitting the database in different tables.
The database has to be used by different people (1->10) and will be stored in a shared folder, reading this page i decided to split the database.

I have thousand of "articles" and every article can have more than 1 photo.

A simple table with the "attachment" field does its job but it's limited (2GB/file)

I split the table in 2 part, first with article data and attachment_id and the second with attachment_id and the attachment. It works but obviously attachment table is still too big to keep all files.

I failed trying to set up a db with a table ("articles") and a lot of attachments table ("att1", "att2"). Access should automatically choose from where it should load data. First try was with two fields in the "articles" table (att_file, integer; att_id, integer) and all macros tried to access table "att"&[att_file] but i can't reach a solution.
An easier way is probabilly to split "attx" table every 100 articles so article (0..99) will go to att0 table, (100-199) will go to att1, etc.


In this direction it's not necessary the field att_id in the "articles" table because i could set the first field of attx as number (not automatic numbering) copying the same id of the articles table. I don't know if this could decrease the performance of the DB.

What i need is not the implementation of my ideas, but maybe a simplier way to reach the same reults. If you agree with my idea can you explane me how to set up the DB? Basically i prefer not to dive in an hardcore programming, specially if there are graphical way to achieve the same results. So please, if it's possible, i prefear to use macros instead of scripts and the mouse instead of the keyboard.

Thanks

[ranman256]

I had a table with a million records and still did not reach 2 Gig.
I dont know what you are storing but you may have to have 1 table for each letter of the alphabet.

[brazoayeye]

I dont know what you are storing

every article can have more than 1 photo

attachment table is still too big

but you may have to have 1 table for each letter of the alphabet.

Articles are named by a numerical id ( xx.xxxxx.xxx ) so it's better to use a not-name-related logic to split files in tables.

Can someone technically help me to reach a solution that split attachments in different tables and that it's transparent to users?

Thanks

[ranman256]

splitting the tables out is invisible to users. Remove and make single db with a single table for tblArticles.
link it in, the users wont feel a thing.

[brazoayeye]

I can't entirely understand what you want to say:

splitting the tables out is invisible to users

Sure, but i means that i want an invisible scheme that fit more table in the same field.

Remove and make single db with a single table for tblArticles.

What whould i remove? I already have a single tbl for articles, i only have attachment table splitted.

link it in, the users wont feel a thing.

what and where should i link?

Thanks

[uaguy3005]

Are you storing images in the database? I'm not sure what the circumstances are dealing with where this DB is located, but I would suggest saving the photos on a network location and just storing the file location in the DB, not the actual file.

[ssanfu]

Totally agree with uaguy3005. Store the images in a folder, not in Access.

See this Post:
https://www.accessforums.net/forms/c...tos-55375.html