Loop with values of a query field

[armstrong722]

I'm trying to email a bunch of individuals but I need to send them separate emails. I have the email part working but can't figure out how to loop through all the email addresses found in a query.

thanks
Armstrong

 

[jhall156]

You need a recordset. In your vba module window go to Tools|References and find Microsoft DAO and select it.

Then you can use DAO recordsets. Check out the online help. JHall

 

[armstrong722]

After delving in a bit more. I have another question(s)

What about using ODBC versus DAO?

Can you use both at the same time?
What are the benefits of one over the other?

Is it easier to manipulate with one or the other?

thanks for your help

 

[cmmrfrds]

DAO is a direct interface to the Jet Engine which is the database engine for Access. ODBC is a standardized interface to connect to other database products. There are hundreds of ODBC drivers for different databases and file systems. Because of ODBC you can create an Access front to most of these data sources, or from various front end products you can use Access as the database. It is sort of like a gateway where any front end product can go to any database or file system. This is a generalized way to interface with data, but not necessarily the most efficient. Many front end products will have direct drivers to various databases.

ADO, which is the default connection for an Access project has superceded both DAO and ODBC. ADO is basically the libraries for OLEDB and there are many OLEDB providers. Database products have or are developing OLEDB providers for their products. Many/most Microsoft data sources including Access have OLEDB providers. The OLEDB provider for Access is a direct connection to the Jet Engine much like DAO. The nice thing about ADO is the syntax will look the same whether you are developing in Access, ASP, VB, or any other front end product that supports ADO.

Of course, DAO and ODBC are still available but aare not being upgraded to the same degree as ADO and OLEDB.

 

[armstrong722]

cmmrfrds thanks !

Does ADO have to be connected or installed like DAO and ODBC the help files are less than adequate in explaining this. As far as examples and syntax are concerned I am looking for a source that is more robust than the online help which I find difficult to decipher.

And I guess my last question goes back to my first.
I'm trying to read and loop thru the fields in a query that has already been set up. Where in the help can I go to find out how to start up ADO and then write a loop, extracting one record at a time and using that as a string for my email. Do I need to make an array and place this recordset into it and use that to read from my loop? Its just very hazy for me. I know the basics about programming and I realize this will probably be in 3 or 4 different places but I can't put it all together.

thanks for your patience and help

 

[armstrong722]

Well I was miraculously successful

For those in the same boat as me here's what I had to do

From the visual basic editor got to tools and then reference
add Microsoft DAO 3.6
Microsoft Outlook 9.0
Microsoft Access 9.0 Object Library

Im not exactly sure which one of these makes it possible I believe the Access Library anyways
I do not think it hurts you to add all three.
Maybe someone would care to comment

I built a query to get the email addresses I was look for.

I created a button on a form and then right clicked on it
and hit build event then I chose code builder and added the following code to it.



Private Sub Command4_Click()
On Error GoTo email_minor_league_tryouts_Err

Dim Db As Database
Set Db = CurrentDb

'"BroadCast Email List to All" this is the name of my query
'But do this individually so only one address per email

Set Rst = Db.OpenRecordset("BroadCast Email List to All", dbOpenDynaset)

'do the loop until all records are done
While Not Rst.EOF
'email is the field name in the query "BroadCast Email List to All"
EmailList = Rst!email
'"Report1" is the name of the txt file being attached
'EmailList holds the individual email address
'etc
'DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]
DoCmd.SendObject acReport, "Report1", "MS-DOSText(*.txt)", EmailList, "", "", "Minor League Tryouts", "Minor League Tryouts will be Saturday March 23 from 9-11am Please bring a glove.", False, ""
'go to the next record in the query
Rst.MoveNext

Wend


email_minor_league_tryouts_Exit:
Exit Sub

email_minor_league_tryouts_Err:
MsgBox Error$
Resume email_minor_league_tryouts_Exit
End Sub





the dang thing works!!!!
its like magic when this stuff happens
armstrong

ps i added the type of comments I need as a rookie programmer im not sure if this forum will add enters on the line wraps if the line is to long for the window, so please be careful fellow newbies...

 

[jhall156]

It's the DAO reference that allows you to use the recordset code you have in there.
You're not using any outlook objects.
Since you're referencing it, you should check out the Outlook object hierarchy in the outlook help files under Advanced Customization|Microsoft Outlook Visual Basic Reference|Microsoft Outlook Objects

You'll dig it the most. It's a little more difficult starting out but very fun. JHall

 

[rccline]

What's the disadvantage of using ADO & SQL as opposed to DAO 3.6?

