By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,573 Members | 903 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,573 IT Pros & Developers. It's quick & easy.

Help with rs.TableName

P: n/a
JF
I would like to automate a Union query going through the tables of my
dataset using VBA:

I have this code:

-------------------------------------------------------------------
Sub ssql()
Dim rs As Recordset
Dim ssql As String
Set rs = CurrentDb("SELECT * FROM Merge;")
rs.MoveFirst
Do While Not rs.EOF
ssql = ssql & "SELECT * FROM" & rs.TableName & "UNION"
rs.MoveNext
Loop
End Sub
----------------------------------------------------------------------------------

I have a "method or data member not found error" for rs.TableName
Thank you in advance for your help!!

May 24 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"JF" <an******@gmail.com> wrote in
news:11********************@u72g2000cwu.googlegrou ps.com:
I would like to automate a Union query going through the
tables of my dataset using VBA:

I have this code:

-------------------------------------------------------------- - ---- Sub ssql()
Dim rs As Recordset
Dim ssql As String
Set rs = CurrentDb("SELECT * FROM Merge;")
rs.MoveFirst
Do While Not rs.EOF
ssql = ssql & "SELECT * FROM" & rs.TableName & "UNION"
rs.MoveNext
Loop
End Sub
-------------------------------------------------------------- - -------------------

I have a "method or data member not found error" for
rs.TableName
Thank you in advance for your help!!

You should look at your ssql using a debug.print statement.
With the code as shown, for table1,table2 and table3, this is
what I expect you'll see

SELECT * FROMTable1UNIONSELECT * FROMTable2UNIONSELECT *
FROMTable3UNION

Which needs some spaces and has a UNION too many.
try:
ssql = ssql & "SELECT * FROM " & rs.TableName & " UNION "
--
Bob Quintal

PA is y I've altered my email address.
May 24 '06 #2

P: n/a
Perhaps you meant

rs!tablename

rs.tablename does not work in current versions of Access.

(david)

"JF" <an******@gmail.com> wrote in message
news:11********************@u72g2000cwu.googlegrou ps.com...
I would like to automate a Union query going through the tables of my
dataset using VBA:

I have this code:

-------------------------------------------------------------------
Sub ssql()
Dim rs As Recordset
Dim ssql As String
Set rs = CurrentDb("SELECT * FROM Merge;")
rs.MoveFirst
Do While Not rs.EOF
ssql = ssql & "SELECT * FROM" & rs.TableName & "UNION"
rs.MoveNext
Loop
End Sub
----------------------------------------------------------------------------------

I have a "method or data member not found error" for rs.TableName
Thank you in advance for your help!!

May 25 '06 #3

P: n/a
"JF" <an******@gmail.com> wrote in
news:11********************@u72g2000cwu.googlegrou ps.com:
I have this code:

-------------------------------------------------------------------
Sub ssql()
Dim rs As Recordset
Dim ssql As String
Set rs = CurrentDb("SELECT * FROM Merge;")
Surely the line above is:

Set rs = CurrentDB.OpenRecordset("SELECT * FROM Merge;")
rs.MoveFirst
Do While Not rs.EOF
ssql = ssql & "SELECT * FROM" & rs.TableName & "UNION"
rs.MoveNext
Loop
End Sub
-------------------------------------------------------------------
--

I have a "method or data member not found error" for rs.TableName


For DAO recordsets, you have to use the ! for field names, so if you
refer to it as rs!TableName, it should work. Also, rs("TableName")
will work. Both of those options are short for
rs.Fields("TableName").

I would also strongly recommend that you declare your recordset
variable this way:

Dim rs As DAO.Recordset

because ADO also has a recordset object.

I also never us CurrentDB() directly in the fashion you have done
it, but instead set a database variable. Of course, in all my apps I
also cache a reference to the currently opened database so I don't
have the overhead of repeatedly opening the reference.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 25 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.