Writing to a database

[Spooked]

I need to be able to take data from a text box and write it back to Access using Visual Basic 6. I have been using ADO throughout the project and have been able to update existing records in the database but I`m unsure of how to create new records using the data supplied in the text boxes, etc. Any help would be appreciated..

[Ianpbaker]

Hi spooked this is nice and easy to do.

Use the following code and it should sort out your problem



Public sub AddRecords()
Dim myRecordset As ADODB.recordset
Dim myConnection As ADODB.connection
Dim strSql As String
Dim strCon As String

strCon = 'put in your connection string here

Set myConnection = New ADODB.connection
Set myRecordset = New ADODB.recordset

myConnection.Open strCon

strSql = "SELECT * FROM tblTest" ' Replace this with your table

myRecordset.Open strSql,myConnection ,adOpenDynamic

myRecordset.addnew
'replace theese with your field names and you textboxes
myRecordset("SOMEFIELD") = txtsometextbox
myRecordset("SOMEFIELD1") = txtsometextbox1
myRecordset("SOMEFIELD2") = txtsometextbox2
myRecordset("SOMEFIELD3") = txtsometextbox3
myRecordset.Update

myConnection.close

Set myConnection = Nothing
Set myRecordset = Nothing


Hope this helps

Ian

[JasonGS]

a quicker method with a little less code and a way quicker recordset transaction...

dbFields = Array("Text1", "Text2", "Text3")
dbValues = Array(Text1.Text, Text2.Text, Text3.Text)

adoRset.Open "table", adoConn, adOpenStatic, adLockOptimistic
adoRset.AddNew dbFields, dbValues
adoRset.Close

[Chris]

May be INSERT INTO statement is fastest to do this,


Dim xSQL$
Dim lngAffectedRcd&
Dim ADOConn As New ADODB.Connection
Set ADOConn = Bla...Bla...Bla...

xSQL = "INSERT INTO TblMyTable VALUES ('" & Text1.Text & "', '" & Text2.Text & "');"
ADOConn.Execute xSQL, lngAffectedRcd, adExecuteNoRecords


Assume TblMyTable only have 2 Text data fields.

[Edited by Chris on 06-02-2000 at 09:46 PM]

[Edneeis]

If the TextBoxes are mapped to the datafields already why don't you just use ADO.addnew before they type the info in the boxes (it will even clear the boxes for a new entry) and then if they change records or add again it will automatically save the info or you can have a save button and just use ADO.update in the click event