data from Excel into SQL Server 2008 via VBA
- From: Doug Howell <douglasehowell@xxxxxxxxx>
- Date: Wed, 25 Nov 2009 06:19:55 -0800 (PST)
I currently have some code in an Excel 2007 workbook which takes data
out of a worksheet and creates records in two different Access 2007
tables.
I would like to place the data into two SQL Server 2008 tables instead
and am unsure on how to change the code.
SQL Server name is: MFGDATA/SCADASQL
Database name is: "Batch Raw Log"
Two table names are: "rawlog" and "mdiadd"
Current Code:
Dim db As Database, rs As Recordset, r As Long, s As Long
Set db = OpenDatabase("\\Dcc-1\dcc scada\Databases\Batch Raw
Log.accdb")
' open the database
Set rs = db.OpenRecordset("rawlog", dbOpenTable)
' all records in a table
s = 5
Do While Len(Range("C" & s).Formula) > 0
r = s ' the start row in the given raw material
Do While Len(Range("C" & r).Formula) > 0
' repeat until first empty cell in column C5+x
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("batdate") = Range("B5").Value
.Fields("prod") = Range("E2").Value
.Fields("prodlot") = Range("H2").Value
.Fields("reactor") = Range("N2").Value
.Fields("rawnum") = Range("C" & r).Value
.Fields("begin") = Range("E" & r).Value
.Fields("end") = Range("F" & r).Value
.Fields("rawlot") = Range("G" & r).Value
.Fields("rawwt") = Range("H" & r).Value
.Fields("oper1") = Range("K" & r).Value
.Fields("oper2") = Range("L" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
s = s + 4 ' next raw
Loop
rs.Close
Set rs = Nothing
Set rs = db.OpenRecordset("mdiadd", dbOpenTable)
' all records in a table
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("batdate") = Range("B5").Value
.Fields("prod") = Range("E2").Value
.Fields("prodlot") = Range("H2").Value
.Fields("reactor") = Range("N2").Value
.Fields("mdidate") = Range("B60").Value
.Fields("mditime") = Range("C60").Value
.Fields("mdisec") = Range("E60").Value
.Update ' stores the new record
End With
rs.Close
Set rs = Nothing
db.Close
Any help would be appreciated.
.
- Follow-Ups:
- Re: data from Excel into SQL Server 2008 via VBA
- From: Bob Barrows
- Re: data from Excel into SQL Server 2008 via VBA
- From: Uri Dimant
- Re: data from Excel into SQL Server 2008 via VBA
- Prev by Date: Re: sql command error
- Next by Date: Re: Unable to insert record in table
- Previous by thread: Unable to insert record in table
- Next by thread: Re: data from Excel into SQL Server 2008 via VBA
- Index(es):
Relevant Pages
|
