Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Array use (Access 97/VBA)

    I have a string array called QueryStr, the number of elements is determined by the number of records in a table i.e. QueryStr(ProjSet), where Projset = rec.fields("CountofName")

    I have filled the array with queries for a Union query which Id then like to make i.e.
    set qdf = CurrentDb.CreateQueryDef("qryNetworkReport", Querystr)

    Would anyone have any idea how to make a query using a dynamic array?

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

    Re: Dynamic Array use (Access 97/VBA)

    Do you mean that the elements of QueryStr are SQL strings of the form "SELECT ... FROM ... WHERE ...", and you want to create a union query that combines all these?

    In that case, you can use something like

    <font face="Georgia">Dim i As Integer
    Dim strSQL as String
    Dim qdf As DAO.QueryDef

    ' Initialize strSQL - not really necessary...
    strSQL = ""

    For i = LBound(QueryStr) To UBound(QueryStr)
    ' Concatenate individual SQL strings
    strSQL = strSQL & " UNION " & QueryStr(i)
    End If

    If strSQL <> "" Then
    ' Get rid of first " UNION "
    strSQL = Mid(strSQL, 8)
    ' Create QueryDef
    Set qdf = CurrentDb.CreateQueryDef("qryNetworkReport", strSQL)
    End If</font face=georgia>

    Note: *you* will have to make sure that all elements of QueryStr can be combined in a Union query!

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Array use (Access 97/VBA)

    Thanks for replying Hans,
    That is what I mean however, using your method would I not run into problems with the length of the string?

    The average query is as follows and at the moment there are 12 of these for different projects, and this may increase:
    SELECT Teachers.[Education Number], "RAF" AS Name
    FROM RAF INNER JOIN Teachers ON RAF.[Teacher Id] = Teachers.[Teacher Id]
    GROUP BY Teachers.[Education Number], "RAF"

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

    Re: Dynamic Array use (Access 97/VBA)

    A SQL string can be about 64,000 characters (look up Specifications, Microsoft Access in the Help index). I don't know of another method beside union queries to combine the results of multiple queries in one query. The only alternative I can think of would be to create a temporary table and use append queries to fill it; however, you'd have to check for duplicate records yourself - union queries remove duplicates automatically, unless you use UNION ALL.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Array use (Access 97/VBA)

    What I meant by length of string was the variable that would hold the SQL string, or does that not have a limit in VBA?

    Also, thanks for the tip about the temporary table. I will try adding the union queries into one string and if that fails I will change the union queries to append queries and run them that way. I should be able to use group by queries to get rid of any duplicates.
    Thanks for your help.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Array use (Access 97/VBA)

    Tried it with adding the string arrays together in one variable and it worked fine.
    Thanks for your help Hans.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic Array use (Access 97/VBA)

    Was there some special reason for posting this question here instead of the Access forum where it really belongs? You can post *all* Access questions there and you'll usually get faster answers if you do.
    Charlotte

Posting Permissions

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