vba mysql 返回字段,使用VBA中的Recordset从查询返回字段值

I am trying to extract the values from a query using VBA.

Specifically, I want to concatenate all the values of the "rosEmail"

field from query "qselRosterEmailList" into one string (strEmails). I

can get one record''s result by using the DLookup fuction, of course,

but I want to get every record''s value. To do this, I believe I need

to build a recordset, but I do not know how.

I''ve read about doing it using DAO in Access 97, but I am using Access

2002. Also, I do not want to register any extra libraries, since I''ll

be sharing the Access database with others and do not want those users

to have to register libraries as well.

The final result should be something like:

strEmails = "pe*****@email.com, pe*****@email.com, pe*****@email.com,

pe*****@email.com"

Thanks for your help,

David

解决方案Unfortunately, this code does not work. I get an error saying

"User-defined type not defined" and it doesn''t go farther than the

first line of code. Although I''m not sure, I think this is because

it''s DAO and not ADO. You wrote "Oh, so convert it to ADO. Or use late

binding" - can you elaborate on how to do this? I don''t know how to

return the Recordset in ADO.

Also, where do I define the query that feeds this data? (strQuery =

qselRosterEmailList or something like that)

Thanks

bdt513 wrote:I am trying to extract the values from a query using VBA.

Specifically, I want to concatenate all the values of the "rosEmail"

field from query "qselRosterEmailList" into one string (strEmails).

I can get one record''s result by using the DLookup fuction, of course,

but I want to get every record''s value. To do this, I believe I need

to build a recordset, but I do not know how.

I''ve read about doing it using DAO in Access 97, but I am using

Access 2002. Also, I do not want to register any extra libraries, since

I''ll be sharing the Access database with others and do not want those

users to have to register libraries as well.

The final result should be something like:

strEmails = "pe*****@email.com, pe*****@email.com, pe*****@email.com,

pe*****@email.com"

Thanks for your help,

David

Umm... where''s the code?

You basically declare a string variable and then inside looping through

the records, you append each value to the string.

This is crap, but it works... assumes you get your info from a query...

Option Compare Database

Public Function AddressList(ByVal strQuery As String) As String

Dim db As DAO.Database

Dim qd As DAO.QueryDef

Dim rs As DAO.Recordset

Dim strList As String

Set db = DBEngine(0)(0)

Set qd = db.QueryDefs(strQuery)

Set rs = qd.OpenRecordset

Do Until rs.EOF

strList = strList & ", " & rs.Fields(2).Value

rs.MoveNext

Loop

AddressList = Right

(strList, Len(strList) - 2)

rs.Close

Set rs = Nothing

Set qd = Nothing

Set db = Nothing

End Function

query:

SELECT tblPeople.FirstName, tblPeople.LastName, Left