storing tableid

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hi,

I have this query (see below) from msysobjects where i have an id and the
name from the table.

SELECT msysobjects.Id, msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Type)=1) AND ((msysobjects.Flags)=0));

What i would like to do, is to store the id from a table with the other
values into another table. That other table has the following
tablestructure:

field datatype
tableid number,
id number,
value currency,
description text

How can i do this?
 
Create the target table the way you want it.

Open the query in design view, and turn it into an Append query (Append on
Query menu). Access will ask what table to append to, and you can map the
fields.
 
Hi Allan,

That is not what i want. I would like pass the tableid from msysobjects with
another table's values into a table. I don't know if i should join the
tables or do it programmatically.

The record should look something like this:

tableid | id | value | description
1 1 10,45 dollars
2 20 100 pounds

tableid 1 = mytable1
tableid 2 = mytable 2

Do you know how to do this?
 
Sorry, Jason, I don't think I understand what you want to achieve.

The msysobjects table does give you the Id of the table, so it's easy to use
that. However it does not have fields called "tableid", "value", and
"description", so I'm not sure where you intend to get these values from.

Tables do have a Description property, but not a Value property. Fields have
a Value property and a Description property.

Are you talking about trying to get all the fields of the table into another
table? If so, this link would help you walk through the fields of the table
to extract their Value, Description, and data type:
http://allenbrowne.com/func-06.html
 
Hi Allan,

The value and description are not from the msysobjects, but from a generic
table where i can store the values from different tables.

Normally when you insert a record, you insert an id and some fieldvalues. In
my case i want to insert a tableid along with the other fieldvalues.
 
Okay, so you already have a query into another table, and you also want to
get the Id value from the MSysObjects table for a table named (say) MyTable.

You could do that with a DLookup(), or (more efficiently) with a subquery.
Try typing an expression such as this into the Field row of your query:
Id: (SELECT Id FROM MSysObjects WHERE (MSysObjects.Type = 1) AND
(MSysObjects.Name = 'MyTable'))

Then map it to the desired field.

If subqueries are new, Microsoft's introduction is:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Hi Allan,

The subquery works for me. However, i saw that you need to set the table
name as a string in the subquery. Is is possible to use dynamically, because
if i change the name of the table i also need to change the name in the
subquery.

Is this possible?

Jason
 
If you have the Id, you could try matching on that instead of the Name.

Otherwise you would build the SQL string dynamically, concatenating the name
of the table into the string.
 
Back
Top