Excel and ADO

[Liquid Metal]

Anyone ever experience this with an Update Statement

I keep getting this: "syntax error (missing operator) in query expression"

    Dim adoConn As New ADODB.Connection
    Dim adoRS As New ADODB.Recordset
    adoConn.CommandTimeout = 3
    adoConn.ConnectionTimeout = 3
    adoConn.CursorLocation = adUseClient
    adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Chr(34) & gstrResultFile & Chr(34) & ";Persist Security Info=False;Extended Properties=" & Chr(34) & "Excel 8.0;HDR=Yes" & Chr(34)

'This works fine
    'pi_strSQL = "UPDATE [Path1$] SET [Path1$].[Processed]=1 WHERE [Path1$].[Processed]=0"   

'This does not work - the only difference is the 'FROM [Path1$]'
    pi_strSQL = "UPDATE [Path1$] SET [Path1$].[Processed]=1 FROM [Path1$] WHERE [Path1$].[Processed]=0"   

    Call adoConn.Execute(pi_strSQL)
    
    adoRS.Close
    Set adoRS = Nothing
    adoConn.Close
    Set adoConn = Nothing

[si_the_geek]

The FROM is invalid in that case, what are you trying to do differently from the working version?

[Liquid Metal]

The FROM is invalid in that case, what are you trying to do differently from the working version?

How would I do an inner join with two tables for an UPDATE statement?

[jcis]

    pi_strSQL = "UPDATE [Path1$]  SET [Path1$].[Processed]=1 FROM [Path1$] WHERE [Path1$].[Processed]=0"

If you want to update your table from another table, you could do something like this:
This Updates [Path1$] from [Path2$], works fine in Access, I don't know if this works with Excel.

UPDATE [Path1$] 
     SET [Path1$].[Processed] = 1 
 WHERE [Path1$].[Processed] IN 
       (Select T.[Processed] 
         FROM [Path2$] T 
       WHERE T.[Processed] = 0);

[Liquid Metal]

That works JCIS! But it is kind of strange it does not take inner joins, rather it takes correlated/sub queries. How would I do an inner join than?

'---------------------------------------------------------------------------
Dim strSQL As String
strSQL = ""
strSQL = strSQL & "UPDATE [Path1$]" & vbCrLf
strSQL = strSQL & "SET [Path1$].[Processed] = 1 " & vbCrLf
strSQL = strSQL & "WHERE EXISTS (SELECT * FROM [Path2$]" & vbCrLf
strSQL = strSQL & "WHERE [File]=[Path1$].[File] AND [XPath]=[Path1$].[XPath])" & vbCrLf
strSQL = strSQL & "AND [Path1$].[Processed] = 0" & vbCrLf
'---------------------------------------------------------------------------

What I really need is to update the Processed field for 2 tables at once. Is that possible?

Thank You

[Liquid Metal]

I am in still interest in wanting to know why this work in Query Anaylzer but does not work through the above code.

UPDATE dbo.Table_2 SET
dbo.Table_2.Data = 'Frack'
FROM 
	dbo.Table_1 T1
INNER JOIN
	dbo.Table_2 T2
ON T1.Buffer = T2.Buffer

SELECT * FROM dbo.Table_2

[si_the_geek]

That will work thru code... for SQL Server databases.

Unfortunately SQL varies by DBMS and/or driver (even tho it is theoretically standardised), and Update is one situation where the difference shows more than most.

What I really need is to update the Processed field for 2 tables at once. Is that possible?

Not in a single update statement I'm afraid. If you use SQL Server (or other major DBMS) you can create a stored procedure, otherwise the best you can do is multiple Updates within a transaction.

[khoar]

Here is a suggestion for troubleshooting embedded sQL statements.

Capture the EXACT SQL statement using an error handler that pastes the SQL into a text box. Then you can copy the SQL and paste it into an SQL tool. It really makes like a lot easier, because the SQL errors that VB displays are often not what is acutally happening.

Build a form, frmErrorDisplay, that has the necessary objects to accept the passed strings.

For example:

Private Sub cmbLogIn_Click()
  On Error GoTo ErrorRoutineErr
 
  .....
  .....
 
  ' Capture SQL string for error trap
  SQLHoldString = strSql
 
  rstUser.Open strSql, cnn, , , adCmdText
  SQLHoldString = ""
 
  .....
  .....
 
ErrorRoutineResume:
  Exit Sub
ErrorRoutineErr:
    If Not SQLHoldString = "" Then
    Beep
    frmErrorDisplay.txInfoWindow.Text = SQLHoldString
    frmErrorDisplay.lbSQLDisplay.Caption = SQLHoldString
    frmErrorDisplay.lbSQLErrorMsg.Caption = "SQL Error --> " & Err & " - " & Error
    frmErrorDisplay.lbErrorMessage.Caption = "Error attempting to Login !" + CRLF + _
                    "There may be SQL Syntax error(s)." + CRLF + _
                    "Contact the system developer for assistance."
    Set gCallingForm = Me
    frmLogIn.Enabled = False
    frmErrorDisplay.Show vbModal
    frmLogIn.Enabled = True
    Exit Sub
  End If
  gIsError = True
  MsgBox "Login Module  --> " & Err & " - " & Error
  Resume Next
 
