Insert Records from Excel Into MS SQL Server

[John]

I've been able to execute select statements to retrieve
selective data from SQL Server inside of the VBA code in
Excel. What commands are necessary to Insert records into
the same database and table?

 

[Dick Kusleika]

John

If you are using ADO, here's an example of how to add a record. You'll need
to set a reference to the ActiveX Data Objects Library under Tools -
References.

Sub AddRec()

Dim mycn As ADODB.Connection
Dim i As Integer
Dim mySQL As String
Dim stConn As String
Dim myRS As ADODB.Recordset

mySQL = "SELECT `Table 1`.number, `Table 1`.name, `Table 1`.TheDate"
mySQL = mySQL & " FROM `c:\Dick\db1`.`Table 1` `Table 1`"

stConn = "DSN=MS Access 97 Database;DBQ=c:\Dick\db1.mdb;"
stConn = stConn & "DefaultDir=c:\Dick;DriverId=281;"
stConn = stConn & "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

Set mycn = New ADODB.Connection

mycn.Open stConn

Set myRS = New ADODB.Recordset

myRS.Open mySQL, mycn, adOpenDynamic, adLockOptimistic

With myRS
.AddNew
.Fields(0).Value = 40
.Fields(1).Value = "Name"
.Fields(2).Value = Now
.Update
End With

myRS.Close
mycn.Close

End Sub

 

[John]

Thanks. I've been using ODBC but am going to give this a
try.