advice on storing archives

[vivian]

I've created a simple database in which we enter order id (automatic), date (automatic), customer, po#, shipping date, and ship via.

then we scan the order (which may be from 2-20 pages long) into the computer so that we do not need to keep actual paper (trying to save on office space).

these files then get inserted the scanned object subform (containing the fields scan number, object, and order id).

I'm realizing that because we'll be scanning quite a few orders that this may take up quite a bit of space, i'm wondering if linking the objects rather than saving them within the database will help.

the only problem with this is that if we move the files containing the actual scanned images, that the whole system will get screwed up.

Does anyone have any ideas?

Thanks in advance

 

[FoFa]

Create a PARM table. In it store the path to your image files. load that into a global variable when the app starts, use it. So basically all you have in your link, is a file name. If the path changes to the image files, just change the PARM table.
I typically setup my PARM table with a KEY, DESCRIPTION, Stringvar, Datevar, intvar columns. Ref any value by the KEY.

Key = IMGPATH
StringVar = \\myservername\imagefiles\ourimages\

Global sImagePath = dlookup("StringVar","ParmTable","KEY = 'IMGPATH')

Just as one way.

 

[vivian]

sorry, i've never heard of a parm table b4.

key=imgpath, is this just the filename itself? i understand the stringvar holds the rest of the path.

i'm not sure how the
Global sImagePath = dlookup("StringVar","ParmTable","KEY = 'IMGPATH')
works, is this a field name as well?

thanks for your ideas and help

 

[vivian]

I have created a table that has the fields: imgpath (primary key, text format), and stringvar (text format)

I cant seem to get the following to work as a calculated field in my form though, I keep getting the #name error:

dlookup("StringVar","ParmTable","KEY = 'IMGPATH')

maybe i'm doing something completely wrong?


Thanx

 

[vivian]

is there a way to put two fields together so that they create a link to a file.

for example if i put in one field: c:/orders/
and in another field: order 1.pdf

can i create a field c:/orders/order 1.pdf ?

it would work sort of like concatenate in excel formulas

 

[vivian]

not sure if there is something like concatenate in access

 

[ScottGem]

Embedding images in an Access database will cause database bloat and is not recommended. The recommendation is to store the PATH to the file then link to it. You don't indicate what types of files you are scanning to as that could meake a differece.

You might also want to consider using software like Paperport. Paperport (from Scansoft) is a document/image management tool. You can use it to do your scanning, save the images and store keywords about them (like order id, date, customer, po#, shipping date, and ship via.). You can also create full text indexes to search.

 

[vivian]

I'm scanning them and saving them as .pdf files. I will not be embedding the files as i realize this will cause some major issues with the number of files required.

What I'm trying to do is just store the path to the file in two separate fields and then with the form creating a third field which will concatenate the other two fields to create a link to the file. For example:

field 1: c:/order images/
field 2: order1.pdf
field 3 (fields 1 and 2 put together in a hyperlink): c:/order images/order1.pdf


Thanks for the ideas guys!

 

[ScottGem]

First I would NOT recommend using spaces in your folder names. This WILL cause problems.

Second, I would not store the base path with the record unless you plan on using lots of different paths (not a good idea). Instead I would set the first part of the path as a constant in the database. Just store the actually file name. This way it makes it easy to change the base path.

Finally I would not use a mapped drive, I would use UNC coding (\\servername\sharename\foldername\fielname) so you don't have to worry about different users having different mappings.

 

[vivian]

Thats what I was thinking, actually. I was just looking for an idea on how to put the two together to form a hyperlink.

But... I can't seem to figure out how to put the two together.

I was just thinking by explaining that I'm putting two fields together to create a hyperlink, that'd I'd then be able to figure out the rest.

 

[ScottGem]

Lookup FollowHyperlink in Access VBA help.

 

[vivian]

sorry I'm still fairly new to all of this vba stuff. i've found this code and I'd like to adapt it to my needs, but I can't make it work, it's giving errors:

Private Sub Command0_Click()
FollowHyperlink "http://www.ncbi.nlm.nih.gov:80/" & _
Me.YourTextBoxNameHere & "=Abstract"
End Sub

adapted to:

Private Sub text30_Click()
FollowHyperlink "C:\WINDOWS\Desktop\" & _
Me.Text30 & "=Abstract"
End Sub


when I mouse over the me.text30 in the code I get the correct doc2.doc file that i'm testing with but when i run the code i get the run time error 432, the file name or class name not found during automation operations. all my file names are spelled correctly. what could be this problem?

 

[ScottGem]

Try it as just:

Private Sub text30_Click()
FollowHyperlink "C:\WINDOWS\Desktop\" & Me.Text30

End Sub

 

[vivian]

thanks sooooo much Scott, that works like a charm.

sorry for the hasstle i'm still fairly new at the vba stuff

 

[ScottGem]

No hassle. Glad to assist