db connection and select statement 3

[sfunk]

Hello,

Using Microsoft ActiveX Data Objects 2.5 Library.

In a Sub I would like to:

1. Open a db connection.

2. Select statement:
"Select email_address From Email_Notification"

3. Loop through and put each email address into a single string separated by a comma and a space. Assign the string to a variable.

4. Close the connection

Thank you for your help.

Sincerely,
Steve

 

[vb5prgrmr]

To assemble the string you could use

Do while RecordSet.Eof
variable = variable & ","
RecordSet.MoveNext
Loop

 

[vb5prgrmr]

oops! sorry!


To assemble the string you could use

Do while RecordSet.Eof
variable = variable & RecordSet.Field("email_address&quot ","
RecordSet.MoveNext
Loop

 

[zemp]

What database are you using? Do you want to use a DSN?

1. To Open a connection to MS Access 2000 on a database named 'Test', located in the same folder as the application.

Private CONN As New ADODB.Connection
With CONN
.CursorLocation = adUseClient
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb ;Persist Security Info=False"
.Open
End With

Same connection using a DSN.

Private CONN As New ADODB.Connection
With CONN
.CursorLocation = adUseClient
.ConnectionString = "Provider=MSDASQL;DSN=mydsn"
.Open
End With

2. "Select [email_address] From [Email_Notification];"

3. Dim rs As New ADODB.Recordset
Dim strSQL As String, strEmails as string

strSQL = "Select [email_address] From [Email_Notification];"
rs.Open strSQL, CONN
rs.MoveFirst
Do
strEmails = strEmails & rs![email_address]
rs.MoveNext
Loop Until rs.EOF
rs.close
set rs = Nothing

4. CONN.Close
Set CONN = Nothing

Thanks and Good Luck!

zemp

 

[lovell811]

This should work:

Public mcnn As ADODB.Connection
Private dbs As ADODB.Recordset

Set mcnn = New ADODB.Connection
mcnn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = \\locationofdatabase\nameofdb.mdb"
mcnn.Open
Set dbs = New ADODB.Recordset
dbs.CursorType = adOpenKeyset
dbs.LockType = adLockOptimistic
dbs.Source = "SELECT * FROM (nameoftable)"
Set dbs.ActiveConnection = mcnn
dbs.Open
with dbs
.addnew
!fieldname = data want to save
.update
end with
dbs.close

Joe

 

[sfunk]

Thank you all!!!! I tried each one (for learning purposes) They all worked great

Steve