Solved

Error: Object Invalid or No Longer Set with EXISTING table

Posted on 2002-03-21
8
2,003 Views
Last Modified: 2007-11-27
In my Access application, there is a temporary table that is regenerated through a make-table query every time I need it (a table of 40 random questions from a larger table of 2500). The make-table SQL is run from VBA code.

After creating the table, I desperately need a sequentially numbered field, simply to have the records numbered 1 through 40. (There is a very good reason to need this number - trust me.)

Because I know of no other method to get a sequentially numbered field, I wrote the following bit of VBA:


Dim tdf As DAO.TableDef, rst As DAO.Recordset
Dim fld As DAO.Field
Dim i As Integer
   
Set tdf = CurrentDb.TableDefs("temptable")
Set fld = tdf.CreateField("seqnr", dbInteger)
tdf.Fields.Append fld

Set rst = CurrentDb.OpenRecordset( _
                          "SELECT * FROM temptable")
With rst
   Do Until .EOF
      i = i + 1
      .Edit
      .Fields("seqnr").Value = i
      .Update
      .MoveNext
   Loop
   .MoveFirst
End With


My problem is this: on the line where I Set the tdf object variable, no error is reported. But on the next line, I get the 'Object invalid or no longer set' error (3420). Now there is no doubt at all that the "temptable" table exists. So my questions are:

1. How come I get this error on this very straightforward piece of code? Why can't I refer to a TableDef in this very simple manner?

2. Is there a smarter way of getting a sequentially numbered field in my data? I would much prefer an extra column in my queries that numbers the result records, but have no clue. And an AutoNumber field is no option, since there is already an AutoNumber field in my temptable...

Double points for two answers...
0
Comment
Question by:FlorisMK
  • 4
  • 3
8 Comments
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
Hi florisMK,

Try the following method, it should fill the numbers upon declaration... (no need to loop through the records, as it uses the AutoNumber mechanism):

Dim tdf As DAO.TableDef, rst As DAO.Recordset
Dim fld As DAO.Field
Dim i As Integer
   
Set tdf = CurrentDb.TableDefs("temptable")
Set fld = tdf.CreateField("seqnr", dbInteger)
fld.Attributes = fld1.Attributes + dbAutoIncrField
tdf.Fields.Append fld



Please resolve the following questions:

Windows 2000 topic area
http://www.experts-exchange.com/jsp/qShow.jsp?ta=win2k&qid=20225639 dated 11/03/01
http://www.experts-exchange.com/jsp/qShow.jsp?ta=win2k&qid=20181512 dated 09/11/01

Microsoft Access topic area
http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20181835 dated 09/12/01

JavaScript topic area
http://www.experts-exchange.com/jsp/qShow.jsp?ta=javascript&qid=20215186 dated 10/26/01
Please notice that the above is a link Q which you can delete as no one yet have replied with a comment!

http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20241875 dated 11/29/01

If you need any forther assistance resolving the above Qs, please post a 0 (zero) pionts question at the Community Support Topic Area [ http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt ], paste the link to the qestion you need help on, an add a short description of what you need, and a moderator will help you out in no time...

Thanx!

Nosterdamus
0
 
LVL 2

Author Comment

by:FlorisMK
Comment Utility
AH - that would be half the solution! Should have thought of that one myself - create an AutoNumber field! But it doesn't solve the main problem: I get error 3420 when referring to the TableDef, so I don't even get the chance to create the new field:

Set tdf = CurrentDb.TableDefs("temptable")
' Executed with no error, but...

' setting a watch on the tdf variable shows me that
' the 'Object is invalid or no longer set', and

Set fld = tdf.CreateField("seqnr", dbInteger)
' generates RTE 3420: Object invalid or no longer set

This while I know for a fact that the table is present and current (setting a watch on CurrentDB shows me the temptable in the TableDefs collection).

PS: Will look into those other questions...
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 200 total points
Comment Utility
Effectively when you're only interested in having a sequence number, all you need is an extension to your query like.
select dcount("questionID","tblQuestions","questionID<=" & questionID), field1, field2 from tblQuestions;

This will enumerate the questions, assuming the questionID is unique. Based on this query you can create again a maketable query...

Nic;o)

0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Microsoft has an article about ranking in queries where the use a sub-query to do the same as the above DCOUNT:
http://support.microsoft.com/support/kb/articles/Q208/9/46.ASP

Nic;o)
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 2

Author Comment

by:FlorisMK
Comment Utility
Nico, that is an excellent solution to the enumeration problem - obviously I'm not too familiar with domain functions (-:

It bypasses the problem of the error referring to the tabledef, so I'm inclined to give you points. Want to give it a few more days though, because I hope there is an obvious answer to the error 3240 problem...
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I can look into that, but then you'll need to compact and zip the .mdb and drop it in my nico5038 mailbox "at"  yahoo.com

Nic;o)
0
 
LVL 2

Author Comment

by:FlorisMK
Comment Utility
I'll just accept your domain function as a solution to my problem - thanks!
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
You're welcome, success with the application !

Nic;o)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now