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. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|