Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    make table (97 sr2)

    We want to create a make table query where the name of the table being created will change according to the criteria of the data. Example: If we have a list of all cities in New Jersey and by using the criteria "Trenton" make a table called Trenton, and the next time we use the criteria "Newark" we would want the table this time to be called Newark.

    I am not sure if something like this is even possible, so thanks for any help!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make table (97 sr2)

    A make-table created interactively uses a fixed name for the table to be created. To make the table name dynamic, you need code. For example: put a text box or combo box on a form in which the user can enter or select the city. The form also contains a command button that the user clicks to create a table.

    For illustration purposes:

    <table border=1><td>Table</td><td>tblData</td><td>Field</td><td>City (text)</td><td>Combo box</td><td>cboCity</td><td>Command button</td><td>cmdMakeTable</td></table>
    The On Click event procedure for the command button would look like this:

    Private Sub cmdMakeTable_Click()
    Dim strSQL As String

    If IsNull(Me.cboCity) Then
    MsgBox "Please select a city.", vbExclamation
    Me.cboCity.SetFocus
    Exit Sub
    End If

    strSQL = "SELECT * INTO [" & Me.cboCity & "] FROM tblData " & _
    "WHERE City = " & Chr(34) & Me.cboCity & Chr(34)
    CurrentDb.Execute strSQL, dbFailOnError
    End Sub

    Note: I don't really like this idea. It may create table names with spaces in them ("New York"), I always avoid spaces in the names of database objects, fields, etc.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: make table (97 sr2)

    In general, make tables get assigned a name at the time you define the query, so you can call it anything you want if you are creating a new query each time. If however you are using a stored query, then you would have to change the name in the properties of the query. That can be done manually, but it can also be done in VBA if you are proficient in the object model for Access, and are comfortable using DAO and VBA. Is there a particular reason for doing a make table instead of simply using a select query against a master table of all cities in New Jersey? As long as you don't mess with the data, the select query may work just as well.

    <font color=blue>Added later:
    I see Hans has provided you some code to do what you described. One question which I also meant to raise is how you get rid of the tables once you have created them? If you don't have a process in mind, then your database will get pretty cluttered with tables over time, and you will start getting prompts that ask if you want to delete the existing table.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make table (97 sr2)

    Thanks,
    I will pass this along.
    Cathy

  5. #5
    New Lounger
    Join Date
    Jun 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make table (97 sr2)

    Cathy and I are working together on the make-table situation. The code you gave works great. Thank you. My next question is - can I export the table created to another database?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make table (97 sr2)

    You can create the table directly into another database:

    strSQL = "SELECT * INTO [" & Me.cboCity & "] IN 'C:AccessOtherDatabase.mdb' FROM tblData " & _
    "WHERE City = " & Chr(34) & Me.cboCity & Chr(34)
    CurrentDb.Execute strSQL, dbFailOnError

  7. #7
    New Lounger
    Join Date
    Jun 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make table (97 sr2)

    THANK YOU!!! It works. The last element of challenge. After I run the make table code I want to either grey out the command button so the code is not run again or have a message box pop up if the table already exists. I am running into a road block because the form is continuous and the button is not unique.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make table (97 sr2)

    As you have found, enabling/disabling controls in a continuous form is not attractive, since it operates on all visible records. You could add a Yes/No field Exported to the table behind the form. In the code for the command button, start by testing if this field is True, and exit if so. Otherwise, export the table and set the field to True:

    ' Test
    If Me.Exported = True Then
    MsgBox "Already exported!", vbInformation
    Exit Sub
    End If

    ' Code to export goes here
    ...

    ' Prevent exporting again next time
    Me.Exported = True

  9. #9
    New Lounger
    Join Date
    Jun 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make table (97 sr2)

    Thank you for all of your help. Everything works perfect - for now.
    Missy

  10. #10
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make table (97 sr2)

    I am using this select into code to make a table in another database. It works the first time but when I run it a second time I get an error because the table name is already in the database. What can I do to make a new table with the same name everyday?

    Thanks
    Cathy

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make table (97 sr2)

    Delete the table before creating a new one. For instance, use this procedure:

    Sub DeleteTableFromOtherDatabase(DatabaseName As String, TableName As String)
    Dim dbs As DAO.Database
    On Error Resume Next
    Set dbs = OpenDatabase(DatabaseName)
    dbs.TableDefs.Delete TableName
    dbs.Close
    Set dbs = Nothing
    End Sub

    In the code you have, insert a line like this above the line that creates the new table:

    DeleteTableFromOtherDatabase "C:AccessOtherDatabase.mdb", "MyTable"

    with, of course, the appropriate names substituted.

  12. #12
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make table (97 sr2)

    I put this in the module section

    Sub DeleteTableFromOtherDatabase(DatabaseName As String, TableName As String)
    Dim name As String
    Dim dbs As DAO.Database
    name = "ATR110F04VOL1winappsForBenefitOf_Shrshared_data.m db"
    On Error Resume Next
    Set dbs = OpenDatabase(name)
    dbs.TableDefs.Delete "CCMBalance"
    dbs.Close
    Set dbs = Nothing
    End Sub

    and this in my code

    DeleteTableFromOtherDatabase "ATR110F04VOL1winappsForBenefitOf_Shrshared_data.m db", "CCMBalance"

    I know I must have something typed incorrectly here. The database is not opening.

    Thanks for your help

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make table (97 sr2)

    The OpenDatabase instruction in this code only opens the other database in code, so that it can manipulated in code - in this instance to delete a table from it. It does not open the database in the Access interface.

  14. #14
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make table (97 sr2)

    I am sorry I am so confused with this. I want to open the other database and then delete the table so the make table code will run correctly each time. Can you direct me?
    Thanks so very much

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make table (97 sr2)

    The code I posted WILL delete the table in the other database, so that the make-table code will run without causing an error. It will not open the other database visibly, but that is not necessary. VBA code is able to manipulate a database even if it is not displayed on the screen.

Page 1 of 2 12 LastLast

Posting Permissions

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