Problem converting to ADO with Recordset (1 Viewer)

KeithWilliams · Feb 23, 2004

Hi,

I am trying to convert my application to ADO, and have a problem with assigning a recordset to the Recordset object of the form, in the Open event:

Code:

    Dim qdf As ADODB.Command
    Dim rst As ADODB.Recordset

-code to set up qdf with a query object and parameters etc-

    Set rst = qdf.Execute
    Set Me.Recordset = rst

(I know, I know, I should have changed the object names from the original DAO variables!)

This code produces the message:
"Run-time error '7965':
The object you entered is not a valid Recordset property."

The original code worked OK, where rst is a DAO recordset and qdf is a QueryDef.

I THINK the 2 set statements should be populating rst with a recordset that is the result of my query, and then assigning that recordset to my form.

Can anyone tell me where I am going wrong?

Thanks,
Keith.

dcx693 · Feb 23, 2004

Try:

Code:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn=CurrentProject.Connection
Set rst=New ADODB.Recordset

rst.Open [i]queryname or SQL string[/i], cnn

Me.RecordSource = rst.Source

KeithWilliams · Feb 24, 2004

Hi dcx693,

I tried your suggestion, but it produced a different set of errors. Let me go back to the beginning. I have a query with parameters, a mix of Short and Text(255) parameter fields. The query works OK when I run it in Access.

I want to open a form using the query as the source of the form's recordset, populating the query parameters with values from the Form's OpenArgs. I don't want the user of the form to be prompted for the parameter values. Can you tell me how I should code the form's Open event?

I don't understand which statement to use to produce a recordset from the query (Execute, Open or something else), which statement to use to assign the recordset to be the form's recordset, and which data type to use for a parameter of type Text(255) .

Really hope you can help me, as I am on the point of reverting to DAO, which will mean I can't migrate to SQL Server in the future!

Thanks,
Keith.

dcx693 · Feb 24, 2004

I want to open a form using the query as the source of the form's recordset, populating the query parameters with values from the Form's OpenArgs.

I can think of a few ways to do this.

1 - Use a stored query. If you parse out the form's OpenArgs properties, you can then place these into hidden fields on the form (if they're not too many of them). You can then refer to these from the stored query using the typical Forms!formname!controlname syntax.

2 - Build your query on the fly in the code's class module. Parse out the OpenArgs and set them equal to variables. Then create a SQL statement that you can place into this line:
rst.Open queryname or SQL string, cnn
from the code I posted earlier.

KeithWilliams · Feb 24, 2004

Hi,

Thanks for the suggestions, but I really want to be able to use the query as-is, without manipulating the query syntax. I have it working great in DAO, and the syntax is quite terse compared to ADO, but I hate the idea that I could never upsize to SQL server if needed. Here's the DAO code that is working for me:

(Note, the passed arguments are Division, Station, Watch, Year, Performance Measure Id and Performance Measure Name, separated by semi-colons. The first 3 arguments are optional, and are passed to the query as NULL parameters if empty. Division, Station and Watch are levels within the organizational hierarchy, and if all left blank, then the query returns results for the whole organization.)

Code:

