Adodb

[Avatar]

Greetings,

I can't see what i'm doing wrong here. The Update statement highlighted in red is the problem. The delete statement directly above it is my temporary workaround until i can get the update fixed.

Public Const DatabasePath As String = ""
Public Const DBName As String = ""
Public Const DatabaseDIR As String = ""
Const strPassword As String = ""

Sub UploadDataTo_DB_ADO()
 Dim Conn As New ADODB.Connection
 Dim Rst As New ADODB.Recordset
 Dim MySQL As String
 Dim AccessConnect As String
 Dim dtQueryDate As Date
 dtQueryDate = Format(Sheet1.Range("Date") - (Weekday(Format(Sheet1.Range("Date"), "YYYY/MMMM/DD"), vbMonday) - 1), "YYYY/MMMM/DD")
 
 AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                 "Dbq=" & DBName & ";" & _
                 "DefaultDir=" & DatabaseDIR & ";" & _
                 "Uid=Admin;Pwd=" & strPassword & ";"
 Conn.Open AccessConnect
 
 'Some dates STILL swap Month/Day around unless i put the format in the SQL line even though i've previously formatted it.
 MySQL = _
  "SELECT tbl_HistoricRewardScore.Date, tbl_HistoricRewardScore.Deleted " & _
  "FROM tbl_HistoricRewardScore " & _
  "WHERE (((tbl_HistoricRewardScore.Date)=#" & Format(dtQueryDate, "YYYY/MMMM/DD") & "#) AND ((tbl_HistoricRewardScore.Deleted)=False));"
  
 Set Rst = Conn.Execute(MySQL)
 
 If Not Rst.EOF Then
  If MsgBox("Date already entered. Overwrite?", vbOKCancel, "Existing Record Detected") = vbOK Then
   MySQL = _
    "Delete tbl_HistoricRewardScore.Date " & _
    "FROM tbl_HistoricRewardScore " & _
    "WHERE (((tbl_HistoricRewardScore.Date)=#" & Format(dtQueryDate, "YYYY/MMMM/DD") & "#));"
   '******************************************************************************'
   'I can't see what i'm doing wrong here..?? Delete works, Update does not..??   '
   'EDIT BY: GW                                                                   '
   'DATE/TIME: 16/02/2011 00:35:00                                                '
   '******************************************************************************'
   MySQL = _
    "UPDATE tbl_HistoricRewardScore SET tbl_HistoricRewardScore.Deleted = True, tbl_HistoricRewardScore.DeletedBy = " & fGetUserName & ", tbl_HistoricRewardScore.DeletedDateTime = #" & Format(Now(), "YYYY/MMMM/DD HH:MM:SS") & "# " & _
    "WHERE (((tbl_HistoricRewardScore.Date)=#" & Format(dtQueryDate, "YYYY/MMMM/DD") & "#) AND ((tbl_HistoricRewardScore.Deleted)=False));"
   'MsgBox MySQL
   '*******************************************************************************
   Set Rst = Conn.Execute(MySQL)
  Else
   Exit Sub
  End If
 End If
 
 'Is there a more efficent way of doing this?
 Set Rst = New ADODB.Recordset
 Rst.Open "tbl_HistoricRewardScore", Conn, adOpenKeyset, adLockOptimistic, adCmdTable
 With Rst
  .AddNew
   !Date = Format(dtQueryDate, "YYYY/MMMM/DD") 'Date
   !TransferInPercent = Sheet1.Range("TransIn") 'TransferInPercent
   !TransferOutPercent = Sheet1.Range("TransOut") 'TransferOutPercent
   !eISAPercent = Sheet1.Range("eISA") 'eISAPercent
   !FRISAPercent = Sheet1.Range("FRISA") 'FRISAPercent
   !ConsolidationPercent = Sheet1.Range("Consol") 'ConsolidationPercent
   !ComplaintsStage1Percent = Sheet1.Range("Complaints") 'ComplaintsStage1Percent
   !HelpdeskPercent = Sheet1.Range("HelpDesk") 'HelpdeskPercent
   !QualityCheckingPercent = Sheet1.Range("Quality") 'QualityCheckingPercent
   !OverallRewardPercent = Sheet1.Range("OLA") 'OverallRewardPercent
   !UpdatedBy = fGetUserName 'UpdatedBy
   !UpdatedDateTime = Format(Now(), "YYYY/MMMM/DD HH:MM:SS") 'UpdatedDateTime
  .Update
 End With
 
 Rst.Close
 Conn.Close
 Set Rst = Nothing
 Set Conn = Nothing
 
