A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dynamic recordset



 
 
Thread Tools Display Modes
  #31  
Old January 26th, 2009, 10:31 PM posted to microsoft.public.access.forms
Danny Lesandrini
external usenet poster
 
Posts: 109
Default Dynamic recordset

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.

--
Danny J Lesandrini

www.amazecreations.com




  #32  
Old January 26th, 2009, 11:24 PM posted to microsoft.public.access.forms
RoyVidar
external usenet poster
 
Posts: 417
Default Dynamic recordset

David W. Fenton wrote:
RoyVidar wrote in
:

David W. Fenton wrote:
"David W. Fenton" wrote in
36.94:

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.


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?


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/

--
Roy-Vidar


  #33  
Old January 27th, 2009, 01:50 AM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Dynamic recordset

"Danny Lesandrini" wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #34  
Old January 27th, 2009, 04:29 AM posted to microsoft.public.access.forms
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default Dynamic recordset

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.ControlSou rce =
"SbjNum"
Form_frmDoobloFileDetails.txtDate.ControlSource = "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSourc e =
"Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource = "SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.Contro lSource =
"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" wrote in message
...
Hi Ken,

below is the complete code of the sub procedure.
My application is importing records from another application and I would
like to give an indication to the user whether the record was imported or
not. as you can see I have another recordset called rsVisits and if I find
the record there the field "IsImported" set to True.
I didn't have any other idea how to implement it.

When I moved the line that sets the forms' recordset to rsNew I still get
an
"#Error" in the text boxs

When I added a requery to the form after setting the recordset I get
"#Name?" in the fields.

I also tried to move the dim of rsNew to one of my modules as public
recordset, this also didn't change the error I get.

Do you know what is the meaning of the "#Error" text in a textbox, I
couldn't find any reference to it in the internet?

Thanks,
Orna.

---------------------------

Private Sub lstFilesList_DblClick(Cancel As Integer)
Dim VarItem As Variant
Dim rs As New ADODB.Recordset
Dim ConStr As String
Dim con As New ADODB.Connection
Dim rsDooblo As DAO.Recordset
Dim dbs As DAO.Database
Dim rsVisits As DAO.Recordset
Dim i As Integer

Dim sFilePath As String
Dim sFileName As String
Dim sTableName As String

Set rsNew = New ADODB.Recordset

Set dbs = CurrentDb

sFilePath = DLookup("ImportPath", "tblProjects", "ProjectId=" &
cmbSekerType.Value)
sTableName = DLookup("tblVisitsName", "tblProjects", "ProjectId=" &
cmbSekerType.Value)

For Each VarItem In lstFilesList.ItemsSelected
If VarItem 0 Then
sFileName = lstFilesList.ItemData(VarItem)
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
sFilePath & "\" & sFileName & ";Persist Security Info=False"
con.Open ConStr
rs.Open "SELECT * FROM Export1", con, adOpenKeyset,
adLockReadOnly, adCmdText
Set rsVisits = dbs.OpenRecordset(sTableName, dbOpenDynaset,
dbReadOnly)

rsNew.Fields.Append "SbjNum", rs.Fields("SbjNum").Type, 50
rsNew.Fields.Append "vDate", rs.Fields("vDate").Type, 50
rsNew.Fields.Append "Srvyr", rs.Fields("Srvyr").Type, 50
rsNew.Fields.Append "SbjNam", rs.Fields("SbjNam").Type, 50
rsNew.Fields.Append "IsImported", adBoolean

rsNew.Open

If rs.RecordCount 0 Then
rs.MoveFirst
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
End If

DoCmd.OpenForm "frmDoobloFileDetails", acNormal

Set Form_frmDoobloFileDetails.Recordset = rsNew