Private Sub Form_Open(Cancel As Integer)
    Dim stOpenArgs() As String
    Dim stArgDivision As String
    Dim stArgStation As String
    Dim intArgStation As Integer
    Dim stArgWatch As String
    Dim stWindowTitle As String
    Dim intArgPerformanceMeasure As Integer
    Dim intArgYear As Integer
    Dim stArgPerformanceMeasureName As String
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim qdf_actual As DAO.QueryDef
    Dim rst_actual As DAO.Recordset
    Dim dateMonthStart As Date
    Dim intActual As Integer
    Dim stCalcActualFormula As String
    Dim qdf_append As DAO.QueryDef
    
    stOpenArgs() = Split(OpenArgs, ";")
    stArgDivision = stOpenArgs(0)
    stArgStation = stOpenArgs(1)
    intArgStation = CInt(stArgStation)
    stArgWatch = stOpenArgs(2)
    intArgYear = CInt(stOpenArgs(3))
    intArgPerformanceMeasure = CInt(stOpenArgs(4))
    stArgPerformanceMeasureName = stOpenArgs(5)
    
    stWindowTitle = " for " & stArgPerformanceMeasureName & " - " _
    & IIf(stArgDivision = "", "", stArgDivision & " Division") _
    & IIf(stArgStation = "", "", " Station " & stArgStation) _
    & IIf(stArgWatch = "", "", " " & stArgWatch & " Watch")
    Me.Caption = "Edit Targets" & stWindowTitle
    
    Set qdf_append = CurrentDb.QueryDefs("Q_Append_Target_InsertMissingMonths_ByParm")
    qdf_append.Parameters("Parm_Division") = IIf(stArgDivision <> "", Left(stArgDivision, 1), Null)
    qdf_append.Parameters("Parm_Station") = intArgStation
    qdf_append.Parameters("Parm_Watch") = IIf(stArgWatch <> "", stArgWatch, Null)
    qdf_append.Parameters("Parm_Performance_Measure") = intArgPerformanceMeasure
    qdf_append.Parameters("Parm_Year") = intArgYear
    
    qdf_append.Execute
    
    Set qdf = CurrentDb.QueryDefs("Q_Select_Target_ByParm")
    
    qdf.Parameters("Parm_Division") = IIf(stArgDivision <> "", Left(stArgDivision, 1), Null)
    qdf.Parameters("Parm_Station") = intArgStation
    qdf.Parameters("Parm_Watch") = IIf(stArgWatch <> "", stArgWatch, Null)
    qdf.Parameters("Parm_Performance_Measure") = intArgPerformanceMeasure
    qdf.Parameters("Parm_Year") = intArgYear
    
    Set rst = qdf.OpenRecordset()
    Set Me.Recordset = rst
        
    Set qdf_actual = CurrentDb.QueryDefs("Q_Select_Incident_ByParm")
    
    qdf_actual.Parameters("Parm_Division") = IIf(stArgDivision <> "", Left(stArgDivision, 1), Null)
    qdf_actual.Parameters("Parm_Station") = intArgStation
    qdf_actual.Parameters("Parm_Watch") = IIf(stArgWatch <> "", stArgWatch, Null)
    qdf_actual.Parameters("Parm_Performance_Measure") = intArgPerformanceMeasure
    qdf_actual.Parameters("Parm_Year") = intArgYear
    
    Set rst_actual = qdf_actual.OpenRecordset()
    rst_actual.MoveFirst
    
    Do
        intActual = rst_actual![IncidentCount]
        
        stCalcActualFormula = stCalcActualFormula & "," & CStr(intActual)
        
        rst_actual.MoveNext
    Loop Until rst_actual.EOF
    
    Me!Calc_Actual.ControlSource = "=Choose(IIf(IsNull(MonthStartDate),1,IIf(Month([MonthStartDate])<4,Month([MonthStartDate])+10,Month([MonthStartDate])-2))" & stCalcActualFormula & ")"
End Sub

In explanation, I use qdf_append to append new rows to a Target table, qdf to query the Target table, and qdf_actual to concatenate values from the Actual table to populate the Calc_Actual field. The form displays Continuous, and will always show 13 rows, 1 for the Year and 12 for the months of that Year. Each row has a MonthStartDate (null for the one Year row) and Target, both obtained from qdf, and an unbound Calc_Actual, which has a ControlSource set up to show a different value for each month. Thus the final form display looks like:

Code:

MONTH       TARGET  ACTUAL
Year        2000    2203
01/04/2003  180     191
01/05/2003  180     187
01/06/2003  190     194
etc.

I'd greatly appreciate any suggestions.

Thanks,
Keith.

dcx693 · Feb 24, 2004

My suggestion #2 is what you're doing. You just need to know how to do it in ADO. Here's a basic example of how to create a parameter query using ADO:

Code:

Dim cnn As ADODB.Connection
Dim cat as ADOX.Catalog
Dim cmd as ADODB.Command
Dim rst As ADODB.Recordset

Set cnn=CurrentProject.Connection
Set cat=New ADOX.Catalog
cat.ActiveConnection=cnn
Set rst=New ADODB.Recordset

Set cmd=cat.Procedures("Q_Append_Target_InsertMissingMonths_ByParm").Command

