Linking to an already linked table

[Storyteller]

Hello All,
I know that I can create a link to a table in another datasource. My question is how can I link to an already linked table?

Example:
ACT Access (BackEnd)
Contacts --> dB Contacts (This is the first link)
Access (BackEnd) Access (FrontEnd)
dB Contacts --> Front End (is what I need to link to another access database.)

I am currently using Access 2000, but I can also do this in Access 2002 (XP).

Thanks,
Michael

 

[GoDawgs]

You can't link to a linked table...you can link back to the source though, link to the ACT db.

 

[Storyteller]

Hello GoDawgs,
Thank you for the confirmation. It is not the response I was I was hoping for ;-)

Regards,
Michael

 

[willir]

Michael

Before hanging up your skates, what are you really trying to do?

My read of your information is...

- An Act program links to an Access database, dbContacts (Backend) - persumably, you link to a table, say tblContact1
- You link dbContacts (backend) to front end Access database

And you want the frontend Access database to link to another database?

Are you saying that you want to link to a logical table that is linked to antoher table?

If so, can you bypass the intermediary logical table in dbContacts and point straight to the source?

I am confused.

Richard

 

[Storyteller]

Hello Richard,

I am creating a Work Order/Inventory system in Access. The contact information is in ACT! (Contact Name, Company, Address, etc) I need to link Access to ACT! to get to the contact information.

The Access BackEnd needs to have tblWorkOrder and tblInventory, etc. I would like the BackEnd also be linked to the ACT! table "Contacts. The Access FrontEnd is to have only Queries, Forms, Reports and Modules.

I am trying to avoid having to link each Access FrontEnd (10 workstations) to the ACT! table "Contacts" individually.

My ideal scenario is to have all tables in the BackEnd and everything else in the FrontEnd. As it stands now, if I link the Access BackEnd to ACT! I cannot then link the Access FrontEnd to the linked ACT! table in the Access BackEnd.

I have explored other products (

http://www.photoact.net/actaccess)

and it is promising, but I would like to minimize the number of software involved if possible.

Thanks for your ideas.

Regards,
Michael

 

[willir]

Very cool project.

Consider this work around...

Create a contact table that is linked to Act, say tblActContact.

Have another contact table or tables that is built from tblActContact. Your users are link to this table or tables.

Then build the logic to use new information in tblActContact to update the other tables.

This part should not be too hard since the data is only going one way. However, deciding on a process to recognize additions / deletions and the toughest one, modifications will be the trick.

I would suggest adding a date field to tblActContact that defualts to Now() so any new additions will have today's date.

Deletions can be detected by running a query for records not in tblActContact.

"We" got rid of Act a couple of years ago, so I can not test this. Although I support the application, I never used it. Does Act allow you to create a field or use an additional field? If so, then it would be helpful if the person entering the contact information also entered a "flag" to indicate that the contact info for this person has changed.

Using these three methods - date, "not in" and "flagged" records would make it fairly easy to update the contact info on the Access side.

You may also want to create a "fix-it" program that goes through the tables to "adjust" for data entry / human errors.

Lastly, I stated contact table or tables that are updated from tblActContact. How you do this will depend on your contact information and the degree of normalization you want todo.

Good luck
Richard

N

 

[danvlas]

You are not limited to one data source...You can link the tables from both back-end and from Act to your front-end.

They will behave in the same way as being in the same database.

You don't need to link the Access Back-End to Act, as the backend does not analyze data. It just stores some...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[willir]

Dan

Agreed.

I think Michael's issue is that he wants to have 10 workstations link to one current contact table or tables.

Are you suggesting he links the ten users to the Act database? By his comment, "I am trying to avoid having to link each Access FrontEnd (10 workstations) to the ACT! table "Contacts" individually.", I had dismissed this option.

You are a proven vetern in this forum. How would you suggest this be accomplished?

Richard

 

[calibrescott]

Hi guys, slightly off topic here, but I notice the 'Group' info from ACT! isn't in the main .dbf data file. Does anyone know where it is or how to get to it?

 

[danvlas]

One solution would be to place one copy of the front-end on the network and run it from all workstations. That way only 1 frontend will be linked to all sources.

Another one would be to use UNC when linking the sources. Link the tables to one front end, then copy the front end to all workstations.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Storyteller]

Hello All,

These are all great suggestions.

The idea of using UNC is something I hadn't thought of so I will look into that one and each of the other ones and see how they work.

Thanks,
Michael

ps: The "Group" table/file is seperate from the contact.dbf You should get a copy of the ACT!SDK to see how to access it. What application are you using? If it is Crystal Reports there is a pdf file on the Crystal Website that shows the table links.