Solved: Return query as a concatenated string

[Touni102]

Hello VBA Express!

I have a problem, and I searched all over for the answer with no luck. So here I am.

The problem is I just started using Access and VBA, and I don't know how to store a query result - preferably a string.

My goal is I have a bunch of tables with the same format. I would like to create an SQL string that includes all of those tables so I can get a list of all of the distinct values in a certain column across all of the tables. What i've done so far to accomplish this is that I've created a query object in my database that gives me a list of all tables that I want to look through (because I have other tables that aren't related). I want to use that query in my vba code to create SQL like this "SELECT DISTINCT "Name" FROM " & <comma separated query result>

Is this possible?
Am I doing everything wrong?
Thanks for any help... I'll probably be frequenting the site to increase my VBA skills :)... I already miss C#

[CreganTur]

Welcome to the forum- it's always good to see new members.

You cannot run a SQL SELECT statement in VBA and use it to return values on its own, but you can use your SQL statement to open a recordset. The recordset would contain all of the values pulled by your statement.

The basic syntax is:
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset

'use your SQL statement to define what records to pull into the recordset
rst.Open "SQL STATEMENT", conn
rst.moveFirst '<<<Explicitly move to first record

'loop to print out value of first field index to immediate window
Do Until rst.EOF
Debug.Print rst.Fields(0).Value
rst.MoveNext
Loop

MsgBox "Records written to immediate window"

'cleanup
rst.close
conn.close
Set rst = Nothing
Set conn = Nothing

The above code was written from memory... but it should work. Let us know if we can provide any more help.

HTH:thumb

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: I forogt to mention that you need to replace SQL Statement with an actual SQL string.

[Touni102]

Thanks for the tip, but I came across a problem. I get "User-defined type not defined with ADODB.Connection. I know it has to do with a Reference, so what must I include?

[CreganTur]

You need a reference to Microsoft ActiveX Data Objects.

Forgot to add that.

[Touni102]

Somewhat worked...

After adding the reference to "Microsoft ActiveX Data Objects 2.0 Library" It runs, but with my query it doesn't produce any results. I tried a simple query and realize that it works with other queries, except the first entry is always blank. When I do my original query, it says that it's already at EOF. I tested my query and it works as an Access Query Object, but when i copy the SQL from it into this VBA code, it doesn't work.

Here is what I have to output the string i want:

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim tblstring As String

Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset

'use your SQL statement to define what records to pull into the recordset
rst.Open "<SQL>", conn
rst.MoveFirst '<<<Explicitly move to first record

tblstring = "["

'loop to print out value of first field index to immediate window
Do Until rst.EOF
tblstring = tblstring & "],[" & rst.Fields(0).Value
rst.MoveNext
Loop

tblstring = tblstring & "]"

MsgBox tblstring

'cleanup
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing


I replaced <SQL> with the following queries.

My query to get table names:
SELECT MsysObjects.Name FROM MsysObjects WHERE (((MsysObjects.Name) Like 'PTS-*') And ((Left$([Name], 1)) <> '~') And ((Left$([Name], 4)) <> 'Msys') And ((MsysObjects.Type) = 1)) ORDER BY MsysObjects.Name

A successful test query:
"SELECT * FROM [PTS-021309];"

[hansup]

I replaced <SQL> with the following queries.

My query to get table names:
SELECT MsysObjects.Name FROM MsysObjects WHERE (((MsysObjects.Name) Like 'PTS-*') And ((Left$([Name], 1)) <> '~') And ((Left$([Name], 4)) <> 'Msys') And ((MsysObjects.Type) = 1)) ORDER BY MsysObjects.NameSince you're using the SQL statement with ADO, you must use ANSI wild card characters --- % instead of *

Also, your "Like" expression already excludes names starting with ~ or Msys, so you can simplify it as follows:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE MsysObjects.Name Like 'PTS-%'
AND MsysObjects.Type = 1
ORDER BY MsysObjects.Name



A successful test query:
"SELECT * FROM [PTS-021309];" That makes sense. That one had no wild card issues to trip you up.

Good luck,
Hans

[Touni102]

Works perfectly... thanks all


Sub UpdateSoftwareQuery()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim tblstring As String

Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset

'use your SQL statement to define what records to pull into the recordset
rst.Open "SELECT MsysObjects.Name FROM MsysObjects WHERE MsysObjects.Name Like 'PTS-%' AND MsysObjects.Type = 1 ORDER BY MsysObjects.Name", conn

rst.MoveFirst '<<<Explicitly move to first record

tblstring = "[" & rst.Fields(0).Value
rst.MoveNext

'loop to add values to string
Do Until rst.EOF
tblstring = tblstring & "],[" & rst.Fields(0).Value
rst.MoveNext
Loop

tblstring = tblstring & "]"

MsgBox tblstring

'cleanup
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing
End Sub
I also noticed a small bug in the loop, where the first bracket would always contain nothing, so added 2 lines of code to add the first entry before entering the loop.

As of now, tblstring will list the entries of the sql query (in this case, all tables that being with "PTS-") in brackets. So as far as this thread goes... mission complete.

[stanl]

seems like you could do the same thing with getstring() - .02 Stan

[Touni102]

Just tried GetString, it worked like a charm. Didn't need the loop to create the String i needed. Thanks