General discussion

Locked

linking tables in access

By trainer4063 ·
Using Access 2000 frontend, SQL2000 backend. Have a need to delete and link tables based on criteria in a query. Example CustomerTableA has data about one set of customers. CustomerTableB has data about another set of customers. SalesTable has sales information about customers and, which table they are in, from both. When looking up customer information using the unique key in the SalesTable, I need to link up to the appropriate CustomerTableA/B. Merging the tables is out of the question because of the large number of records. I already have a script that allows me to delete the table alias that is already linked and bring in the other table using a form. Any Help Please.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by ESchlangen In reply to linking tables in access

If the structure of the two tables is the same, you could use a UNION to make a view of both. Then use this view to look up customer info. This would eliminate the need to know where the specific customer data resides.

Collapse -

by trainer4063 In reply to

Poster rated this answer.

Collapse -

by trainer4063 In reply to linking tables in access

Unfortunately, I also need to edit the information in the CustomerTableA or B. A union query will not allow me to edit or change the information in it. A union query is read only. I have a form that allows me to manually make the link, I'm trying to automate the selection based on a field in the SalesTable so my users don't have to stop what they are doing just to change which table they are getting their information from. The forms VB code to make the selection looks like this:

Private Sub cmdAttachTable_Click()

If MsgBos ("Attach table " & Forms!frmAttachTable!Attach & "?") Then

DoCmd.DeleteObject acTable, "Campaign"
Docmd.TransferDatabase acLink, "ODBC", ............
DoCmd.Close

End If
End Sub

I just want to automate the process for my users. The less they have to interface with the better.

Collapse -

by trainer4063 In reply to linking tables in access

This question was closed by the author

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums