Record Count from Cmd.Execute-Saved query 2

[Poduska]

I have a vb module which is running a saved query which references a form for use as criteria on the query. For this I use CMD.Execute as described in thread705-1328125 and this works great.

However,
I can no longer get my record count to return as anything but -1 even if I try using adOpenStatic. Am I using adOpenStatic in the incorrect location or is there a better way?

Sub find_orders_one_parameter()

Dim conn As ADODB.Connection
'Dim cat As ADOX.Catalog
Dim cmd As New ADODB.Command
Dim rs  As New ADODB.Recordset
Dim strConn As String
Dim pl As String
Dim Record_Count As Integer

    
strConn = "Provider=Microsoft.jet.oledb.4.0;" & _
          "Data Source=" & CurrentProject.Path & _
          "\reporting.mdb"

   cmd.ActiveConnection = strConn
   cmd.CommandText = "[qry_with_form_criteria]"
 
    Set rs = cmd.Execute(, Array(CDate([Forms]![frm_date_selector]![start_date]), adOpenStatic, , adCmdStoredProc))
    
    Record_Count = rs.RecordCount
    MsgBox (Record_Count)
    
    If Not rs.EOF Then
    Debug.Print rs.GetString
Else
    MsgBox ("No Records Found" & Chr(13) & _
    "Try another date")
End If

Set conn = Nothing
Set rs = Nothing
Set cmd = Nothing
End Sub

When I run queries WITH OUT criteria referencing forms I do change to adOpenStatic and get a record count everytime.

   Set rst = New ADODB.Recordset
        With rst
        .Open "[qry_report_1_summary_availability]", strConn, adOpenStatic, adLockReadOnly, adCmdStoredProc
    End With
    Record_Count = rst.RecordCount
    'MsgBox (Record_Count)

 

[jadams0173]

I had a similar problem and solved by using a client side cursor.

conn.cursorlocation=aduseclient

I tried to have patience but it took to long! -DW

 

[Poduska]

I have never used Cursor's. Where whould this command be located? I tried it here and the query ran but still -1 records.

   cmd.ActiveConnection = strConn
   cmd.CommandText = "[qry_rpt_6_dc_stock_levels_select]"

   ' Execute the Command, passing in the values for the parameters

    Set rst = cmd.Execute(, Array(CDate([Forms]![frm_date_selector]![start_date]), adUseClient, adOpenStatic, adCmdStoredProc))
    
    
If Not rst.EOF Then
    Debug.Print rst.GetString
Else
    MsgBox ("No Records Found" & Chr(13) & _
    "Try another date")
End If

Honestly the only reason I am using the saved query is that I never can get the SQL to work when I try to use a form field as criteria.

 

[jadams0173]

Generally I open the connection instead of passing the conneciton string.

Function OpenConnection() As Boolean

   On Error GoTo OpenConnection_Error

If conn Is Nothing Then
    Set conn = New ADODB.Connection
End If


If conn.State = 0 Then
  With conn
 [red] .CursorLocation = adUseClient[/red]
  .Mode = adModeReadWrite
  .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=" & g_DBPath & g_DBName & ";" & _
                      "Jet OLEDB:Database Password="

  .Open
  OpenConnection = True
  End With
End If

   On Error GoTo 0
   Exit Function

OpenConnection_Error:

OpenConnection = False


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure OpenConnection of Module ConnectToDB" & vbCrLf & "The connection to the database could not be made!", vbCritical, "Connection Failed"
End Function

You can also post the SQL and we can try to make that work if it is a better solution.


I tried to have patience but it took to long! -DW

 

[Poduska]

I pasted in your function, pasted in my connection string and commented out the existing connection string and "New ADODB connection" line and it retrieves my data WITH record count. Thanks.

But if you are offering .... here is the SQL. I don't think the table names are necessary for each line in the Where clause, I just pasted it in from the SQL window.

The query simply retrieves data from an Oracle Materialized view which is linked to the MDB. There is a form with two calendar controls named Start date and End date. Most of my queries for this project use a Between start date and end date but this query only uses one.

The user clicks on the calendar control, Clicks the respective command button and it exports to excel. If I could get the queries into code it would also kind of hide the queries from the user, less upkeep!

SELECT APPS_XXCOC_DC_STOCK_LEVELS_MV.EAN, APPS_XXCOC_DC_STOCK_LEVELS_MV.ARTIST AS Artist, APPS_XXCOC_DC_STOCK_LEVELS_MV.TITLE AS Title, APPS_XXCOC_DC_STOCK_LEVELS_MV.SUPPLIER_NAME AS [Supplier Name]
FROM APPS_XXCOC_DC_STOCK_LEVELS_MV
WHERE (((DateValue([APPS_XXCOC_DC_STOCK_LEVELS_MV]![SALES_DATE]))=[Forms]![frm_date_selector]![start_date]))
ORDER BY APPS_XXCOC_DC_STOCK_LEVELS_MV.EAN;

