Dynamic recordset

[Ken Snell \(MVP\)]

The Help files contain some information on this. Search on Form Object and
select Recordset from the Properties list.

 

[Ken Snell \(MVP\)]

Here's a link to a Microsoft article about error values (such as #Error and
#Name?):

ACC2000: Troubleshooting Tips for Error Values
http://support.microsoft.com/default.aspx?scid=kb;en-us;209132


I don't have enough time right now to study what you've posted about the
code. I will do that as soon as I can and post back.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[David W. Fenton]

The Recordset property of a form was introduced
in Access 2000 and is settable to a predefined DAO recordset
(never an ADO recordset, as form recordsets are always DAO).

As I said in another post, this is incorrect. The form's Recordset
property can be set to an ADO recordset, but it will be read-only.

 

[David W. Fenton]

As I mentioned in my previuos post, the reason why I don't use an
sql statement is because I need to join data from a local table
and a remote table. the remote table is not a linked table, for
each row in the list box I regenerate the connection string.

I really don't see the issue. You can specify a connect string in
the FROM clause of the SQL.

 

[David W. Fenton]

I know how to work with recordsets and thinking about it, I don't
see any utility in populating a form from a variable unless it is
coming from another database.

You don't need it even then. There are at least two ways to do it:

1. specify the connect string with the table name in the FROM
clause, OR

2. define a derived table using its own connect string.

The latter is probably somewhat neater to implement, but I doubt
there'd be any difference between the two in terms of performance.

 

[Danny Lesandrini]

Orna:

I've done what you are doing, and while I agree with others that there might
be an easier way to accomplish it, your problem seems much simpler than
that. If the text boxes show #Error then you need to verify that their
ControlSource property values appear exactly as they do in the recordset
you're creating.

I could be mistaken, but I believe your issue is here, in these lines. You
can't open a form and then load its recordset and control source from
another place. This code to set the recordset should be in the form's
Open() event. Once the form is open, it's too late.

Set Form_frmDoobloFileDetails.Recordset = rsNew

Form_frmDoobloFileDetails.txtDoobloCode.ControlSource =
"SbjNum"
Form_frmDoobloFileDetails.txtDate.ControlSource = "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSource = "Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource = "SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.ControlSource =
"IsImported"

Run the code in the On Open event of the target form and then do this ...

Me!txtDoobloCode.ControlSource = "SbjNum"
Me!txtDate.ControlSource = "vDate"
Me!txtSurviyer.ControlSource = "Srvyr"
Me!txtBranch.ControlSource = "SbjNam"
Me!chkRecordImported.ControlSource = "IsImported"

Set Me.Recordset = rsNew

 

[Danny Lesandrini]

David, I don't know if such an ADO recordset is read only when pointing to
an Access MDB file, but I built an app that was entirely driven on ADO
recordsets, dynamically pointing to SQL Server databases.

I know that probably seems odd, but there was one HUB database that had
login and task info, and n number of client databases that were referenced
in the Task database. A user would load their task list and double clicking
a task would need to load an editable recored from the appropriate client
database.

Like I said, this was an odd application, but it taught me how to leverage
the .Recordset property of forms and controls. Seems like the .Recordset
property of reports was not as simple or flexible, but I forget the details
of the issues I faced. Anyhow, those recordsets were editable and after
setting it, the rest was seamless from VBA point of view.

 

[RoyVidar]

Seems like
the .Recordset property of reports was not as simple or flexible, but
I forget the details of the issues I faced.

Allthough the report exposes a recordset property, it can only be set
in an ADP, as far as I know.

 

[RoyVidar]

As I said in another post, this is incorrect. The form's Recordset
property can be set to an ADO recordset, but it will be read-only.

No, you can very well have updateable forms that are based on ADO
recordsets, except as I stated elsewhere

if you're using Access 2000 AND it's based on Jet data

With Access 2000, you can have updateable forms based on ADO
recordset if you're using MSDataShape and SQL Server OLEDB providers.

For later versions of Access, you can have updateable forms also when
the ADO recordset is based on Jet data.

Here's a repost of the links

ACC2000: Forms Based on ADO Recordsets Are Read-Only
http://support.microsoft.com/default.aspx?scid=kb;EN-US;227053

How to bind Microsoft Access forms to ADO recordsets
http://support.microsoft.com/kb/281998/EN-US/

 

[David W. Fenton]

No, you can very well have updateable forms that are based on ADO
recordsets, except as I stated elsewhere

if you're using Access 2000 AND it's based on Jet data

That's not what MichKa's article says. Did it change after he posted
that? And is this specific to A2K?

 

[Danny Lesandrini]

I can't speak for ALL situations, but with an Access 2003 ADP
one can load a form with an ADO recordset that is updateable.

 

[RoyVidar]

That's not what MichKa's article says. Did it change after he posted
that? And is this specific to A2K?

I think that when MichKa says

"At present, the ADO recordsets mentioned above will cause the form to
be read-only."

he is pointing to his above bullet point/paragraph were he only talks
about ADO recordsets based on Jet tables. He says nothing about SQL
server.

So - he's specifically talking about ADO recordsets based on Jet data,
and since the article is dated 1/22/00, it means latest version at
that time, was Access 2000.

And this is true, under those circumstances - Access 2000 AND
the ADO recordset is based on Jet data, the form is read-only.

And yes, this changed in the 2002 version of Access, where ADO
recordsets based on different OLEDB providers (SQL Server, Jet,
Oracle and ODBC) could provide updateable form recordsets.

But, as MS says (for 2002 and later versions):
"Requirements for Microsoft Jet
Even though it is possible to bind a form to an ADO recordset that is
using data from a Jet database, Microsoft recommends that you use DAO
instead. DAO is highly optimized for Jet and typically performs faster
than ADO when used with a Jet database.