End Sub

I get the following error on the "Set Rst = Conn.Execute(MySQL)" line:
---
Run-time error '-2147217904 (80040e10)':

[Microsoft][ODBC Microsoft Access Drives] Too few parameters.
Expected 1.
---

Anyone able to lend a hand with this one? - Google’s not particularly useful on this one.

 

[Avatar]

Hrm.. not sure how to edit my original post..?

FYI the Update Statement generated is:

UPDATE tbl_HistoricRewardScore SET tbl_HistoricRewardScore.Deleted = True, tbl_HistoricRewardScore.DeletedBy = "P83672", tbl_HistoricRewardScore.DeletedDateTime = #2011/February/21 10:59:56# WHERE (((tbl_HistoricRewardScore.Date)=#2011/February/21#) AND ((tbl_HistoricRewardScore.Deleted)=False));

This works if i drop it into Access as is.

 

[Ruddles]

Unless fGetUserName returns a string complete with its own quotes, this:-

   [COLOR=black]MySQL = _[/COLOR]
[COLOR=black]   "UPDATE tbl_HistoricRewardScore SET tbl_HistoricRewardScore.Deleted = True, tbl_HistoricRewardScore.DeletedBy = [B]"[/B] & fGetUserName & [B]"[/B], tbl_HistoricRewardScore.DeletedDateTime = #" & Format(Now(), "YYYY/MMMM/DD HH:MM:SS") & "# " & _[/COLOR]
[COLOR=black]   "WHERE (((tbl_HistoricRewardScore.Date)=#" & Format(dtQueryDate, "YYYY/MMMM/DD") & "#) AND ((tbl_HistoricRewardScore.Deleted)=False));"[/COLOR]

needs to be this:-

[COLOR=black]   MySQL = _[/COLOR]
[COLOR=black]   "UPDATE tbl_HistoricRewardScore SET tbl_HistoricRewardScore.Deleted = True, tbl_HistoricRewardScore.DeletedBy = [B][COLOR=red]'[/COLOR]"[/B] & fGetUserName & [B]"[COLOR=red]'[/COLOR][/B], tbl_HistoricRewardScore.DeletedDateTime = #" & Format(Now(), "YYYY/MMMM/DD HH:MM:SS") & "# " & _
    "WHERE (((tbl_HistoricRewardScore.Date)=#" & Format(dtQueryDate, "YYYY/MMMM/DD") & "#) AND ((tbl_HistoricRewardScore.Deleted)=False));"
[/COLOR]

(It's a bit wide - scroll right a little.)

I think you're generating:-

tbl_HistoricRewardScore.DeletedBy = P83672,

whereas you need to be generating:-

tbl_HistoricRewardScore.DeletedBy = [B][COLOR=#ff0000]'[/COLOR][/B]P83672[B][COLOR=#ff0000]'[/COLOR][/B],

because DeletedBy is a text field.

 

[Avatar]

Thanks for the reply.

Indeed it does return a string without quotes. I noticed just after making the original post (which i can't edit irritatingly) that i had removed the quotes by accident. However i was using double quotes (" ") not singles (' '). I wasn't aware it had to be singles...!

I was actually generating:

tbl_HistoricRewardScore.DeletedBy = "P83672",

I've now amended it to be single quotes as you indicated and all is fine and dandy!

Thanks!

 

[Ruddles]

You can use double quotes or single quotes in a SQL string...

... however...

... if you're building the SQL string in VBA, you have to double up the double quotes to produce a single one in the final string(IYSWIM).

To avoid the confusion, use single quotes when they're part of the SQL and just use the double quotes to start and end the string assignment in VBA.

Try this:-

msgbox "The word ""hello"" is in quotes."
msgbox """This whole sentence is in quotes."""