Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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

  2. #2
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='kwvh' post='792270' date='05-Sep-2009 13:53']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[/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.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='PeterN' post='792274' date='05-Sep-2009 13:41']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.[/quote]
    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

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='792275' date='05-Sep-2009 13:50']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.[/quote]
    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

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •