Database over 2 gigs

I want to create a database with a large number of MS Word documents stored
as OLE objects but the database will be over 2 gigs. Is there any way to have
a database over 2 gigs in Access?
I was thinking of using multiple databases to store the files and then
bringing all the info together with a union query, however union does not
support OLE fields.
Any suggestions?

Bill,

Firstly, it's not a good idea to store BLOBS in Access. It bloats the file
size terribly, and renders it highly susceptible to corruptions. You're
better to store the binaries on a server somewhere, and only store their
paths in the database.

But if you must... You can stripe the database. That is, create multiple
database files, each containing a table that stores a separate subset
(category/classification) of binaries. As each database file can hold up to
2GB (well, close enough for rock-n-roll), and putting only one table in each
of these secondary database files, the combined database storage size is
limited only by the size of your disk.

You can use distributed queries to join the tables from all databases, to
allow searching.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyT...764559036.html


"Bill119" wrote in message
...
I want to create a database with a large number of MS Word documents stored
as OLE objects but the database will be over 2 gigs. Is there any way to
have
a database over 2 gigs in Access?
I was thinking of using multiple databases to store the files and then
bringing all the info together with a union query, however union does not
support OLE fields.
Any suggestions?

Oh, and I forgot to mention, you can link these tables in, to get "direct"
access to them.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyT...764559036.html


"Bill119" wrote in message
...
I want to create a database with a large number of MS Word documents stored
as OLE objects but the database will be over 2 gigs. Is there any way to
have
a database over 2 gigs in Access?
I was thinking of using multiple databases to store the files and then
bringing all the info together with a union query, however union does not
support OLE fields.
Any suggestions?

What you do is create the union query only of the primary
key of each table, and the left join that query to all
of the source tables.

You need a key that is unique across all files: seed
the primary key differently, or use a random autonumber,
with a second field in the key to identify the table.

Don't use GUID keys unless you are a masochist.

(Most people would put the files into a file folder, and
in the database only index the files.)

(david)


"Bill119" wrote in message
...
I want to create a database with a large number of MS Word documents stored
as OLE objects but the database will be over 2 gigs. Is there any way to
have
a database over 2 gigs in Access?
I was thinking of using multiple databases to store the files and then
bringing all the info together with a union query, however union does not
support OLE fields.
Any suggestions?

Thanks alot for both replies, that's very helpful.
The reason I want to store the files themselves in the database
is to increase access time. We have a very slow network at work
and it normally takes roughly 3-4seconds to load the documents. This
may not seem like much, but it has annoyed people, especially when
they have several docs they want to quickly skim over.
Someone suggested to me to use OLE objects to store the docs to
speed loading time. I tried that and somehow even over our slow network
access time is almost nill, you can skim through like 50 reports in a second.

We also want to create a version of the database that can be browsed
offline and it seemed easier to burn a single database file to DVD rather
than a folder with thousands of docs.

Anyway, that's why I want to use OLE. I'm about to attempt the multiple
database idea with the left-join query, hopefully that does it.
Thanks!

"david epsom dot com dot au" wrote:

What you do is create the union query only of the primary
key of each table, and the left join that query to all
of the source tables.

You need a key that is unique across all files: seed
the primary key differently, or use a random autonumber,
with a second field in the key to identify the table.

Don't use GUID keys unless you are a masochist.

(Most people would put the files into a file folder, and
in the database only index the files.)

(david)


"Bill119" wrote in message
...
I want to create a database with a large number of MS Word documents stored
as OLE objects but the database will be over 2 gigs. Is there any way to
have
a database over 2 gigs in Access?
I was thinking of using multiple databases to store the files and then
bringing all the info together with a union query, however union does not
support OLE fields.
Any suggestions?