Updated Question - Updating an SQL Database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


.

Relevant Pages

  • How to connect to SQL from machine without domain
    ... my presumption is that script will be run from machine that not belongs to any AD domain. ... Is it possible to connect to MS SQL database on domain member server from vbscript script running on workstation in workgroup using domain user login and pass? ... Dim sServer ...
    (microsoft.public.scripting.vbscript)
  • RE: export user accounts from NT 4.0 domain
    ... ATTENTION THE SCRIPT MUST BE RUNNED FROM A COMPUTER WHERE EXCEL IS ... from the information in a Microsoft Excel spreadsheet. ... Dim strLast, strFirst, strMiddle, strPW, intRow, intCol ... On Error GoTo 0 ...
    (microsoft.public.windows.server.scripting)
  • Re: Password Expire
    ... We have one fron end Edge server in our DMZ which passes email onto two ... I used to schedule a script to run every 24 hours on my Exchange 2003 ... Dim fso, txtarray, BodyText ... Call ProcessFolder (objContainer, numDays) ...
    (microsoft.public.exchange.admin)
  • Re: Collecting output generated via one spreadsheet into a new spreasheet
    ... A quick read of the script indicates that it makes no attempt to actually write out any of the information is has found. ... 'Call Output sub ... I would hazard a guess that if the output contains only the info associated with the last record from the input file, that you are failing to increment the row counter associated with the output spreadsheet after writing to it. ... Dim strUserName, objUserDomain, objGroup, objUser, strGroupList ...
    (microsoft.public.scripting.vbscript)
  • Re: LDAP query information
    ... a "Dim" statement. ... execution of the script. ... ' Filter on computer object. ... ' Construct LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)