My Database is too big!!! Help.....

[Guest]

Hi,

I have created a database that records visists to stores and also the Excel
document/report completed at the visit is linked in an OLE field in each
record. After linking/attaching 400 of these my database started to creak
because it was 2GB. What is the best way to solve this problem? Does a
hyperlink field take up as much space or is it better to split my databases?

Any help would be much appreciated.

 

[Arvin Meyer [MVP]]

First make several copies of the database file, then build another database
and move about half the data to it, then compact both of them.

Now you can breathe.

You can link the Excel files by simply storing the path to them. Have a look
at my database DogMgr:

http://www.datastrat.com/Download/DocMgr_2K.zip

which will catalog the paths to Excel, Word, and text files. You can then
limit the files by either changing the code, running a query, and/or
deleting the data you don't want.

You can use a hyperlink as shown by using a label as a hyperlink and
grabbing the filepath with something simple like:

Me![txtPath] = Me![FilePath] & Me![FileName]
Me.lblPath.HyperlinkAddress = Me.txtPath

After doing this, you'll find that your database is probably less than
1/1000 the size.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[nor]

to : Arvin Meyer
i get invalid arrugument note when i use OLE in my db, i am use MS power
point in OLE , when i delete OlE in Design view i am still cannot add or edit
data
can you help me
Norizan

First make several copies of the database file, then build another database
and move about half the data to it, then compact both of them.

Now you can breathe.

You can link the Excel files by simply storing the path to them. Have a look
at my database DogMgr:

http://www.datastrat.com/Download/DocMgr_2K.zip

which will catalog the paths to Excel, Word, and text files. You can then
limit the files by either changing the code, running a query, and/or
deleting the data you don't want.

You can use a hyperlink as shown by using a label as a hyperlink and
grabbing the filepath with something simple like:

Me![txtPath] = Me![FilePath] & Me![FileName]
Me.lblPath.HyperlinkAddress = Me.txtPath

After doing this, you'll find that your database is probably less than
1/1000 the size.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have created a database that records visists to stores and also the Excel
document/report completed at the visit is linked in an OLE field in each
record. After linking/attaching 400 of these my database started to creak
because it was 2GB. What is the best way to solve this problem? Does a
hyperlink field take up as much space or is it better to split my databases?

Any help would be much appreciated.

 

[Arvin Meyer [MVP]]

Instead of using OLE bound objects, try linking to object in code, per:

http://www.datastrat.com/Download/DocMgr_2K.zip
and
http://www.datastrat.com/Download/Picture2K.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

to : Arvin Meyer
i get invalid arrugument note when i use OLE in my db, i am use MS power
point in OLE , when i delete OlE in Design view i am still cannot add or
edit
data
can you help me
Norizan

First make several copies of the database file, then build another
database
and move about half the data to it, then compact both of them.

Now you can breathe.

You can link the Excel files by simply storing the path to them. Have a
look
at my database DogMgr:

http://www.datastrat.com/Download/DocMgr_2K.zip

which will catalog the paths to Excel, Word, and text files. You can then
limit the files by either changing the code, running a query, and/or
deleting the data you don't want.

You can use a hyperlink as shown by using a label as a hyperlink and
grabbing the filepath with something simple like:

Me![txtPath] = Me![FilePath] & Me![FileName]
Me.lblPath.HyperlinkAddress = Me.txtPath

After doing this, you'll find that your database is probably less than
1/1000 the size.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have created a database that records visists to stores and also the Excel
document/report completed at the visit is linked in an OLE field in
each
record. After linking/attaching 400 of these my database started to
creak
because it was 2GB. What is the best way to solve this problem? Does a
hyperlink field take up as much space or is it better to split my databases?

Any help would be much appreciated.

 

[Gen]

You sample database had just the answers I was looking for - thanks! I copied
the code for modCallPath so that I can have a browse button on a form to
select a path and enter the file extension into a text box. Could you hlep me
with two modifications? The code doesn't make any sense to my amatuer eye!

1) I'd like the code to actually collect a document path, not just a file
path, so that I can link an excel file to each record in my DB.
2) If I press my browse button by accident, and then hit Cancel on the form
that pops up listing the path names, it closes and deletes the path that was
already entered in the text box. Can I change that so if I press cancel it
simply closes the form without changing anything?

I am using Access 2003. Thanks for the help.

Gen

First make several copies of the database file, then build another database
and move about half the data to it, then compact both of them.

Now you can breathe.

You can link the Excel files by simply storing the path to them. Have a look
at my database DogMgr:

http://www.datastrat.com/Download/DocMgr_2K.zip

which will catalog the paths to Excel, Word, and text files. You can then
limit the files by either changing the code, running a query, and/or
deleting the data you don't want.

You can use a hyperlink as shown by using a label as a hyperlink and
grabbing the filepath with something simple like:

Me![txtPath] = Me![FilePath] & Me![FileName]
Me.lblPath.HyperlinkAddress = Me.txtPath

After doing this, you'll find that your database is probably less than
1/1000 the size.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have created a database that records visists to stores and also the Excel
document/report completed at the visit is linked in an OLE field in each
record. After linking/attaching 400 of these my database started to creak
because it was 2GB. What is the best way to solve this problem? Does a
hyperlink field take up as much space or is it better to split my databases?

Any help would be much appreciated.