Results 1 to 3 of 3

Thread: naming a table

  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: naming a table

    Is there a way to have Access automatically name a table? I have a database that keeps track of workflow. Everyone has to enter in what they received as work for each day at the end of the month the figures are totaled. I want to have a button that archives the list and names it the current month/year that it was created. IS that possible?

  2. #2
    Join Date
    Nov 2002
    Posts
    49

    Re: naming a table

    Originally posted by cc3658
    Is there a way to have Access automatically name a table? I have a database that keeps track of workflow. Everyone has to enter in what they received as work for each day at the end of the month the figures are totaled. I want to have a button that archives the list and names it the current month/year that it was created. IS that possible?
    I do not have a code for you to create the table, but there are examples in this forum. As far as the other code you will need here is an example:

    archived589 = "arc589_" & Year(Date)
    strsql = "INSERT INTO " & archived589 & " SELECT * FROM tbl589 "
    strsql = strsql & "WHERE tbl589.ID = " & "'" & txtID & "'"
    DoCmd.RunSQL strsql


    To modify this code to your application, you will need to decide on a table name. In this example, arc589 is the base table name with the year added at the end. Change the string to give you the month/year format you are looking for.

    This code will work only if the table exists. In my application, I created several tables for future use, but there has to be a better way than that. Also, another option is to only have 1 archive table and have a date column in it. You can save all your records to this table, and retrieve records sorted by the date they were added to the table.

    Hopefully this gives you a few ideas to get you started.

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    You can create a table using the ADOX Tables Collection, and append it to the ADOX Catalog Collection:

    Dim cat as New ADOX Catalog
    Dim tbl as New ADOX.Table
    cat.activeconnection = currentproject.connection

    with tbl
    .name = YourTableName & HoweverYouWantToFormatYourDate
    .columns.append "SomeField", TheTypeConstantOfField
    .columns("SomeField").OtherStuffYouNeed
    ...
    end with

    cat.tables.append tbl

    The Access Online help shows how to do this with DAO. Just type CreateTableDef Method Example in the Answer Wizard and it will come up.
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

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