cmd.Parameters("Parm_Division")=IIf(stArgDivision <> "", Left(stArgDivision, 1), Null)
[i]etc...[/i]

cmd.Execute ' if you need to execute this query

rst.Open cmd, cnn, , ,adCmdStoredProc

Me.RecordSource = rst.Source

dcx693 · Feb 24, 2004

KeithWilliams, I forgot to make sure you also knew to include a reference to ADOX in your VB editor. Go to Tools, References, then click on "Microsoft ADO Ext.2.x. for DDL and Security".

KeithWilliams · Feb 24, 2004

Hi, I tried adapting my approach using your example, and ther first part works great (executing the append query) but the second part (assigning a query to the Form's recordset) doesn't work. Here's the code:

Code:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
    Dim stOpenArgs() As String
    Dim stArgDivision As String
    Dim stArgStation As String
    Dim intArgStation As Integer
    Dim stArgWatch As String
    Dim stWindowTitle As String
    Dim intArgPerformanceMeasure As Integer
    Dim intArgYear As Integer
    Dim stArgPerformanceMeasureName As String
    Dim qdf As DAO.QueryDef
    'Dim rst As DAO.Recordset
    Dim qdf_actual As DAO.QueryDef
    Dim rst_actual As DAO.Recordset
    Dim dateMonthStart As Date
    Dim intActual As Integer
    Dim stCalcActualFormula As String
    Dim qdf_append As DAO.QueryDef
    Dim cnn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    
    stOpenArgs() = Split(OpenArgs, ";")
    stArgDivision = stOpenArgs(0)
    stArgStation = stOpenArgs(1)
    intArgStation = CInt(stArgStation)
    stArgWatch = stOpenArgs(2)
    intArgYear = CInt(stOpenArgs(3))
    intArgPerformanceMeasure = CInt(stOpenArgs(4))
    stArgPerformanceMeasureName = stOpenArgs(5)
    
    stWindowTitle = " for " & stArgPerformanceMeasureName & " - " _
    & IIf(stArgDivision = "", "", stArgDivision & " Division") _
    & IIf(stArgStation = "", "", " Station " & stArgStation) _
    & IIf(stArgWatch = "", "", " " & stArgWatch & " Watch")
    Me.Caption = "Edit Targets" & stWindowTitle
    
    Set cnn = CurrentProject.Connection
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = cnn
    Set rst = New ADODB.Recordset
    Set cmd = cat.Procedures("Q_Append_Target_InsertMissingMonths_ByParm").Command
    cmd.Parameters("Parm_Division") = IIf(stArgDivision <> "", Left(stArgDivision, 1), Null)
    cmd.Parameters("Parm_Station") = intArgStation
    cmd.Parameters("Parm_Watch") = IIf(stArgWatch <> "", stArgWatch, Null)
    cmd.Parameters("Parm_Performance_Measure") = intArgPerformanceMeasure
    cmd.Parameters("Parm_Year") = intArgYear
    cmd.Execute
    
    
    Set cmd = cat.Procedures("Q_Select_Target_ByParm").Command
    cmd.Parameters("Parm_Division") = IIf(stArgDivision <> "", Left(stArgDivision, 1), Null)
    cmd.Parameters("Parm_Station") = intArgStation
    cmd.Parameters("Parm_Watch") = IIf(stArgWatch <> "", stArgWatch, Null)
    cmd.Parameters("Parm_Performance_Measure") = intArgPerformanceMeasure
    cmd.Parameters("Parm_Year") = intArgYear
    
    rst.Open cmd, , , , adCmdStoredProc
    Me.RecordSource = rst.Source
        
    Set qdf_actual = CurrentDb.QueryDefs("Q_Select_Incident_ByParm")
    
    qdf_actual.Parameters("Parm_Division") = IIf(stArgDivision <> "", Left(stArgDivision, 1), Null)
    qdf_actual.Parameters("Parm_Station") = intArgStation
    qdf_actual.Parameters("Parm_Watch") = IIf(stArgWatch <> "", stArgWatch, Null)
    qdf_actual.Parameters("Parm_Performance_Measure") = intArgPerformanceMeasure
    qdf_actual.Parameters("Parm_Year") = intArgYear
    
    Set rst_actual = qdf_actual.OpenRecordset()
    rst_actual.MoveFirst
    
    Do
        intActual = rst_actual![IncidentCount]
        
        stCalcActualFormula = stCalcActualFormula & "," & CStr(intActual)
        
        rst_actual.MoveNext
    Loop Until rst_actual.EOF
    
    Me!Calc_Actual.ControlSource = "=Choose(IIf(IsNull(MonthStartDate),1,IIf(Month([MonthStartDate])<4,Month([MonthStartDate])+10,Month([MonthStartDate])-2))" & stCalcActualFormula & ")"
End Sub

On the line
Me.RecordSource = rst.Source
I get the error:
Run-time error '3129':
Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'.

In the debugger, the value of rst.Source is:
exec PARAMETERS Parm_Performance_Measure Short, Parm_Year Short, Parm_Division Text ( 255 ), Parm_Station Short, Parm_Watch Text ( 255 );
SELECT [MonthStartDate], [SAP_Target].[Value] AS Target
FROM SAP_Target
WHERE Nz([SAP_Target].[Year],'')=Nz([Parm_Year],'') And Nz([SAP_Target].[Division],'')=Nz([Parm_Division],'') And Nz([SAP_Target].[Station],'')=Nz([Parm_Station],'') And Nz([SAP_Target].[Watch],'')=Nz([Parm_Watch],'') And Nz([SAP_Target].[PerformanceMeasureId],'')=Nz([Parm_Performance_Measure],'');

Is it possible the recordset can't cope with the parameters? Do I need to parse them out? That's what I am trying to avoid - parsing them out and then substituting in their values into the WHERE clause. Or if I just strip off the part up to the first semi-colon, will the recordset still pick up my parameters? I can't see how it would. I don't understand why I should need to do this when I don't with DAO. Baffled!

Many thanks for your help,
Keith.

PS I do have ADOX in my References, thanks.

dcx693 · Feb 24, 2004

I assume you've tried it with just rst?

KeithWilliams · Feb 24, 2004

Hi,

Yes, I've tried
Me.RecordSource = rst
if that's what you mean.

I get Compile Error: Type mismatch.

Thanks,
Keith.

Clon · Jun 7, 2013

Hello:

I've had the same problem, it is really easy to fix!

Simply, set this property of the recordset before opening it

rst.CursorLocation = adUseClient

Your original code should then work as you posted it.

Greetings

Clon · Jun 7, 2013

Sorry, my previous reply was too quick!

First, you need to declare and set the connection:

dim conn as adodb.connection
set conn = currentproject.connection

Then, you need to specify that the cursor is to be created at the client, not the server:

conn.CursorLocation = adUseClient

When you create the command, you must set its connection to conn

Set qdf.ActiveConnection = conn

The rest of the code should work.

Regards

Clon · Jun 7, 2013

Hmmmm... I think I'm growing older.... I didn't notice the dates :-S

Galaxiom · Jun 8, 2013

Since we are back here lets clear up some stuff

dcx693 said:
Me.RecordSource = rst.Source[/code]

RecordSource is a string. Assigning it with rst.Source would result in the form having a DAO recordset built on the same source as the ADO recordset, rendering the entire ADO recordset redundant.

The line required is:
Set Me.Recordset = rst

BTW Requerying the form's ADO recordset is done with:
Set Me.Recordset = Me.Recordset

Clon said:
set conn = currentproject.connection

For the Access databases it is usually best to use the connection:

CurrentProject.AccessConnection

This overcomes some problems with updateability that can otherwise be experienced.

Clon · Jun 12, 2013

Thanks, Galaxiom!! I didn't know about the AccessConnection property.

Regards,

Problem converting to ADO with Recordset (1 Viewer)

KeithWilliams

Registered User.

dcx693

Registered User.

KeithWilliams

Registered User.

dcx693

Registered User.

KeithWilliams

Registered User.

dcx693

Registered User.

dcx693

Registered User.

KeithWilliams

Registered User.

dcx693

Registered User.

KeithWilliams

Registered User.

Clon

Registered User.

Clon

Registered User.

Clon

Registered User.

Galaxiom

Super Moderator

Clon

Registered User.

Similar threads

Users who are viewing this thread