1

I have an Excel worksheet that is connected to an Oracle database via ODBC which brings in invoice data. I am then attempting to read the data in the Excel worksheet from another worksheet in the same workbook by using a SQL statement using an excel range that defines the downloaded (linked) data.

The SQL statement works fine without the WHERE clause. When I add the WHERE clause which is setting criteria on a Date field, I get the error listed in the subject of this post. I believe the syntax I am using in the SQL statement for the date field is correct -- because if I copy the downloaded data and paste it as Values into another worksheet, and then process the SQL statement against this copied data, the SQL statement works. Also if I select other (non-date) fields to use in the WHERE clause, the code works.

Below is the code. (The range name "InvoiceData" defines the dataset that the SQL statement will read). Please let me know if you have any ideas how to correct. My backup plan is to simply copy / paste the data as values - but this will make the file considerably larger.

The error message of "the connection for viewing your linked Microsoft Excel ... was lost" is encountered on the last statement of the code.

Function OpenRangeRS(rng As Range) As Recordset
'**Returns as recordset for the passed in range or range name
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sDBPath As String
Dim sRangeAddress As String

Dim sFile As String
Dim sConnect As String
Dim sSQL As String
Dim dReportDate As Date

sDBPath = ThisWorkbook.FullName

sConnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & sDBPath & ";HDR=Yes';"
       cn.Open sConnect


dReportDate = Range("SelectedDate")

sSQL = "SELECT * FROM [InvoiceData] "
sSQL = sSQL & " WHERE Invoice_Date = " & "#" & dReportDate & "#"

rs.Open sSQL, sConnect
Community
  • 1
  • 1
mmogul
  • 11
  • 1
  • 3

3 Answers3

1

My bet's on the date format. However, there are a few points worth making that may or may not be useful to you:

  1. You can use parameterised queries with ADODB and this will help you keep control of your queries (http://www.unixwiz.net/techtips/sql-injection.html) and, in your case, will help manage date formats more easily.
  2. I prefer to read Excel date values using Value2 and then converting the date to a format that I want within the VBA code. This will protect you if someone changes the date format of the cell itself (What is the difference between .text, .value, and .value2?)
  3. Returning a RecordSet from a function can sometimes be problematic if it or the connection are closed. I prefer to handle the Recordset within the routine that open and closes the database and, if necessary, return the processed data. For example, if you are writing to a worksheet, then I'd rather read the Recordset into an array of Variants, appropriately sized, and return that.

I'm not sure if the code below will answer your specific question, but it might help and provides an example of the three points:

Function OpenRangeRS(rng As Range) As Variant
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
    Dim rs As ADODB.Recordset
    Dim sDBPath As String
    Dim sConnect As String
    Dim sSQL As String
    Dim hDateValue As Double
    Dim dReportDate As Date
    Dim output() As Variant
    Dim i As Long

    'Open the connection
    Set cn = New ADODB.Connection
    sDBPath = ThisWorkbook.FullName
    sConnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & sDBPath & ";HDR=Yes';"
    cn.Open sConnect

    'Format date value as required for db
    'Note: prob not needed for parameterised query but shown here in case you stick to unparameterised
    hDateValue = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value2 '~~> define as needed
    dReportDate = Format(CDate(hDateValue), "dd/mm/yyyy")

    'Define the command object and append parameter
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cn
    sSQL = "SELECT * FROM [InvoiceData] WHERE Invoice_Date = ?"
    cmd.CommandText = sSQL
    Set prm = cmd.CreateParameter(, adDate, adParamInput, , dReportDate)
    cmd.Parameters.Append prm

    'Open the recordset object
    Set rs = cmd.Execute

    '... do whatever you need to with the recordset
    If Not rs.EOF Then
        ReDim output(1 To rs.RecordCount, 1 To 3)
        i = 1
        Do Until rs.EOF
            output(i, 1) = i
            output(i, 2) = rs!SomeField
            output(i, 3) = rs!OtherField
            rs.MoveNext
            i = i + 1
        Loop
        OpenRangeRS = output
    End If


    'Tidy up
    Set prm = Nothing
    Set cmd = Nothing
    rs.Close
    cn.Close
End Function
Community
  • 1
  • 1
Ambie
  • 4,872
  • 2
  • 12
  • 26
1

I had the same error and resolved it with a simple Range("").ClearFormats prior to querying a sheet within my open Excel file.

Geoff
  • 21
  • 1
-1

I agree it is probably the date format. I would suggest formatting it in an unambiguous date format

dReportDate = Format(Range("SelectedDate"), "yyyymmdd")

to be sure that it is language independent

Bob Phillips
  • 437
  • 1
  • 3
  • 7