Write Data to a database
 

Does anyone have example code of adding records in a database?

When I try to record a macro of the addition of database records within MS
Query, I don't get the desired results in a VBA Module. I only get the query
string.

thanks

Write Data to a database
 

Add a sub procedure to a new module and paste in the following. For this to
work you'll need an Access database named 'contacts.mdb' in a directory
named 'C:\Data\Access' The database will need one table named contacts with
two text fields; one named 'first' another named 'last' In your procedure
you'll probably need to set a reference to ADO so Tools|References and check
the box for 'Microsoft ActiveX Data Objects 2.x Library'

----------------------------------
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConnect As String, strSQL As String
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data\Access\contacts.mdb;Persist Security Info=False"

strSQL = "SELECT contacts.first, contacts.last " _
& "FROM contacts; "

Set cnn = New ADODB.Connection
cnn.Open strConnect
Set rs = New ADODB.Recordset
rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!first = "Dave"
rs!last = "Patrick"
rs.Update
rs.Close
cnn.Close
----------------------------------

(watch for line wrapping)

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Simon Shaw" wrote:
| Does anyone have example code of adding records in a database?
|
| When I try to record a macro of the addition of database records within MS
| Query, I don't get the desired results in a VBA Module. I only get the
query
| string.
|
| thanks
|

Write Data to a database
 

Simpler:

Dim cnn As Object
Set cnn = CreateObject("ADODB.Connection")
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Data\Access\contacts.mdb;" & _
"Persist Security Info=False"
cnn.Execute _
"INSERT INTO Contacts (first,last) VALUES ('Dave','Patrick')"
Jamie.

--