Linking ALL Tables from BackEnd Database

[David Barger]

I have a bid / contact database that will have a backend and multiple
frontends. From the contact data, I intend to generate a list of potential
bidders for each estimate that we have. Since this would be a new table in
the backend, how do I get the other frontends to recognize the new tables?

 

[Dorian]

You need to link that table into every front-end.
Tools--Database Utilities--Linked Table Manager
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[David Barger]

Actually, I was looking for something a little more automated. It is my
intention to add and delete tables from the BE frequently. I would like the
user to be able to update links to these tables without having to add each
table individually.

Besides which, the linked table manager is for tables that are already
linked. I am looking for new tables not yet linked to the front end.

 

[Peter Hibbs]

If this is something you are likely to do on a regular basis then have
look at the Back End Update Utility which will make it easier for you.
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=410

Also the BE Relink code will re-link the new tables to the back-end
file. See
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=184

HTH

Peter Hibbs.

 

[Tony Toews [MVP]]

Actually, I was looking for something a little more automated. It is my
intention to add and delete tables from the BE frequently. I would like the
user to be able to update links to these tables without having to add each
table individually.

I would question your system design if you want to add or delete
tables from the BE frequently.

What is your objective here? Possibly we can give you some
alternative solutions.

Tony

 

[Paul Shapiro]

Instead of a new table for each bid's bidders, you should be able to use an
association table. Something like this perhaps:

Job (jobID, jobName, startDate, endDate, etc.)

Contact (contactID, lastName, firstName, ...)

JobBid (jobID, contactID, bidDate, estimatedPrice, ...)

I've never seen a good database design that needs new tables created on a
regular basis. Whatever the need appears to be, I would suggest there must
be a better design alternative. Better to keep considering design
alternatives until you find a good one, rather than trying to implement a
poor design.

 

[David Barger]

I would question your system design if you want to add or delete
tables from the BE frequently.

What is your objective here? Possibly we can give you some
alternative solutions.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

My objective is to store the bid list for each estimate. First, I need to
track who has accepted the bid invitation. And later, we have to send out
addendums. At some point, I also intend to try and integrate the database
with FedEx's software to make the process a lot less aggravating. (Here I
sit with a database that has all of those addresses. There has to be a way I
can automate printing the FedEx labels.)

I was thinking that once we have finished with an estimate, we can archive
the table.

I am extremely open to suggestions on this matter, and greatly appreciate
suggestions. (Similar to how a drowning person appreciates a lifeline.)

 

[David Barger]

But I am very good at finding the strange instances where the norm doesn't
work. This may not be one of those occasions, and I am certainly open to
better ideas.

My thought is to put the data in a table named from our estimate number.
Then from a form anyone can load the data and modify as needed. Once the
estimate is done, we archive the data. This table has CompanyID, CatagoryID,
PointofContactID, Select, and Accept. I only populate the table if a company
has been selected. (Select is only false if they are deselected.)

So, if I were to add an estimate number field....

This requires contemplation.

Muchas Gracias

 

[John W. Vinson]

But I am very good at finding the strange instances where the norm doesn't
work. This may not be one of those occasions, and I am certainly open to
better ideas.

My thought is to put the data in a table named from our estimate number.
Then from a form anyone can load the data and modify as needed. Once the
estimate is done, we archive the data. This table has CompanyID, CatagoryID,
PointofContactID, Select, and Accept. I only populate the table if a company
has been selected. (Select is only false if they are deselected.)

Storing data - an estimate number - *in the table name* is certainly A Very
Bad Idea. What if you wanted to count the number of estimates accepted, or
count the number of companies that had received estimates!?

Why not just *add a field*:

This table has EstimateNo, CompanyID, CatagoryID,
PointofContactID, Select, and Accept

and use a Query to select only the values for one estimate (if you should need
to do so)?

 

[Tony Toews [MVP]]

My objective is to store the bid list for each estimate. First, I need to
track who has accepted the bid invitation. And later, we have to send out
addendums. At some point, I also intend to try and integrate the database
with FedEx's software to make the process a lot less aggravating. (Here I
sit with a database that has all of those addresses. There has to be a way I
can automate printing the FedEx labels.)

I was thinking that once we have finished with an estimate, we can archive
the table.

I am extremely open to suggestions on this matter, and greatly appreciate
suggestions. (Similar to how a drowning person appreciates a lifeline.)

Mark the estimate as closed or inactive. Then, other than for one or
two "history" type of screens all forms, combo boxes and reports
ignore the closed/inactive estimates.

Tony