linking massive amounts of files

[qurn]

Hi, it's me again, the guy who works with the Amusement ride industry. I'm trying to set up a way to ogranize all our saftey bulletins that we get.

I've already looked into indexing but the major problem is that the user will need to be able to search on multiple criteria (for example the Tilt A Whirl was made by three different manufacturers). Also I can't download that nifty google tool bar because we're running win 2000 without service pac 3.

Access seemed like a great alernative, but I have about 300 or so files I need to link. Is there a faster way of linking these files than going through and doing them one by one?

I have a pre-made index the guy before me made in exel. Most all the files are in PDF format if it helps at all.
Thanks.

 

[The_Doc_Man]

If you intended to SEARCH the files, .PDF hurts, not helps. If you already know what is in the files, you might be luckier.

The catch is that if it is not invented by Microsoft, you are not guaranteed to be able to read it. Microsoft suffers from a really bad case of NIH syndrome (Not Invented Here). Unless you have the full version of Adobe and a .DLL file that you can load to the Access "references" list, the .PDF will be a "black box" to you. The Adobe .DLL file, if you had the right one, would "expose" the contents of the .PDF so that Access could examine whatever the Component Object Model for .PDF contains. In that case, Access could do the search for you.

(Hope you understand "References" in Access context. If you don't, search this forum before asking for more details. "References" is a common question topic.)

The pre-made index you mentioned will undoubtedly help, since Excel is easy enough to import with minimal massaging. At worst you would have to import all fields as text and go back to retrofit the things you need. But Access handles that sort of thing O.K.

Regarding multiple makers of a product, your solution might be that you need compound keys or (more likely) a couple of junction tables. For instance, suppose that Acme Co. makes a Tilt-a-Whirl and Dizzy Inc. makes a Tilt-a-Whirl. I take it from your comments that Acme might issue a safety bulletin but Dizzy either would not, or would issue a different one. (I can see it now... Acme issues a bulletin that says coyotes can be thrown violently from a Tilt-a-Whirl when in the presence of any roadrunners.)

Back to business... Your key is then to know BOTH maker and ride name in order to define the bulletins. If you are not familiar with normalization, it is time to learn.

I might consider at least six tables to do this right and leave open the possibility of future enhancements.

tblBulletin
BulletinTitle, text
BullID, could be autonumber, is PRIME KEY of this table
other info on bulletins

tblBullKwd
Keyword, text
BullID, foreign key to tblBulletin

You would search tblBullKwd for given keywords in order to find bulletins using those keywords. Your "predefined index" might help here... or not. Depends on what happens to be in it.

tblMaker
MakerName, text
MakerID, could be autonumber, is PRIME KEY of this table
other info on ride makers

tblRide
RideName, text
RideID, could be autonumber, is PRIME KEY of this table
other ride info

tblWhoMakes
RideID, foreign key to tblRide
MakerID, foreign key to tblMaker

An entry in tblWhoMakes means that the referenced ride is made by the referenced maker.

tblMakeRideBull
RideID, foreign key to tblRide
MakeID, foreign key to tblMaker
BullID, foreign key to tblBulletin

An entry in this table means that the named bulletin applies to the named ride from the named maker. Note that if the maker sends out a bulletin that covers all rides, you make as many entries as you have rides by that maker, so you have one entry for each ride (with constant maker and bulletin number). If the maker issues two bulletins for the same ride (probably at different times), you have two entries with different bulletin numbers (with constant maker and ride).

If you have not come across this before, it is called a junction table.

 

[qurn]

Thank for the fast reply I found it incredibly helpful. I was worried that I might have to do something like this. The good news is that I understood almost all you said, I'M LEARNDING! I'm going now to read up on references and junction tables. Referances aren't the same as relationships are they?

Edit:there's no way I'll be getting any DLL file because the state hates me doawnloading crap, but we should get some text recognition software soon that will help us change the file type.

 

[The_Doc_Man]

I work with the Dept. of Defense so "the state hates me doawnloading crap" goes double for me. Instead of state agencies, I get to deal with NCIS - and not the good-lookin' goth chick from the TV series of that name, either. I get the investigators with orthopedic shoes and no sense of humor.

But I digress... this Adobe .DLL file wouldn't be something you could download. I'm talking like, you would have to buy the Adobe WRITER kit to get it. Reader won't have it as far as I know. (Unless someone else on the forum has found it and can advise you.)

As to your other question: No, references are not the same as relationships, ... or maybe they are related only distantly.

A relationship shows that two tables have something in common. One or more fields (the same number from each table) will participate in the relationship. The tables can be JOINED across the common fields thus creating a potentially useful query that shows data from two tables related to each other in some way. For example, the maker table is related to the whomakes table (in my earlier example) because each entry in whomakes points back to a maker. If you have many rides from the same maker, you will have a many (whomakes) to one (maker) relationship. Which means that you could look up the makers of a given ride. Or, in the other junction table, the bulletins for a give maker and/or ride.

References show that Access wants to use a dymamically linked library file (.DLL) because Access and this file have something in common. Namely, Access wants to look up the entry point of a routine in the .DLL file because it wants to call that entry point. References are like relationships but sort of at the callable code level and they are unidirectional.

OH, one other MAJOR difference. Relationships are kept entirely within the .MDB file. References are externally supported by the Windows Registry. Which is why relationships work anywhere but references are system-specific.

 

[qurn]

Wow man your are being extreemly helpful recently. I read another thread you posted in yesterday where a guy was having a similar problem. He was using the getfile.mdb to try to edit somethings. I pretty much assumed what references where, and reading up on it showed my origonal instincts to be correct. Sence they are supported by the windows registry means that they are written in Basic, just like the getfile.mdb, right?

If this is the case I've it a major stumbling block, I know very little about basic. I even have trouble understanding the newbie threads in this forum.

Do you have know any place I could get some info on Basic a complete newbie could understand, I've googled for tutorials before but came up goose egg.

I have adobe acrobat reader, my boss has acrobat writer. of course i believe it would be illegal for me to "borrow" and I have no money to buy anything my self.

thanks and good luck "working for the state" buddy.