Add Records to a Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I get this SQL to add to a table

Option Compare Database
Sub OpenDataBaseConnection()

Dim objConn As ADODB.Connection
Dim objRST As ADODB.Recordset
Dim strSQL As String
Dim strConn As String

'Create the ADO Connection and RecordSet Objects

Set objConn = CreateObject("ADODB.Connection")
Set objRST = CreateObject("ADODB.RecordSet")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Documents and Settings\Mike Jones\Desktop\" _
& "Payroll_1.2.mdb;Persist Security Info=False"
strSQL = "Select * from tblRegions;"

objConn.Open (strConn)

objRST.Open strSQL, objConn, adOpenForwardOnly, adLockBatchOptimistic
objRST.MoveFirst

While Not objRST.EOF
Debug.Print objRST.Fields("RegionID")
Debug.Print objRST.Fields("RegionName")
Debug.Print objRST.Fields("EmployeeID")
objRST.MoveNext

Wend
objRST.Close
objConn.Close

Set objRST = Nothing
Set objConn = Nothing

End Sub
 
Why not save yourself a little trouble and look up AddNew in the help?

Here's an example of adding new records using ADO... although there
are several other ways to do it... (using a stored procedure, using a
command object... ) This one is probably the least efficient.

Option Compare Database
Option Explicit

Public Sub AddRecords()
Dim objConn As ADODB.Connection
Dim objRST As ADODB.Recordset
Dim strSQL As String
Dim strConn As String

'Create the ADO Connection and RecordSet Objects

Set objConn = CreateObject("ADODB.Connection")
Set objRST = CreateObject("ADODB.RecordSet")

strConn = CurrentProject.Connection
strSQL = "Select * from Invoice;"

objConn.Open (strConn)

'objRST.Open strSQL, objConn, adOpenDynamic, adLockBatchOptimistic
objRST.Open strSQL, objConn, adOpenKeyset, adLockOptimistic

'objRST.MoveFirst

With objRST
.AddNew
.Fields("InvoiceNo") = 2000
.Fields("Customer") = 1
.Fields("InvoiceDate") = Date
.Fields("Markup") = 0.02
.Fields("CommissionRate") = 0.04
.Update
End With

objRST.Close
objConn.Close

Set objRST = Nothing
Set objConn = Nothing
End Sub
 
Make your query an append query. For example if you wanna add to table
destination the records of table tblregions with RegionID=3:
strsql="INSERT INTO destination ( Fld1, Fld2,...(names of fields to append
data in table destination) ) SELECT tblregions.RegionID,
tblregions.RegionName ... (names of the fields in the source table. The
number of fields must be the same of the fields to which you append) FROM
tblregions where RegionID=3"

HTH Paolo
 
Back
Top