The existing code uses

CDate([Forms]![frm_date_selector]![start_date])

to pass the date from the calendar control to the query although I am not sure how the ADO CDate differs from Access's DateValue, perhaps it is the same thing.

If I can get one working example of a working Where clause I may be on my way.

Thanks again.

 

[PHV]

Something like this ?
strSQL = "SELECT EAN, ARTIST AS Artist, TITLE AS Title, SUPPLIER_NAME AS [Supplier Name]" _
& " FROM APPS_XXCOC_DC_STOCK_LEVELS_MV" _
& " WHERE DateValue(SALES_DATE)=#" & Forms!frm_date_selector!start_date & "#" _
& " ORDER BY EAN"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Poduska]

PHV, I do believe I have left you a star before, now another...SUCCESS!

But I have a question,
I know why you used the # sign as it is a date.
Would the reference to a control on a form, say a list box, change if the value was numeric or a string?

I have seen several threads which deal with encasing strings with the ' "single quote, double quote" ' which is confusing.

I just want to understand this once and for all!

Learn, learn, learn.

Thanks

 

[PHV]

A where clause for a text field:

...
 & " WHERE TextField='" & Replace(Forms!Mainform![text control], "'", "''") & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Poduska]

You loose me at the comma after the field designation, I have bolded the area.

...
 & " WHERE TextField='" & Replace(Forms!Mainform![text control], "'",[b] "''") & "'"[/b]

Let me see if I can explain where I get lost.

[red]& " WHERE TextField=[/red] This starts a new row by connecting the red code with what is above.

[red]'" & Replace(Forms!Mainform![text control], "'"[/red] now do I need another & instead of a comma after [text control] ?


I was assuming that after the second double quote is where I would place an [red]AND[/red] if there were additional where statements.

Or am I way off base and REPLACE is a statement and not just telling me to "replace this with your field info"

Thanks

 

[PHV]

Replace is a VBA function (since office2000)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

A more complex where clause:

...
 & " WHERE TextField='" & Replace(Forms!Mainform![text control], "'", "''") & "'" _
 & " AND DateField=#" & Format(Forms!Mainform![date control], "yyyy-mm-dd") & "#" _
 & " AND NumericField=" & Forms!Mainform![numeric control]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Poduska]

Let me know if it would be best to start a new thread but..

The final evolution of my sql is to add the criteria from a list box which was created with a union query to add a * to the top of the list box for "ALL" faq701-2330. This works GREAT with regular queries, the * brings back everything as expected.

However this is not the case with the SQL in VB

Here is the saved query "Where" statement which works

WHERE (((DateValue([APPS_XXCOC_SHIPMENTS_MV]![SALES_DATE])) Between [Forms]![frm_date_selector]![start_date] And [Forms]![frm_date_selector]![end_date]) AND ((APPS_XXCOC_SHIPMENTS_MV.SHIPMENT)>0) AND ((tbl_Chart_Item.Chart) Like ([Forms]![frm_date_selector]![list_chart])))

This Where statement is in my VB code and it works when you select the actual item in the list box but not the *.

"WHERE (((DateValue(APPS_XXCOC_SHIPMENTS_MV!SALES_DATE) Between # " & Forms!frm_date_selector!start_date & "#" _
& "And #" & Forms!frm_date_selector!end_date & "#) " _
& "AND APPS_XXCOC_SHIPMENTS_MV.SHIPMENT > 0) " _
& "AND tbl_Chart_Item.Chart Like '" & Replace([Forms]![frm_date_selector]![list_chart], "'", "''") & "' ) " _

If this helps, when I was debugging the statement one of the error messages came up showing the * reflected in the final SQL as '*'. I dont know if that is correct.

The list box is created with this code. I tried to add "" around the * in the list box SQL but I kept getting Data type mismatch (which I don't understand why).

SELECT tbl_charts.Chart_Type AS Chart FROM tbl_charts GROUP BY tbl_charts.Chart_Type UNION SELECT "*"  FROM tbl_charts ORDER BY chart;

 

[PHV]

with ADO, the wildchar is not * but %

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Poduska]

Thanks PHV,
Most of our development is in Oracle here so I am used to using % in the on line screens etc. Just for the heck of it I tried a % out of frustration a few hours ago and it worked.

But thanks again for confirming what I though was just a quirk.