PDA

View Full Version : Solved: Link to two databases from one front end



khyber
07-31-2005, 09:41 AM
I designed and implemented a database that contains nineteen tables ? most of the fields are text fields with 255 character limits. This is an index to a repository for documents stored online.

Now I must add more tables to access more documents. The second index is different from the first. It contains the master documents that govern the documents in the first index. It will contain a lot of text fields with the same character limit ? 255. Because of the size I created a second database for this new index.

The Access front end to the first repository index contains links to the data tables. I must do this with the second repository index. Will this create a problem ? a front end linking to data tables in two different databases? The tables used for option lists and tables used for reports (results of make-table queries and keyword searches) are all local ? in the front end. The input forms write to either one or another index. Never does an input form write to both indices. Should the need arise - to write to both from one input form - will this be possible?

I will be designing reports on the contents of both indices. I have already created queries to check out the links and they are working fine.

If anybody has advice or can point me to knowledge based articles on this problem I sure would appreciate it.

TNX

xCav8r
07-31-2005, 09:45 AM
This is tough to wrap my mind around. Can you screen shot your relationships and attach it to this thread? That might provide a better frame of reference for any discussion about the advantages and disadvantages of your entity relationships.

khyber
07-31-2005, 09:56 AM
Actually, there are no defined relationships between the two databases containing data. There is a relationship on one field only. It is the master document primary key. It is manully entered in the second database when the child document is indexed. I created the relationship when I designed a query. It is a one to many on the master document side (one master contract governs many child contracts). That is the one and only field that appears in both databases. The rest of the text - schedules, and obligations - is specific to the document that is indexed.

khyber
07-31-2005, 10:01 AM
If you need that snapshot I will send it from work tomorrow. I don't have Snagit on my home PC. It may take a while. I have to learn how to attach it to this post. Bear with me, I am a newbie to this site

xCav8r
07-31-2005, 10:03 AM
Alrighty then. We'll assume your entity relationships are AOK.


Will this create a problem ? a front end linking to data tables in two different databases?

No, you can link to tables in as many databases as you'd like from an Access front end, and as long as you can access those tables, the number of databases will not have an effect.


Should the need arise - to write to both from one input form - will this be possible?

Yup, again, no effect from having tables from more than one database so long as they're available.

xCav8r
07-31-2005, 10:05 AM
If you need that snapshot I will send it from work tomorrow. I don't have Snagit on my home PC. It may take a while. I have to learn how to attach it to this post. Bear with me, I am a newbie to this site

Snaggit is for video screen capture. All you need to use is the PrtScn button on your keyboard to get a screen shot. And, to attach an image to a post, you just hit the manage attachments button when you're in advanced posting mode. Alternatively, you could upload the screen shot to the web some place and then use bb tags [ img ] URL [ /img ] to refer to it.

khyber
07-31-2005, 10:19 AM
xCav8r. You have set my mind at ease. Intuitively, I knew I could do this. And, some of the text books I learned from state that this is possible - they just don't provide examples or real databases to prove that it is possible. A real voice out there with loads of experience and knowledge in this field has calmed my inhibitions. I will continue with the original design. The data tables will always be available to the front end and there should be little traffic on the wires - this database is limited to four users only. In the event it gets bigger we will make it a web based application. In that case, I know it is do-able.

Thanks again for your excellent help. Have an excellent day!

xCav8r
08-02-2005, 10:16 PM
khyber, I'm happy to help. If you want some advice on your entity relationships, just let me know. :)