Dynamic Table Reference

[Guest]

I have several tables that are linked to excel spreadsheets that also drive parametric solid models in Inventor. The link allows dynamic updates on revision. The structure of each table is identical. I have several queries and reports that extract part attributes for creating a Bill of Materials

I would like to have the user open a dialog box where he inputs the Job Name, which is the table name. This name is then used in the FROM clause in the queries and the record source in the reports, creating a Bill of Materials for that job. How can this be done

Thanks in advance

 

[Rebecca Riordan]

You can't do use parameters in the FROM clause of a query, so the best you
can do is create the query in code. But are you sure jobs ought to be
separate tables? It's more usual to use the JobName as the primary key of a
single table. If you do that, you can select the JobName in the WHERE
claue, which _does_ accept a parameter, and you can set up what you're
asking for without coding.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

I have several tables that are linked to excel spreadsheets that also

drive parametric solid models in Inventor. The link allows dynamic updates
on revision. The structure of each table is identical. I have several
queries and reports that extract part attributes for creating a Bill of
Materials.

I would like to have the user open a dialog box where he inputs the Job

Name, which is the table name. This name is then used in the FROM clause in
the queries and the record source in the reports, creating a Bill of
Materials for that job. How can this be done?

 

[Art]

Hi Sam,
Consider Rebecca's solution. You can use hyperlinks
in a table field to link the excel sheets.

The Easy Day

-----Original Message-----
As far as I know, i can't maintain links to several

spreadsheets in one table, hence the individual tables
which can maintain the links. The idea is to input the
data once and have it drive the model and the Bill of
materials. Revisions are common during the life cycle of
the job, so maintaining the link is advantageous,
lessening errors. After the job is delivered, I plan to
append the table to a master table, which serves as an
archive.