Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updated Question - Updating an SQL Database
Greetings!
Thanks to Dave Patrick I have made progress but I am still having difficulties. Using the script below I can only update the first record in the SQL DB from an Excel spreadsheet.. I have added a Unique Record as the primary key in the SQL DB but still not luck. What am I missing? If I use the rs1.AddNew command I add records to the DB instead of updating. Thanks Dave *****Start EXCEL VBA Code***** Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim strsql1 As String, strconn Dim i As Integer i = 2 LastRow = Cells(65536, 1).End(xlUp).Row strconn = "provider=msdaSQL.1;Persist Security Info=False;user id=sa;Data source=AdHocRequest;Initial Catalog=AdHocRequest" strsql1 = "Select * from ASNUPDATE" Set cnn = New ADODB.Connection Set rs1 = New ADODB.Recordset cnn.Open strconn rs1.Open strsql1, cnn, adOpenDynamic, adLockOptimistic For i = 2 To LastRow ' rs1.AddNew ' rs1.Fields.Item("UniqueRec").Value = Sheets("Sheet1").Range("A" & i) ' rs1.Fields.Item("timewritten").Value = Sheets("Sheet1").Range("B" & i) ' rs1.Fields.Item("LogFile").Value = Sheets("Sheet1").Range("C" & i) ' rs1.Fields.Item("computername").Value = Sheets("Sheet1").Range("D" & i) ' rs1.Fields.Item("recordnumber").Value = Sheets("Sheet1").Range("E" & i) ' rs1.Fields.Item("Category").Value = Sheets("Sheet1").Range("F" & i) ' rs1.Fields.Item("eventtype").Value = Sheets("Sheet1").Range("G" & i) ' rs1.Fields.Item("eventcode").Value = Sheets("Sheet1").Range("H" & i) ' rs1.Fields.Item("SourceName").Value = Sheets("Sheet1").Range("I" & i) ' rs1.Fields.Item("user1").Value = Sheets("Sheet1").Range("J" & i) ' rs1.Fields.Item("Message").Value = Sheets("Sheet1").Range("K" & i) rs1.Fields.Item("ReviewDate").Value = Sheets("Sheet1").Range("L" & i) rs1.Fields.Item("SysAdmin").Value = Sheets("Sheet1").Range("M" & i) rs1.Fields.Item("Comments").Value = Sheets("Sheet1").Range("N" & i) i = i + 1 rs1.Update Next rs1.Close cnn.Close +++++++From Orginal Post++++++++ I have an Excel Spreadsheet that is populated from a SQL Database. There are several fields to be updated on a daily basis. I am looking for help on the code necessary to write the changes back to the SQL database. I used some ADODB connections strings in other script, but cannot figure out in Excel VBA. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database price updating | Excel Worksheet Functions | |||
database that can be updated daily | Excel Worksheet Functions | |||
Updating or merging files, same file, they were updated independan | Excel Discussion (Misc queries) | |||
Updating a database table | Excel Programming | |||
Updating a database query from XL | Excel Programming |