If rsNew.RecordCount 0 Then
Form_frmDoobloFileDetails.txtDoobloCode.ControlSou rce =
"SbjNum"
Form_frmDoobloFileDetails.txtDate.ControlSource = "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSourc e =
"Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource =
"SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.Contro lSource =
"IsImported"
Form_frmDoobloFileDetails.lblFileName.Caption = "רשימת
הבקרות בקובץ: " & sFileName
Else
Form_frmDoobloFileDetails.lblFileName.Caption = "אין רשומות
בקובץ: " & sFileName
Form_frmDoobloFileDetails.lblFileName.ForeColor = 255
End If


End If
Next VarItem
End Sub


"Ken Snell (MVP)" wrote:

I believe you've not posted the complete code? I don't see anything in
this
code that establishes the rsNew recordset object? Is it supposed to be a
subset of the rs recordset's data? If yes, why not just open it as a
filtered or unfiltered recordset from the rs recordset, instead of
writing
data into it by a loop through rs data?

Also, I would move your setting of the ControlSource properties to BEFORE
you set the form to that rsNew recordset. Or, do a requery of the form
after
you set those properties.

--

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



"Orna" wrote in message
...
Hi,

I have a form with a listbox control, the listbox displays access db
file
name in a specific directory, each database has only one table with the
same
name and exact schema but with different data. each time the user
double
clicks on one of the rows in the listbox, a dialog form opens and
displays
the data in the table.
when I use an ADODB recordset object to view the data in the remote
database, it works ok. but if I use a recordset I generated in my code
(with
additional fields) , I get the correct number of records but the data
displayed in each one of the text boxs is "#Error".
Below is my code, rsNew is the recordset I am trying to attach to my
dialog.
I am trying to solve this for few days and I will appreciate any help.
Thanks,
Orna.

ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
sFilePath & "\" & sFileName & ";Persist Security Info=False"
con.Open ConStr
rs.Open "SELECT * FROM Export1", con, adOpenKeyset,
adLockReadOnly, adCmdText
Set rsVisits = dbs.OpenRecordset(sTableName, dbOpenDynaset,
dbReadOnly)

rsNew.Fields.Append "SbjNum", rs.Fields("SbjNum").Type, 50
rsNew.Fields.Append "vDate", rs.Fields("vDate").Type, 50
rsNew.Fields.Append "Srvyr", rs.Fields("Srvyr").Type, 50
rsNew.Fields.Append "SbjNam", rs.Fields("SbjNam").Type, 50
rsNew.Fields.Append "IsImported", adBoolean

rsNew.Open

If rs.RecordCount 0 Then
rs.MoveFirst
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
End If

DoCmd.OpenForm "frmDoobloFileDetails", acNormal
Set Form_frmDoobloFileDetails.Recordset = rsNew

If rsNew.RecordCount 0 Then

Form_frmDoobloFileDetails.txtDoobloCode.ControlSou rce =
"SbjNum"
Form_frmDoobloFileDetails.txtDate.ControlSource = "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSourc e =
"Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource = "SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.Contro lSource =
"IsImported"
End If







  #35  
Old January 28th, 2009, 08:46 AM posted to microsoft.public.access.forms
Orna
external usenet poster
 
Posts: 18
Default Dynamic recordset

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.

"David W. Fenton" wrote:

=?Utf-8?B?T3JuYQ==?= wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

  #36  
Old January 28th, 2009, 10:21 AM posted to microsoft.public.access.forms
Orna
external usenet poster
 
Posts: 18
Default Dynamic recordset

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" wrote:

=?Utf-8?B?T3JuYQ==?= wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

  #37  
Old January 28th, 2009, 08:58 PM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Dynamic recordset

=?Utf-8?B?T3JuYQ==?= wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #38  
Old January 28th, 2009, 10:28 PM posted to microsoft.public.access.forms
Orna
external usenet poster
 
Posts: 18
Default Dynamic recordset

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.


"David W. Fenton" wrote:

=?Utf-8?B?T3JuYQ==?= wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

  #39  
Old January 29th, 2009, 04:03 AM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Dynamic recordset

=?Utf-8?B?T3JuYQ==?= wrote in
:

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).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.