If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Encrypt AccesS File? | milest | General Discussion | 2 | February 9th, 2005 07:58 PM |
MS Access unable create MDE Database....please help | Ismail baba | General Discussion | 1 | November 17th, 2004 05:38 PM |
Database periodically needs rebuild and locks users out | spectrum | General Discussion | 2 | July 13th, 2004 06:24 PM |
Mutliple Tables lookup? | Westley | Database Design | 4 | June 15th, 2004 01:07 AM |
Please Help, The database is in an unexpected state; Microsoft Access can't open it. | Gary A. Hollenbeck | New Users | 2 | May 20th, 2004 05:42 PM |