0

I've some code in Excel that updates an Access table based on if RTP_ID equals IngID, the following matches and works if they are numeric in RTP_ID: 

sSQL = "SELECT * FROM Tbl_Primary WHERE RTP_ID = " & lngID

However I would like it where RTP_ID could be a string.

I've tried: 

sSQL = "SELECT * FROM Tbl_Primary WHERE RTP_ID = '" & lngID & "'"

but that still doesn't work, any ideas?

So if RTP_ID was 1 it would work, but if it was 1A it wouldn't.

Edit- here is the code I currently have:

Application.ScreenUpdating = False    

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim MyConn
Dim lngRow As Long
Dim lngID, LR, Upd
Dim strID As String
Dim j As Long
Dim sSQL As String

LR = Range("B" & Rows.Count).End(xlUp).Row
Upd = LR - 1

lngRow = 2
Do While lngRow <= LR


strID = Cells(lngRow, 2).Value


sSQL = "SELECT * FROM Tbl_Primary WHERE RTP_ID2 = " & strID



Set cnn = New ADODB.Connection

MyConn = "Provider = Microsoft.ACE.OLEDB.12.0;" & _
"Data Source =\Work\Sites\HLAA\NEW\test\HLAA 2015 NEW.mdb"
With cnn

.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn

End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, LockType:=adLockOptimistic

.

With rst

.Fields("MonitorCapacity") = Cells(lngRow, 74).Value


 rst.Update
End With


rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

lngRow = lngRow + 1

Loop
MsgBox "You just updated " & Upd & " records"
Community
  • 1
  • 1
Calum
  • 143
  • 1
  • 3
  • 13
  • Could it be that the second SQL string is looking at MonitorID rather than RTP_ID? Other than that the SQL looks fine. I assume that lngID is a string variable so it can hold '1A'? If it's not it will throw an error, or return a 0 if you're using `On Error Resume Next`. On what you've given us I don't think it's your SQL string that's causing the error. – Darren Bartrup-Cook Oct 19 '15 at 10:20
  • Sorry that's from previously testing I've update my question it should read `RTP_ID` and my IngID is `lngID = Cells(lngRow, 1).Value` so it could be any within column A, which contains string values. – Calum Oct 19 '15 at 10:24
  • Do you use `On Error Resume Next`? At the top of your VBA code is it `Dim lngID AS String` or `Dim lngID AS Long` or `Dim lngID`? Bit of a long shot - have you tried `Cells(lngRow,1).Value2` although (I think) that will only affect currency and date value data types. When you say it doesn't work - how doesn't it work? Does it throw an error, or return incorrect results? – Darren Bartrup-Cook Oct 19 '15 at 10:58
  • If the first version works, then `RTP_ID` is a numeric field so I don't see how it could work if you pass a non-numeric value to it. – Rory Oct 19 '15 at 11:20
  • @Rory Yeah but I'm going to change it to RTP_ID2 which isn't just numerica values. – Calum Oct 19 '15 at 11:39
  • @DarrenBartrup-Cook I will update my question with all the code I am using at the moment. – Calum Oct 19 '15 at 11:40
  • Then it should work with the apostrophes, though obviously you can't use a `Long` variable type. – Rory Oct 19 '15 at 11:40
  • What do you mean with apostrophes? I've added the code im using to my question. – Calum Oct 19 '15 at 11:45

1 Answers1

1

I'd rewrite the code as below:

Dim cnn As Object
Dim lngRow As Long
Dim lngID As Long, LR As Long, Upd As Long
Dim strID As String

LR = ThisWorkbook.Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
Upd = LR - 1
lngRow = 2

Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=\Work\Sites\HLAA\NEW\test\HLAA 2015 NEW.mdb;" & _
    "Persist Security Info=False;"

Do While lngRow <= LR
    strID = ThisWorkbook.Worksheets("Sheet2").Cells(lngRow, 2).Value

    cnn.Execute "UPDATE Tbl_Primary SET MonitorCapacity = '" & _
        ThisWorkbook.Worksheets("Sheet2").Cells(lngRow, 74).Value2 & _
        "' WHERE RTP_ID2 = '" & strID & "'"

    lngRow = lngRow + 1

Loop
MsgBox "You just updated " & Upd & " records"

You may need to change the Worksheet name - when you just put Range("B" & Rows.Count) it will use whichever sheet is active at the time, so need to state the worksheet.

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • Sorry i realise that was because I didnt change it to `Monitor_Capacity` however I do get now : "No value given for one or more required parameters" – Calum Oct 19 '15 at 12:38
  • 1
    That may be due to a blank cell in your worksheet. Check the value of strID and Cells(lngRow,74) when it throws the error. You may need to update the SQL to check for those - `cnn.Execute "UPDATE Tbl_Primary SET MonitorCapacity = " & _ IIf(ThisWorkbook.Worksheets("Sheet2").Cells(lngRow, 74).Value2 = "", "Null", _ "'" & ThisWorkbook.Worksheets("Sheet2").Cells(lngRow, 74).Value2 & "'") & _ " WHERE RTP_ID2 = '" & strID & "'"` – Darren Bartrup-Cook Oct 19 '15 at 13:05
  • Hi again Darren, do you know how I can update multiple fields where `RTP_ID2 = the strID`, so `MonitorCapacity` and `MonitorSize` both get updated? – Calum Oct 20 '15 at 09:59
  • 1
    You just need to add the MonitorSize to the Update SQL: `UPDATE Tbl_Primary SET MonitorCapacity = '1', MonitorSize='1' WHERE RTP_ID2 = '2'`. http://www.w3schools.com/sql/sql_update.asp. – Darren Bartrup-Cook Oct 21 '15 at 08:34
  • It might be worth while looking at transactions if there's a possibility your updates may fail - http://stackoverflow.com/questions/1987696/rollback-multiple-sql-update-queries-in-ms-access – Darren Bartrup-Cook Oct 21 '15 at 08:38
  • Thanks Darren, you da real MVP! – Calum Oct 21 '15 at 11:17