End Sub

Hope this helps.

[si_the_geek]

Very true, this FAQ thread has similar suggestions.

[khoar]

Debug.Print is sometimes okay, but only when developing the application and for very simple SQL statements.

Many times the SQL statement can be way, way too long for the immediate window. I have several apps where some of the SQL statements are 50 lines or more long.

Using the error form, if a client has a problem, I can walk him through copying the SQL statement and then he can paste it into an email and send it to me.

Makes supportung multiple, remote applications much easier.

[si_the_geek]

True, which is one reason I made the SQL formatter (link via the FAQ thread), so that long SQL statements are much easier to read.

I personally don't like the idea of letting users see the SQL, it's a bit of a risk in terms of security etc (they may try running their own version). In situations like that I prefer to save the SQL statement to a file (with simple encryption), which can be emailed if appropriate.

If you have access to the database, you can save the SQL to an error log table, or for larger DBMS's (such as SQL Server/Oracle) you can check error logs etc.

[Liquid Metal]

Thank you for your replies guys.

So, how do I update an Excel Spreadsheet using ADO?

[khoar]

I would not know as I would never consider using ADO to write to Excel. Excel is not a database.

If I were foing to write to a spreadsheet such as Excel, I would open an instance of Excel, such as:

Set vbExcel = CreateObject("Excel.Application")
 
  ' For TESTING ONLY !!!!!  If you want Excel to be visible
  If gRunMode = "Testing" Then
    vbExcel.Visible = True
  End If

Then I would write to the spreadsheet using VBA code:

' Capture Raw Data, if flag is set
  If gCaptureRawData = "YES" Then
 
    ' Activate data tab
    vbWorkbook.Sheets("Data").Select
 
    ' Find last row
    LastRow = MaxRow("B4")
 
    ' Delete current data, if any exists
    If LastRow > 3 Then
      Range("B4:S" + RTrim(CStr(LastRow))).Select
      Selection.ClearContents
    End If
    Range("B4").Select
 
    ' Write result set to Excel data worksheet
    RowCounter = 4
    rstDetail.MoveFirst
    Do Until rstDetail.EOF
      Range("B" + RTrim(CStr(RowCounter))).Value = RTrim(rstDetail!AgencyName)
      Range("C" + RTrim(CStr(RowCounter))).Value = RTrim(rstDetail!Address)
      Range("D" + RTrim(CStr(RowCounter))).Value = RTrim(rstDetail!CityState)
      Range("E" + RTrim(CStr(RowCounter))).Value = RTrim(rstDetail!SubAgent)
      Range("F" + RTrim(CStr(RowCounter))).Value = RTrim(rstDetail!Underwriter)
      Range("G" + RTrim(CStr(RowCounter))).Value = rstDetail!ClientNumber
      Range("H" + RTrim(CStr(RowCounter))).Value = rstDetail!CustomerState
      Range("I" + RTrim(CStr(RowCounter))).Value = RTrim(rstDetail!CustomerName)
      Range("J" + RTrim(CStr(RowCounter))).Value = RTrim(rstDetail!PolicyDesc)
      Range("K" + RTrim(CStr(RowCounter))).Value = RTrim(rstDetail!SpecProv)
      Range("L" + RTrim(CStr(RowCounter))).Value = rstDetail!OrderNo
      Range("M" + RTrim(CStr(RowCounter))).Value = CDbl(rstDetail!PremiumDue)
      Range("N" + RTrim(CStr(RowCounter))).Value = CDbl(rstDetail!PremiumPaid)
      Range("O" + RTrim(CStr(RowCounter))).Value = CDbl(rstDetail!NetDueMMC)
      Range("P" + RTrim(CStr(RowCounter))).Value = CDbl(rstDetail!Commission)
      Range("Q" + RTrim(CStr(RowCounter))).Value = rstDetail!RA_LName
      Range("R" + RTrim(CStr(RowCounter))).Value = CDbl(rstDetail!RA_FlatFee)
        Range("S" + RTrim(CStr(RowCounter))).Value = rstDetail!VoidIndicator
      rstDetail.MoveNext
      RowCounter = RowCounter + 1
    Loop
  End If

[si_the_geek]

Thank you for your replies guys.

So, how do I update an Excel Spreadsheet using ADO?

I was under the impression you had that part working

What is the current situation?

[Liquid Metal]

Thank You si_the_geek!

It was the same old stuff. I couldn't update on a join but when I take the join out, it works fine.

[Liquid Metal]

Thanks Hoar.

I understand your reference about not using Excel as a database. This was a special case.

Thank You

[si_the_geek]

I'm afraid it look as if jcis's method (post #4) is about all you can do with Excel databases.