Any suggestions as to how this project could be accomplished in ADO?

Thansk

Robert

 

[cmmrfrds]

Generally speaking, ADO and DAO are 2 different libraries that allow you to work with data objects, while SQL is the language of relational databases. Access like SQL Server and Oracle and many others are relational databases, the database produces a recordset which is where DAO and ADO come in to work with the resultant recordset. As I said this is very general but the main comparison is between the 2 libraries since they do comparable things with data objects. This is a source of confusion since many of the objects from both libraries have the same name, such as, "recordset". Access can work equally well with both libraries and in the same program, but the objects must be expicitly defined by prefixing with the library name, i.e. DAO.RecordSet or ADODB.Recordset.

Both libraries have advantages and disadvantages. The main advantage of DAO is that is optimized for the Access database and is a good choice for working with the Access database and some other Microsoft products. ADO is more generalized to work with multiple data sources. With ADO you can create recordset objects on non-relational data sources as well as relational data sources and how you work with the data objects is the same. Also, ADO is a good choice for connecting an Access front end to an SQL Server database. It eliminates the need to use ODBC, which was Microsoft's first product to link an Access front end to databases other than Access.

In my opinion, if speed and efficiency of working with the Access database is a primary requirement, then DAO. After that the advantages starts to shift to ADO. I have used both libraries and sometimes in the same application. I was able to leverage my ADO knowledge when I started coding ASP programs (web pages), since the ADO objects are the same as what I learned in Access. The best way to make an evaluation is to list all your requirements and do a side-by-side comparison. This was very general but hopefully gives you some background to make a decision. Definitely keep reading and soliciting opinions and defining your requirements for the application then it will be easier to make a decision.

This link shows syntax differences/similarity between DAO and ADO.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp

On your question of setting up ADO on the example in this post, the difference is in the syntax of setting up the recordset. The rest remains the same. Check the above link to see how to setup an ADO recordset. If you still have questions or problems then post the code that does not work correctly for you under a topic identifying the problem and somebody on the Forum will help you further.

 

[rccline]

Thank you for the clarification and te detailed reply. When I see code, I'm not sure if I'm looking at ADO or DAO.

Above a database was declared and a record set was declared.

In the following example, we have a rs, and a db set.
I'm guessing this is the ADO version. Neither one will work for me.

Robert


Private Sub Email_Click()
Set Db = CurrentDb
SQL = "SELECT * FROM dbo_EMAIL;"
Set rs = Db.OpenRecordset(SQL)


rs.MoveFirst

Do While rs.EOF = False
EMAIL = EMAIL & rs!EMAIL & ";"
rs.MoveNext
Loop


Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("dbo_EMAIL&quot


With rs
Do While Not .EOF
EMAIL = EMAIL & IIf(Len(EMAIL) > 0, ";", "&quot & rs!EMAIL
.MoveNext
Loop
End With

 

[cmmrfrds]

dim db as DAO.Database
Set Db = CurrentDb '- this is DAO

This may or may not be ADO depending on what references are checked.
Dim rs As Recordset '- instead
Dim rs As ADODB.Recordset OR rs As DAO.Recordset

While in vba code go up to tools then references and check that the reference is set for the library you are using. There may be several libraries of the same type availabe - try the latest.

Microsoft DAO 3.6 object library '- could be 3.5??
Microsoft ActiveX data objects 2.6 '- could be 2.5??

In your case (assuming access 2000), the recordset is ADO and the method to open the recordset is DAO. Can't work. Also, in ADO need to create an instance. i.e. New keyword.

Dim rs As New ADODB.Recordset

For ADO use .Open
rs.Open "your sql or table"

Example of ADO setup.
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As ADODB.Recordset, connString As String
Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

sql1 = "select * from dbo.Employees "

'-- only the first parm is required others refine the resultset returned or identify the connection.
rs.Open sql1, cn, adOpenStatic, adLockOptimistic
Debug.Print rs.RecordCount
Debug.Print rs.EOF

 

[rccline]

Thank you for the clarification and the detailed reply. When I see code, I'm not sure if I'm looking at ADO or DAO.

I found a reference at MSDN.Microsoft.com that carries your example further.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp

This helps not only compare the differences between ADO and DAO, but when they cite the examples, the example refers to fundamentals like "file name" or "table name." This is very helpful for it helps a novice like me for it relates to more concrete examples I can identify. I am often wondering what a particular string is refering to if it is not clearly identified; is this a field, a table name, an abstract string to name something?? Then I have to substitute within the string the different objects I think it could be. As a novice, even the syntax is a challenge.

Thank you again for your assistance.

Robert