Letting users build their own Dynamic Queries

[thechazm]

Ok so this is sort of a side fun project of mine (if there ever is such a thing) where I designed an interface that uses a treeview control to populate the table names and field names I want the users to have access to.

Then all they do is select the fields they want to include in their query and click the add selected and it adds those fields to a sub form so they can specify other options.

This part works great so far but I am having some problems with building the SQL in code that relates the fields that need to be related in a normalized database environment.

It works 100% with just a single table but if I add more than one table I don't know what the best way is to handle that.

I currently have a table build to feed the treeview where I specify the related table to the field but being able to dynamically create the SQL for this I just can't picture.

Any help or guidance is appreciated. I am attaching a picture of the screen and also my code I use to build the SQL if anyone is interested and maybe help me fill in the gaps.

Function BuildQuery(rs As DAO.Recordset)
Dim strSQL As String, strTblName As String, bSorted As Boolean, qdf As QueryDef, db As Database, rs2 As DAO.Recordset, lQueryID As Long
'On Error GoTo ErrHandler
bSorted = False
Set db = CurrentDb
rs.MoveFirst
rs.Sort = "[C Order]"
Set rs = rs.OpenRecordset
lQueryID = rs("C Query ID")
Set rs2 = db.OpenRecordset("Select * from [Custom Query Relationships Query] WHERE((([CQ ID]) = " & lQueryID & "))", dbOpenSnapshot)
'Build Initial SQL
strSQL = "Select "
' Get Field Names
Do While rs.EOF = False
    If rs.AbsolutePosition = 0 Then
        strSQL = strSQL & "[" & rs("C Field Table") & "].[" & rs("C Field Name") & "]"
    Else
        strSQL = strSQL & ", [" & rs("C Field Table") & "].[" & rs("C Field Name") & "]"
    End If
    
    rs.MoveNext
Loop
strSQL = strSQL & " From "
rs.MoveFirst
' Get Table Names
Do While rs.EOF = False
    ' Personnel INNER JOIN [Personnel Shops] ON Personnel.ID = [Personnel Shops].[Personnel ID]
    If strtablename <> rs("C Field Table") Then
        If rs.AbsolutePosition = 0 Then
            strSQL = strSQL & "[" & rs("C Field Table") & "]"
            strtablename = rs("C Field Table")
        ElseIf strtablename <> rs("C Field Table") And rs2.EOF = False Then
            Do While rs2.EOF = False
                strSQL = strSQL & " INNER JOIN [" & rs2("Child Table") & "] ON [" & rs2("Master Table") & "].[" & rs2("Master Field") & "] = [" & rs2("Child Table") & "].[" & rs2("Child Field") & "]"
                rs2.MoveNext
            Loop
            strtablename = rs("C Field Table")
        Else
            strSQL = strSQL & ", [" & rs("C Field Table") & "]"
            strtablename = rs("C Field Table")
        End If
    End If
    rs.MoveNext
Loop
rs.MoveFirst
' Setup WHERE Statement
 
 
 
' Setup Order By
Do While rs.EOF = False
    If rs("C Sort") = True And bSorted = False Then
        bSorted = True
        strSQL = strSQL & " ORDER BY [" & rs("C Field Name") & "]"
    ElseIf rs("C Sort") = True And bSorted = True Then
        strSQL = strSQL & ", [" & rs("C Field Name") & "]"
    End If
    rs.MoveNext
Loop
strSQL = strSQL & ";"
If CurrentData.AllQueries("TempQuery").IsLoaded = True Then
    DoCmd.Close acQuery, "TempQuery", acSaveNo
End If
If ObjectExists("Query", "TempQuery") Then
    DoCmd.DeleteObject acQuery, "TempQuery"
End If
Set qdf = db.CreateQueryDef("TempQuery", strSQL)
DoCmd.OpenQuery "TempQuery", acViewNormal, acReadOnly
rs.Close
Set rs = Nothing
Exit Function
ErrHandler:
If Err.Number = 3021 Then
    rs.Close
    Set rs = Nothing
    Exit Function
Else
    MsgBox Err.Number & " - " & Err.Description
End If
rs.Close
Set rs = Nothing
    
End Function

 

[plog]

If only there was a program with a graphical user interface that allowed users to select tables to build queries with, select fields of those tables and then do other query manipulations such as ordering and grouping data.

You do realize you are essentially building Access with Access? Why not create a database with linked tables to just the ones you want them to have access to and then teach them to run SELECT queries?

 

[thechazm]

Lol I like your responce but honestly its not a practical approach. I do understand where you are coming from but the idea is that it will flat line the normalized structure without having to teach 100,000 people to learn access.

It's more cost effective to have an interface like this with or without some limitations and I believe that you may have missed what I said above right now this is just for fun so if you want to contribute then thats great but if not then no worries.

 

[thechazm]

Thanks Pat for your input but remember this is just for fun. I am planning on making it so it can read the relationships from the BE or whatever database you tie it to and having it flatten the data where needed. This is not ment for any kind of production environment... yet anyway.

The interface as it is right now is only the early stages and is just something to get it functional. The next step for the interface is dragging and dropping objects instead of using a tree view and sub interfaces. Just fun stuff guys thats all.