There are a variety of ways that you can add a record to a database, each of which has advantages and disadvantages.
In general, you should use whichever method is appropriate to your existing code and methods - so if you already have a recordset connected to the correct table, feel free to use the recordset to add the data.
The examples here are for ADO, if you are using DAO then only the first two methods are relevant (but you must use a different object for .Execute).
In each of the examples below we will add a single record to a table (called test), this table contains 4 fields (a, b, c, and d), each of a different data type so that you can see the code needed for each type. The values we will add have been defined like this:
VB Code:
Private Const Data1 As String = "dee-u sample" ' text field
Private Const Data2 As Boolean = True ' boolean field
Private Const Data3 As Integer = 1000 ' numeric field
Private Const Data4 As Date = #14/1/2006# ' date/time field
note that in the examples below you can use values or variables instead (or values of controls, such as Text1.Text) in place of Data1, Data2, etc.
Method 1: Using the AddNew method of the recordset object.
If you already have a Recordset connected to the appropriate table, then it is quite easy to use the Recordset to add the data.
Note that for this to work, the Recordset must be updatable - which means that it must be created using the "recordset.Open" method (rather than "connection.Execute"), and must have a lock type set to something other than adLockReadOnly (this can be set as a parameter of Open). Also, some database systems will not allow you to add data if you have fields from more than one table in your Recordset.
Note that if you do not have a appropriate Recordset open already, it is quicker to use one of the other methods.
VB Code:
'replace adoRecordset with the name of your Recordset object
With adoRecordset
.AddNew
.Fields("a").Value = Data1
.Fields("b").Value = Data2
.Fields("c").Value = Data3
.Fields("d").Value = Data4
.Update
End With
Method 2: Using an SQL Insert statement, via a Connection object
If you do not have an appropriate Recordset available, but do have a Connection linked to the right database, you can use an Insert statement to add the new record.
One downside of this method is that you need to understand a little SQL, but the following example should be enough for you to modify to your needs.
Another problem is that if your data contains any single quote characters it will stop this method working, but this can be fixed quite easily (see How do I put the ' character into an SQL string?).
VB Code:
Dim strSQL As String
strSQL = "INSERT INTO test (a,b,c,d)" & _
"VALUES('" & Data1 & "'," & _
Data2 & "," & _
Data3 & "," & _
"#" & Data4 & "#)"
'Replace adoConnection with the name of your connection object
'note: the last two arguments used for Execute here makes the execution of the command faster
Note that the # signs are used around dates for an Access database, for other database systems use single quotes ( ' ) instead.
Method 3: Using the execute method of the Command object and parameters
Using the Command object is basically an alternative to using the Execute method of the Connection object, however it is slightly faster and does not have issues with quotes in your data.
This method is even faster for adding multiple records, as you can just replace the values in the Parameters, instead of creating new ones for each record you are adding.
VB Code:
Dim strSQL As String
strSQL = "INSERT INTO test (a,b,c,d) VALUES(?,?,?,?)"
Dim adoCommand As ADODB.Command
Set adoCommand = New ADODB.Command
With adoCommand
'Replace adoConnection with the name of your connection object
Method 4: Passing values to a pre-defined action query (Access) or Stored Procedure (other database systems)
This method uses similar code to the previous example, however most the work is now done on the database instead of in your program. This means that you can you can add extra rules to your query/SP that the program does not need to implement - which is useful if you are likely to work with the same database in multiple programs.
For database systems which use a separate server (such as SQL Server or MySQL) this method can also be much faster, as they not only have better hardware than desktop PC's, but the database system can optimise the methods which are used behind the scenes.
The disadvantages are that you need to know how to make the query/SP, and that you cannot see what is happening inside it while you debug your code (but this is arguably a good thing!).
VB Code:
Dim adoCommand As ADODB.Command
Set adoCommand = New ADODB.Command
With adoCommand
'Replace adoConnection with the name of your connection object
.ActiveConnection = adoConnection
.CommandType = adCmdStoredProc 'since it is a Query/SP