Can't use recordset after splitting
in [Help]
|
From: Tia Murchie-Beyma on 21 Jun 2010 17:42 After splitting the database, portions of code that use recordsets (now based on linked tables) don't work. Runtime error 3251 "Operation is not supported for this type of object." I've read past posts on the same issue, but I can't get any solutions to work. Self- taught amateur (so be gentle and use itty-bitty words, please). Even hard-coding the new BE would be fine, really! My users are trying to place folks in jobs and feed widows -- AND their air conditioning just died! I really need to fix this quickly. This code creates the next unique Household_Id (based on adding 1 to the last value added). The line where it breaks is: Household_Id_Recordset.Index = "PrimaryKey" The location of the BE is: D:\Client Statistical Database \ClientDatabase_BE.mdb Option Compare Database Option Explicit Private current_db As Database Private Household_Id_Recordset As Recordset Private Household_Id_Table As TableDef Private Sub Form_Current() Dim Current_Household_Id_String As String Dim Current_Household_Id_Int As Long Dim Update_Sql As String 'Open Next_Household_Id table Set current_db = CurrentDb() Set Household_Id_Table = current_db.TableDefs("Next_Household_Id") Set Household_Id_Recordset = Household_Id_Table.OpenRecordset() Household_Id_Recordset.Index = "PrimaryKey" 'Find out next household id Current_Household_Id_String = Household_Id_Recordset.Fields(0).Value 'Pad left with zeros Current_Household_Id_String = Right("000000" & Current_Household_Id_String, 6) 'Write household id into the form Me![Household_Id] = Current_Household_Id_String 'Add 1 to next household id table Current_Household_Id_Int = Val(Current_Household_Id_String) Current_Household_Id_Int = Current_Household_Id_Int + 1 Update_Sql = "UPDATE Next_Household_Id SET Next_Household_Id.Next_Id = " & Trim(Str(Current_Household_Id_Int)) DoCmd.RunSQL (Update_Sql) End Sub Any ideas are very welcome! If I can get this bit working, I should be able to apply it to do in 8 other spots of code, and thus make some very hard-working, sweaty charity workers a bit happier. Sigh. Tia
From: Salad on 21 Jun 2010 19:24 Tia Murchie-Beyma wrote: > After splitting the database, portions of code that use recordsets > (now based on linked tables) don't work. Runtime error 3251 > "Operation is not supported for this type of object." I've read past > posts on the same issue, but I can't get any solutions to work. Self- > taught amateur (so be gentle and use itty-bitty words, please). Even > hard-coding the new BE would be fine, really! My users are trying to > place folks in jobs and feed widows -- AND their air conditioning just > died! I really need to fix this quickly. > > This code creates the next unique Household_Id (based on adding 1 to > the last value added). > The line where it breaks is: Household_Id_Recordset.Index = > "PrimaryKey" > The location of the BE is: D:\Client Statistical Database > \ClientDatabase_BE.mdb > > > Option Compare Database > Option Explicit > > Private current_db As Database > Private Household_Id_Recordset As Recordset > Private Household_Id_Table As TableDef > > Private Sub Form_Current() > > Dim Current_Household_Id_String As String > Dim Current_Household_Id_Int As Long > Dim Update_Sql As String > > 'Open Next_Household_Id table > Set current_db = CurrentDb() > Set Household_Id_Table = current_db.TableDefs("Next_Household_Id") > Set Household_Id_Recordset = Household_Id_Table.OpenRecordset() > Household_Id_Recordset.Index = "PrimaryKey" > > 'Find out next household id > Current_Household_Id_String = > Household_Id_Recordset.Fields(0).Value > 'Pad left with zeros > Current_Household_Id_String = Right("000000" & > Current_Household_Id_String, 6) > > 'Write household id into the form > Me![Household_Id] = Current_Household_Id_String > > 'Add 1 to next household id table > Current_Household_Id_Int = Val(Current_Household_Id_String) > Current_Household_Id_Int = Current_Household_Id_Int + 1 > Update_Sql = "UPDATE Next_Household_Id SET > Next_Household_Id.Next_Id = " & Trim(Str(Current_Household_Id_Int)) > > DoCmd.RunSQL (Update_Sql) > > End Sub > > > Any ideas are very welcome! > > If I can get this bit working, I should be able to apply it to do in 8 > other spots of code, and thus make some very hard-working, sweaty > charity workers a bit happier. Sigh. > > Tia You can padzero using format. Ex: num = 1 ? format(num,"00000000") 00000001 num = 1234 ? format(num,"00000000") 00001234 num = 12345678 ? format(num,"00000000") 12345678 You could use Dlookup to get the next id since it appears there's only one record in the table Dim lngID as Long lngID = Dlookup("Next_Id","Next_Household_Id") Some people get the nextID using Dmax to get that maximum id value in a table and adding 1 to it without using another table to hold the next key value. Ex: lngNextID = Dmax("YourIDFldName","YourTableName") + 1 Splitting the database should not affect the working of the database. Modifying code in 7-8 in other locations simply adds the opportunity for more errors. What I'd recommend is put the word STOP under the Private Sub Form_Current() line. This will place you in debug/step mode. Then step thru the code and determine which line your code is blowing up on. Knowing where it's blowing up would appear crucial to solving your problem.
From: Tia Murchie-Beyma on 21 Jun 2010 20:15 Salad, thank you for your ideas on padding zero and incrementing Household_Id. The line where this module breaks is: Household_Id_Recordset.Index = "PrimaryKey" and the reason it breaks is that table-type recordsets don't work with linked tables, which is naturally what I have after splitting. That's why splitting affected the code, and why I need to change it in several different places in the application. I knew how to use recordsets, more or less, and how handy that was! But now those forms are broken. Tia
From: David W. Fenton on 21 Jun 2010 21:08 Tia Murchie-Beyma <tiamurch(a)gmail.com> wrote in news:a2f1ca73-efd7-4e33-9ad0-de86d5d36dc8(a)x21g2000yqa.googlegroups.co m: > This code creates the next unique Household_Id (based on adding 1 > to the last value added). > The line where it breaks is: Household_Id_Recordset.Index = > "PrimaryKey" Sounds like code that used to use SEEK, which works only on tables. In a brief scan of the code, I didn't see a SEEK, though. The other thing is that you can't open a table-type recordset on a linked table. Neither of these is something to worry about. You can always get the same functionality by opening a database object that points to the back end. Of course, in general, there is hardly ever any justification for using SEEK, as it shines only when you're jumping around a large recordset thousands of time. Otherwise, you should use FindFirst or just filter your recordset to the desired records. As to table-type recordsets, I see no reason not to leave off the option entirely and let the default recordset type be used (dynaset). -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 21 Jun 2010 21:09
Tia Murchie-Beyma <tiamurch(a)gmail.com> wrote in news:f8c9972a-9649-4dfb-ae83-166904a73a40(a)s9g2000yqd.googlegroups.com : > the reason it breaks is that table-type recordsets don't work with > linked tables, which is naturally what I have after splitting. > That's why splitting affected the code, and why I need to change > it in several different places in the application. I knew how to > use recordsets, more or less, and how handy that was! But now > those forms are broken. I've never declared a type-type recordset nor used SEEK. And I have never had a form broken by problems affecting recordsets, since I use bound forms, and let Access manage the recordsets behind them. It sounds to me like your unsplit application had a lot of things in it that I'd classify as premature optimization -- things sound like they were too complicated by half. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |