Insert Records from Excel Into MS SQL Server
 

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?

Insert Records from Excel Into MS SQL Server
 

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

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"John" wrote in message
...
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?

Insert Records from Excel Into MS SQL Server
 

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

-----Original Message-----
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

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"John" wrote in message
...
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?


.