Hello All and thanks for the help in advance -

The module that I am working on is meant to update an Access db by keying off of multiple fields in an excel sheet that will be variable. I am fine with the db connection and creating a sql string in VBA though I do not know the best way to either loop/execute a varying number of similar update statements. I have included a copy of the code that I have been working with below (some parts of my experiments are included) Thanks and please let me know if i need provide any futher information.



Sub UpdateSomeRecordsADO()
Dim cnn As ADODB.Connection
Dim UpdCommand As ADODB.Command
Dim dbstrg As String
Dim UpdStrg As String

    dbstrg = ""
    dbstrg = dbstrg & "O:\AP\2008\testdb.mdb"
' dbstrg = dbstrg & Cells(2, 12)
' dbstrg = dbstrg & "\North American "
' dbstrg = dbstrg & Cells(2, 11)
' dbstrg = dbstrg & ".mdb"

    '' Open the connection.
    Set cnn = New ADODB.Connection
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cnn.Open dbstrg

    '' Set the command text.
    Set UpdCommand = New ADODB.Command
    Set UpdCommand.ActiveConnection = cnn

    '' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    UpdStrg = ""
    UpdStrg = UpdStrg & "UPDATE [Sheet1] SET [Paid] = 'Confirmed' WHERE [Acct] = 00003 AND [T/D] = 20080512 AND [B/S] = 1 AND [Price] = 1388.25"

' vtSql = vtSql & " UPDATE " & "Sheet1"
' vtSql = vtSql & " SET Salaryx= 24680"
' vtSql = vtSql & " WHERE Namex='Jennings'"

    With UpdCommand
        .CommandText = UpdStrg
        .CommandType = adCmdText
        .Execute
    End With
' Loop
'Range("Q2").Select

 ' Do
 ' UpdCommand.Execute
 ' ActiveCell.Offset(1, 0).Select
 ' Loop Until IsEmpty(ActiveCell.Offset(0, -16))


    '' Close the connections and clean up.
    cnn.Close
    Set UpdCommand = Nothing
    Set cnn = Nothing
End Sub

Hi aabnormal,
So you wish to extract data from Excel and enter it into an MS Access database. The database connection code is fine. I can see you wish to extract it from within an MS Excel spreadsheet using the Range, however it's not clear what cells you wish to extract it from.
If you write what you are trying to do in pseudo code i will post the actual VBA code implementation.

Jack

Courtesy of http://www.OfficeSoftworks.co.uk
and http://www.OfficeSoftworks.com