[snipped some explanations and code sample]

Note that the form is bound to an updateable recordset that is using
Jet data."

http://support.microsoft.com/kb/281998/EN-US/

Also, using MSDataShape and SQL Server OLEDB providers, one can have
updateable forms based on ADO recordsets in the Access 2000 version.

http://support.microsoft.com/kb/227053/EN-US/

 

[David W. Fenton]

I can't speak for ALL situations, but with an Access 2003 ADP
one can load a form with an ADO recordset that is updateable.

I don't think we are talking about ADPs here, since, by definition,
that's an ADO environment.

 

[Ken Snell \(MVP\)]

I'm puzzled by why you use Form_frmDoobloFileDetails to refer to the form
named frmDoobloFileDetails. Any particular reason you don't use Me. instead
(assuming that the code is running in that form's module), or
Forms("frmDoobloFileDetails"). if it's not?

I believe that the problem with #Error is because the rsNew recordset is
destroyed when the lstFilesList_DblClick subroutine finishes. So, at that
point, your form has no more data to display, and you see the #Error message
in the controls.

The instantiation of the rsNew recordset needs to be done in the
Declarations section of the form that is running the code. (And take out the
instatiation from the lstFilesList_DblClick procedure.) That means that
you'll need to handle the possibility that your subroutine procedure is run
more than once, and that you don't append the same fields to the recordset
when it already has those fields.

Also, your code is looping through the list box's selections, and apparently
is opening the "frmDoobloFileDetails" form over and over, and adding the
same five fields over and over to the rsNew recordset, and then constantly
resetting the form's recordset to the rsNew recordset, which may have
problems because you are adding the same fields to it over and over.

I think you also need to pull this code out from the loop of VarItem and run
it after the end of that loop:

DoCmd.OpenForm "frmDoobloFileDetails", acNormal

Set Form_frmDoobloFileDetails.Recordset = rsNew

If rsNew.RecordCount > 0 Then
Form_frmDoobloFileDetails.txtDoobloCode.ControlSource =
"SbjNum"
Form_frmDoobloFileDetails.txtDate.ControlSource = "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSource =
"Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource = "SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.ControlSource =
"IsImported"
Form_frmDoobloFileDetails.lblFileName.Caption = "øùéîú
äá÷øåú á÷åáõ: " & sFileName
Else
Form_frmDoobloFileDetails.lblFileName.Caption = "àéï øùåîåú
á÷åáõ: " & sFileName
Form_frmDoobloFileDetails.lblFileName.ForeColor = 255
End If



This is the code that continually appends the same fields over and over to
the rsNew recordset. It too should not be running within the VarItem loop:

Do While Not rs.EOF
With rsNew
rsNew.AddNew
!SbjNum = rs!SbjNum
!vDate = rs!vDate
!Srvyr = rs!Srvyr
!SbjNam = rs!SbjNam
End With
rsVisits.Filter = "DoobloId=" & rs!SbjNum
If rsVisits.RecordCount = 0 Then
rsNew!IsImported = False
Else
rsNew!IsImported = True
End If
rsNew.Update
rs.MoveNext
Loop


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Orna]

David,

Thanks for your post, I wasn't aware to the fact that it's possible, I think
it might solve my problem.

Thanks,
Orna.

 

[Orna]

Hi David,

Your post was very helpfull, I'm opening the recordset with the following
sql string:

SELECT ([export1].[SbjNum]) In (select doobloid from " & sTableName & ") AS
IsImported, * FROM export1 IN '" & sFilePath & "\" & sFileName & "'"

It works great!

Thanks again,
Orna.

 

[David W. Fenton]

Your post was very helpfull, I'm opening the recordset with the
following sql string:

SELECT ([export1].[SbjNum]) In (select doobloid from " &
sTableName & ") AS IsImported, * FROM export1 IN '" & sFilePath &
"\" & sFileName & "'"

It works great!

Much easier, no? I'm so glad you worked it out (as it can be kinda
complicated to get the connect strings and the derived table syntax
correct).

You might now consider not even bothering with a recordset, and
assigning this SQL string as the recordsource for your form in the
same place that you're now assigning the recordset.

 

[Orna]

Setting the forms' recordsource sound even better, I'll try that.
BTW, when I tried to join the two tables from the two different databases I
got an error, so I found a workaround.

is the following syntax possible at all?
SELECT field1,field2 from table1 in 'PATH1',table2 in 'PATH2'
WHERE table1.field1=table2.field2

Orna.

Your post was very helpfull, I'm opening the recordset with the
following sql string:

SELECT ([export1].[SbjNum]) In (select doobloid from " &
sTableName & ") AS IsImported, * FROM export1 IN '" & sFilePath &
"\" & sFileName & "'"

It works great!

Much easier, no? I'm so glad you worked it out (as it can be kinda
complicated to get the connect strings and the derived table syntax
correct).

You might now consider not even bothering with a recordset, and
assigning this SQL string as the recordsource for your form in the
same place that you're now assigning the recordset.

 

[David W. Fenton]

Setting the forms' recordsource sound even better, I'll try that.
BTW, when I tried to join the two tables from the two different
databases I got an error, so I found a workaround.

is the following syntax possible at all?
SELECT field1,field2 from table1 in 'PATH1',table2 in 'PATH2'
WHERE table1.field1=table2.field2

That's an implicit join, instead of the explicit join, and yes, it's
fine, as Jet optimizes them exactly the same. That is, it will be
just as efficient as a join in utilizing indexes if it can (which it
probably can't in this case with heterogeneous data sources).