Is it possible to create a table from another table?

Topic

New Reply

If I have a table that contains records identifying a field name, field type, default value, format, caption etc., can I use the records within the table to create a table from scratch? Would I use DAO or ADO?

Is it also possible to create export specifications from a table? For example if I have a table with all the field names, size, starting position, type, can I create a text Export spec? I have several tables that I need to export to fixed width text file, and I also have the specifications for the layout in a separate table. I am looking for a way to create the spec from that table, instead of manually creating the spec.

Thanks for your consideration.

Ken

Reply | Quote

Replies

If I have a table that contains records identifying a field name, field type, default value, format, caption etc., can I use the records within the table to create a table from scratch? Would I use DAO or ADO?

Is it also possible to create export specifications from a table? For example if I have a table with all the field names, size, starting position, type, can I create a text Export spec? I have several tables that I need to export to fixed width text file, and I also have the specifications for the layout in a separate table. I am looking for a way to create the spec from that table, instead of manually creating the spec.

Thanks for your consideration.

Ken

Play around with Export from the File menu. This only allows you to do one at a time but gives you all standard formats, excel, text, csv, other database formats. You can use the records within a table to make another one by using a Make Table query. If you want to do more than one table at a time, you would have to use code. A lot depends on what it is you need to do with these tables in terms of determining the most efficient way to do it.

Reply | Quote

Play around with Export from the File menu. This only allows you to do one at a time but gives you all standard formats, excel, text, csv, other database formats. You can use the records within a table to make another one by using a Make Table query. If you want to do more than one table at a time, you would have to use code. A lot depends on what it is you need to do with these tables in terms of determining the most efficient way to do it.

Peter,

Thanks for the reply. I need to be more specific.

I have table A with fields named:
Name: Type: Caption : Precision
where Name = the name of the field to be created
Type is the type of field
Caption is the caption to use for the field
Precision is the decimal places for numeric values

Sample Records in the table :
txtFirstName : Text : First Name :
txtLastName : Text : Last Name :
intAge : Integer : Age : 0
PayRate : Currency: Rate : 2
Weight : Single : Weight : 2
DoB : Date/Time: Birthdate :

These records would create fields in a table with the above specs.

I hope this better explains my goal.

Thanks!

Ken

Reply | Quote

In answer to your first question, yes you can – but you don’t use either. You manipulate a native Access table using the TableDef properties with VBA. ADO and DAO are methods for working with records in tables, but not for changing the table design.

However, creating or changing export specifications programatically is not a trivial task, and is dependent on the version of Access you are using. In particular, Access 2007 stores them in a different fashion. I’ve never attempted to do that, and I must say my experience trying to create fixed with text files presents some significant challenges. For example, boolean fields don’t seem to behave terribly well, and dates sometimes what to export with time as well. With further web research you may be able to find some information on how to approach it, but my guess is the programing research would take far longer than just creating the export specifications. If you do find a good resource on the subject, please post it so others can benefit from it. Thanks.

Reply | Quote

In answer to your first question, yes you can – but you don’t use either. You manipulate a native Access table using the TableDef properties with VBA. ADO and DAO are methods for working with records in tables, but not for changing the table design.

However, creating or changing export specifications programatically is not a trivial task, and is dependent on the version of Access you are using. In particular, Access 2007 stores them in a different fashion. I’ve never attempted to do that, and I must say my experience trying to create fixed with text files presents some significant challenges. For example, boolean fields don’t seem to behave terribly well, and dates sometimes what to export with time as well. With further web research you may be able to find some information on how to approach it, but my guess is the programing research would take far longer than just creating the export specifications. If you do find a good resource on the subject, please post it so others can benefit from it. Thanks.

Wendell,

Thanks for the follow up. I am using Access 2003. I have found a snippet of code I am testing for creating the tables. The master source table I have contains like 200+ fields with specs, and I need to create several tables from the source. If I have any luck, I can share the resulting code.

As for the date and boolean fields, in the past when I had to create text files, I used queries and formatted the date fields using something like ExprtDate:format(MyDate,”YYYYMMDD”) as the field to export. And something like YesNo:IIF(Fldname=true,”Y”,”N”).

I’ll research and test creating an export spec via code and share any positive experience.

Thanks!

Ken

Reply | Quote

Sometimes one forgets to look in the obvious places. It finally dawned on me that I should search this forum, and I think you may find This post by Jan Karel Pieterse – it deals with import specs instead of export specs, but it describes where you can find the specs in 2003, and indicates that those objects are exposed to the VBA engine in 2007, but not 2003 and earlier.

Reply | Quote