Saya mencoba memasukkan atau memperbarui lembar kerja eksternal menggunakan SQL dengan VBA. Di bawah kode fungsi lengkap. Kesalahan terjadi ketika saya mencoba membuka recordset dalam loop melewati kueri pemilihan sql. Bantuan apa pun akan dihargai. Saya tahu bahwa string koneksi berfungsi karena saya menggunakannya pada kode lain.

String SQL terlihat seperti ini:

select top 1 VALUENAME FROM [TradingTotals$] WHERE LOWER(VALUENAME)= LOWER('Trading') AND VALUEDATE =29/05/2020 AND CLOSINGMONTH = 'May' ORDER BY VALUEDATE DESC)

Kode Fungsi Lengkap

Public Function InsertClosingMonthTotals(ByVal CollOfTradeLogTotObj As Collection) As Boolean 

   Dim IsSuccess As Boolean
   Dim Item As TradeLogTotalsObj
   Dim Sql As String
   Dim SqlSelect As String
   Dim ConnDbString As String

   Dim TotalRecords As Long

   Dim Name As String
   Dim Value As Variant
   Dim Trading As Variant
   Dim LongShort As Variant
   Dim Therms As Variant
   Dim Valdate As Date
   Dim ClosingMonth As String

   ClosingMonth = Helper.FormatValue(Date, formatTypes.AsMonthlongOnly)


   Set Glob_Conn = New ADODB.Connection
   Set Glob_RecSet = New ADODB.Recordset

   ' Client-side cursor for correct return of record count
   Glob_RecSet.CursorLocation = adUseClient

   'Get Connection string according to the database server type
   ConnDbString = Helper.GetConnectionString(ServerTypes.Excel, Glob_FilePathForDataInput)


   'if the connection is closed then open it
   If (Glob_Conn.State = 0) Then
      Glob_Conn.Open (ConnDbString)
   End If


   For Each Item In CollOfTradeLogTotObj
      Name = Item.Name
      Value = Helper.FormatValue(Item.Value, AsNumber)
      LongShort = Helper.FormatValue(Item.LongShort, AsDecimalThreeDigits)
      Therms = Helper.FormatValue(Item.Therms, AsDecimalThreeDigits)
      Valdate = Helper.FormatValue(Item.dateTime, AsDateDisplay)

      SqlSelect = "select top 1 VALUENAME FROM [" & Glob_SheetNameTotalBooks & "$]" & _
      " WHERE LOWER(VALUENAME)= LOWER(" & "'" & Name & "'" & ")" & " AND VALUEDATE =" & Valdate & _
      " AND CLOSINGMONTH = " & "'" & ClosingMonth & "'" & _
      " ORDER BY VALUEDATE DESC)"

      Debug.Print ("SQL SELECT " & SqlSelect)
      Debug.Print ("*************************************************************")

      'open the record set
      If (Glob_RecSet.State <> 1) Then
     ' ******* ERROR ON LINE BELOW ON OPEN **********
      Glob_RecSet.Open SqlSelect, Glob_Conn, adOpenForwardOnly, adLockOptimistic
      End If

      TotalRecords = Glob_RecSet.RecordCount
      If (TotalRecords > 0) Then
      Sql = "UPDATE TradingTotals SET VALUENAME =" & "'" & Name & "'" & _
      ",VALUEDATE =" & Valdate & _
      ",VALUE =" & Value & _
      ",LONGSHORT =" & LongShort & _
      ",THERMS =" & Therms & _
      ",CLOSINGMONTH =" & "'" & ClosingMonth & "'" & _
      "  WHERE LOWER(VALUENAME) = LOWER(" & Name & ") AND VALUEDATE = " & Valdate & " AND CLOSINGMONTH =" & ClosingMonth
      Else
      Sql = "INSERT INTO " & Glob_SheetNameTotalBooks & " (VALUENAME,VALUEDATE,VALUE, LONGSHORT, THERMS, CLOSINGMONTH )" & _
      " VALUES (" & "'" & Name & "'" & "," & Valdate & "," & Value & "," & LongShort & "," & Therms & "," & ClosingMonth & ")"
      End If



      Debug.Print ("SQL INSERT " & Sql)
      Debug.Print ("*************************************************************")

      Glob_Conn.Execute Sql

   Next Item

 'cleanup
   Helper.CloseConnectionObjects Glob_RecSet, Glob_Conn

   InsertClosingMonthTotals = IsSuccess
End Function

enter image description here

EDIT kueri ini berfungsi

SQL SELECT select TOP 1 VALUENAME FROM [TradingTotals$] WHERE LOWER(VALUENAME) ='Trading'

Dan sepertinya masalahnya ada pada LOWER() ; adakah yang tahu jika LOWER() tidak dapat digunakan dalam kueri excel?

EDIT 2

Sepertinya itu hanya masalah "dengan benar" menulis kueri di Excel; yang di bawah ini berfungsi. Di yang sebelumnya juga ada tambahan ")" di akhir kueri

SQL SELECT select top 1 VALUENAME FROM [TradingTotals$] WHERE VALUENAME= 'Trading' AND VALUEDATE ='29/05/2020' AND CLOSINGMONTH = 'May' ORDER BY VALUEDATE DESC

Saya mencoba sekarang untuk menggunakan LOWER() lagi

EDIT 3 LOWER() tidak berfungsi, setidaknya dalam cara saya menggunakannya di VBA;

SQL SELECT select top 1 VALUENAME FROM [TradingTotals$] WHERE LOWER(VALUENAME)='Trading' And ValueDate =  '29/05/2020' AND CLOSINGMONTH = 'May' ORDER BY VALUEDATE DESC

Saya juga mencoba tanda kutip ganda untuk literal

1
brillox 29 Mei 2020, 14:33

1 menjawab

Jawaban Terbaik

Jadi pada akhirnya ada masalah yang berbeda tetapi sebagian besar karena sintaks kueri yang salah saat saya menulis SQL seperti saat menanyakan server SQL; belum sampai dengan kecepatan dengan sintaks query excel.

Terima kasih kepada semua yang menambahkan komentar yang bermanfaat, saya tidak tahu bagaimana menerima jawaban dari komentar jadi saya memposting ini sebagai jawaban tetapi tidak ada penghargaan untuk saya sama sekali; semua kredit untuk orang-orang hebat yang menambahkan komentar di sini.

Saya sekarang menghadapi masalah lain dengan penyisipan .. pada dasarnya bahkan jika saya melewatkan angka sepanjang dan tanggal disimpan di excel seperti umumnya. Bahkan jika saya memformat kolom dalam spreadsheet sebagai tanggal dan nomor, itu dikembalikan lagi ke umum?? tapi saya membuka pertanyaan baru untuk masalah ini

0
brillox 29 Mei 2020, 15:48