Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union & Table Create (Access 2000)

    Could someone help me with VBA code to Union to Tables and then from the result create a new table

    Thanks

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

    Re: Union & Table Create (Access 2000)

    The easiest way to create a table from the results of a union query is to create two stored queries:
    - A Union query that selects the data, and
    - A Create Table query based on the Union query.
    Run the second query to create the new table.

    If you wish, you can build the SQL strings for both queries in code, and then use DAO to create querydefs with this SQL, and execute them using Docmd.OpenQuery.

    On the other hand, you can use DAO or ADO to loop through records and write records to a new table, but that will be much slower than using SQL.

    If you want more help, perhaps you can provide more details.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union & Table Create (Access 2000)

    I'm using the following code to create 2 temp tables:

    DoCmd.RunSQL _
    "SELECT DISTINCT AgreementNo, AccNo INTO tblBillReb1 FROM Contr WHERE Not AgrNo Is Null " & _
    "AND RebT1 Not Like ""STMNT"" And RebT1 Not Like ""DIV"";"

    DoCmd.RunSQL _
    "SELECT DISTINCT AgreementNo, AccNo INTO tblBillReb2 FROM Contr WHERE Not AgrNo Is Null " & _
    "AND RebT2 Not Like ""STMNT"" And RebT2 Not Like ""DIV"";"

    Now I would like to join (make one table) from these two table using DoCmd

    Thanks

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

    Re: Union & Table Create (Access 2000)

    As far as I know, you can't do that in one SQL statement. A union query can only be a selection query. The following SQL statement is not valid in Jet SQL:

    SELECT * FROM tblBillReb1 UNION SELECT * FROM tblBillReb2 INTO tblUnion

    So you must either store the union (selection only) query and then run a create table query on it (the latter can be done with DoCmd.RunSQL but the former MUST be a stored query), or open a recordset based on the union SQL statement, and loop through the records to save them to a new table.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union & Table Create (Access 2000)

    How do you do the "stored query" as part of the code

    Thanks for your time

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

    Re: Union & Table Create (Access 2000)

    I always use DAO for this; you need a reference to the Microsoft DAO 3.6 Object Library for this to work. Code could look like this:

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    Set dbs = CurrentDb
    strSQL = "SELECT * FROM tblBillReb1 UNION SELECT * FROM tblBillReb2"
    Set qdf = dbs.CreateQueryDef("qryUnion", strSQL)
    strSQL = "SELECT * FROM qryUnion INTO tblUnion"
    dbs.Execute strSQL, dbFailOnError

    Set qdf = Nothing
    Set dbs = Nothing

  7. #7
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union & Table Create (Access 2000)

    When I run the code:

    Function CreateUnionRebates()

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    Set dbs = CurrentDb
    strSQL = "SELECT * FROM tblContrBillRebate1 UNION SELECT * FROM tblContrBillRebate1"
    Set qdf = dbs.CreateQueryDef("qryUnion", strSQL)
    strSQL = "SELECT * FROM qryUnion INTO tblContrBillReb"
    dbs.Execute strSQL, dbFailOnError

    Set qdf = Nothing
    Set dbs = Nothing

    End Function


    I get the following error:
    Run-time error '3012':
    Object 'qryUnion' already exist.

    Somewhere I'm doing something wrong

    Thanks again for the help

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

    Re: Union & Table Create (Access 2000)

    Apparently you already created a query with that name. This will happen if you run this code repeatedly. You can delete the query if it exists before creating it anew:

    On Error Resume Next
    ' Delete query, but don't holler if it doesn't exist.
    dbs.QueryDefs.Delete "qryUnion"
    On Error Goto 0 ' or if you have error handling, jump to its label.
    ...
    Set qdf = dbs.CreateQueryDef ...

    Note: this code is dangerous in a multi-user environment in which several users could try to run this code at more or less the same time. One user could create qryUnion, then another user could delete it before the first one has had the chance to use it.

Posting Permissions

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