Hello, I need help writing a sim

[sfunk]

Hello,

I need help writing a simple Sub or a Function to perform an Insert any time it is called. When I call the Sub or Function I want to pass it a number (ie. 398594) that I have in a variable called numCount. In the Sub or Fun, I would like to set the database connection, perform the insert, and close the connection and exit the Sub. I don't know how to do this programmatically. So you have the correct information from my database I will list the
details.

The DSN name is DSNCount.
The Table is tblCount.
The Field is Count.
The value that is passed to it when it is called is numCount.

Thank you,
Steve

 

[sunaj]

Hi,

It should look something like:

-----------------------------------------------------------
Dim Conn as Connection

Set conn = new connection
Conn.Open "DSN=DSNCount;Uid=myUsername;Pwd=myPassword;"
Conn.Execute "INSERT INTO tblCount(Count) VALUES (" & cstr(numCount) & &quot"
Conn. close
set conn = nothing
-----------------------------------------------------------

Remember to set a reference to microsoft ADO in project references.

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[lovell811]

Try this, I wasn't sure what your if your db was dsn so here it is:
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\DSNCount-(assuming that is your db).mdb"
mcnn.Open
Set dbs = New ADODB.Recordset
dbs.CursorType = adOpenKeyset
dbs.LockType = adLockOptimistic
dbs.Source = "SELECT * FROM tblCount"
Set dbs.ActiveConnection = mcnn
dbs.Open
with dbs
.addnew
!Count = numCount
.update
end with
dbs.close
mcnn.close

Joe

 

[sfunk]

Thank you that works great. My next question is:

I would like to do something similar. The steps are:

1. Open a connection.

2. Select email_address From Email_Notification

3. Loop through and put each email address into a 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

 

[lovell811]

Try this:
Public mcnn As ADODB.Connection
Private dbs As ADODB.Recordset
Dim email As String

email = ""

Set mcnn = New ADODB.Connection
mcnn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = \\locationofdatabase\DSNCount-(assuming that is your db).mdb"
mcnn.Open
Set dbs = New ADODB.Recordset
dbs.CursorType = adOpenKeyset
dbs.LockType = adLockOptimistic
dbs.Source = "SELECT * FROM Email_Notification"
Set dbs.ActiveConnection = mcnn
dbs.Open
dbs.MoveFirst
While dbs.EOF <> true
with dbs
email = email & !email_address & &quot;, &quot;
.MoveNext
end with
Wend
dbs.close
mcnn.close

Joe