Hyperlink Problems (Automatic updates)

[Fingerman32]

I'm self-taught and my computer has come close to extinction on more than one
occasion. I'm creating an access table and trying to add hyperlinks to image
files in one of the fields. The problem is that I have multiple image files
(about 23,000) with different extensions. Instead of a 1234.jpg or .tif, they
have been supplied to us with a .001, .002, etc. extension format (depending
on how many pages are in each record). I will be getting updated images
every month (about 500 or so) and will need to update these links
efficiently. I have tried creating macros, reports, forms, etc. under the
assumption that I would be able to link them back to my original table.

1) I have been unsuccessful and would like to know if there in an easier way
of updating the hyperlinks each time the main table is updated other than
manually browsing for the new file?

2) How do I make one hypterlink access all the files with different
extensions withouth having to do a separate query or macro? Will a pop-up
window work and if so, how?

3) Last problem, for some reason I'm getting import errors when importing an
excel spreadsheet into access in the "date" field. I've downloaded an add-in
that accepts dates earlier than 1900, but access is still giving me import
errors. Do I have to create a new spreadsheet with my text document before
access will recognise the date column?

Please help.

Scott

 

[TC]

Re. 1) and 2), I think it would help if you tried to describe what you
are trying to do, /without/ using any technical Access terms like
table, hyperlink, form, and so on. The problem with your questions as
they currently stand, is that you have made some assumptions about how
you should do it "under the hood". For example, you've made an
assumption that you'll need to use hyperlinks. It's hard for us to
validate those assumptions, or to answer your questions, unless you
take a step backwards, and describe what you are trying to do from an
"end user" viewpoint.

For example: "Every month we get 500 image files. Each file corresponds
to a person in our database. We need to somehow associate each image
file with the relevant person." etc. etc. Notice that my example /does
not/ make any assumptions about how to actually do it "under the hood".

Re. 3), what is the error message?

HTH,
TC [MVP Access]

 

[Fingerman32]

TC,

Guess I should have read through the questions a little better. I see I
wasn't clear on what the problems were. I already had questions 1 and 2
answered. Number 3 though is still baffling me.

The error I'm getting is "Type Conversion Failure" under "Recording Date"
field. Do I need to create a text document from the spreadsheet first and
re-import the text document into excel, then import spreadsheet into access?

Scott

 

[TC]

That message suggests that the dates in your excel sheet are having
trouble being recognized by Access during the import. Are they all in
the same format? What is that format? If they are all in an unambiguous
format, like 01-Jan-2006, it should work ok. If they aren't, it might
not.

HTH,
TC [MVP Access]

 

[Fingerman32]

TC,

I'm sorry, I should have stated what I had already tried. That didn't work
either. I've tried changing the date format in every option that is available
and I still get the same import errors.

Any more suggestions

Scott
Senior CAD Manager

 

[TC]

Again: are all the dates in the same format, and if so, what is that
format?

TC [MVP Access]

 

[Fingerman32]

Yes, they are all in the same format, and I've tried importing them into
access with each different format.

 

[Fingerman32]

TC, Let me clarify. When I try to change the format, the format says that the
dates are in jan 14, 1889, but it still displays them in the cell as
1/14/1889 whereas 1/14/1999 displays jan 14, 1999

Yes, they are all in the same format, and I've tried importing them into
access with each different format.

Again: are all the dates in the same format, and if so, what is that
format?

TC [MVP Access]

 

[Fingerman32]

Solution:

Create a column in excel, but 1800's records out and past into new column.
Import spreadsheet into access. Cut 1800's records in colum you created, then
paste them into access "date" column.

Scott

TC, Let me clarify. When I try to change the format, the format says that the
dates are in jan 14, 1889, but it still displays them in the cell as
1/14/1889 whereas 1/14/1999 displays jan 14, 1999

Yes, they are all in the same format, and I've tried importing them into
access with each different format.

Again: are all the dates in the same format, and if so, what is that
format?

TC [MVP Access]

 

[TC]

Be aware that "Date" is a reserved word in Access. Look up "reserved
words" in F1 help.

HTH,
TC [MVP Access]