Split a string for use as query parameters.

[Hobbes29]

I am looking for help with SQL / query.



I have a form that list tables in my MS Access 2007 DB. The purpose of the form is to cycle through the tables in my db and display the tables in a multiselect list box. I am only interested in the table names, because the user is going to select the tables and then using a SQL query, append the tables into a single table. Each table has identical fields.

Table names can change, but my sample DB tables are called Table1, Table2, Table3, TableN where N is some number of tables in the DB.

In my function testMultiselect_Click(), I pass all the tables the user selects to a string 'sTemp' separated by a comma in a text box. (The textbox is used for testing to make sure I am selecting the right tables.) An example of sTemp is "Table1, Table2 Table3".

I am looking for assistance in writing the SQL that passes the selected tables from sTemp to a query/sql statement that appends the tables the user selects into a single table where the single table is called "tblCombine".

testMultiselect_Click() is called from a button click.

The code I have so far is:

Private Sub testmultiselect_Click()
 
    Dim oItem As Variant
    Dim sTemp As String
    Dim iCount As Integer
    Dim SQLText As String
 
 
    iCount = 0
 
    If Me!NamesList.ItemsSelected.Count <> 0 Then
        For Each oItem In Me!NamesList.ItemsSelected
 
            If iCount = 0 Then
                 sTemp = sTemp & Me!NamesList.ItemData(oItem)
                 iCount = iCount + 1
            Else
                 sTemp = sTemp & "," & Me!NamesList.ItemData(oItem)
                 iCount = iCount + 1
 
                 'JUST TESTING THE COUNT OF TABLES
                 MsgBox "I only selected after else: " & iCount
 
                 'THIS IS WHERE I WANT TO SPLIT sTemp AND CREATE A
                 'SQL TO TAKE THE TABLES I SELECT AND APPEND THEM TO
                 'A SINGLE TABLE CALLED tblCombine.
 
             End If
         Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub 'Nothing was selected
    End If
 
    Me!mySelections.Value = sTemp
End Sub

Thanks,
Ken

[pbaldy]

I don't think you want to combine them together in a string, unless I'm missing something. Within the loop, you could append each one:

strSQL = "INSERT INTO tblCombine (Field1, Field2) SELECT Field1, Field2 FROM " & Me!NamesList.ItemData(oItem)

CurrentDb.Execute strSQL

[Hobbes29]

Paul,

Thank you!. I used this string in the code and it did exactly what I needed.

In excel I was able to fill an array with a list a strings and split the string for use and thought I could do something similar with Access but quickly learned VBA operates a little differently between access and excel.

Ken

[pbaldy]

Happy to help. You can certainly split a string in Access, but it didn't seem that you needed to here.