Solved

Error: Object Invalid or No Longer Set with EXISTING table

Posted on 2002-03-21
8
2,009 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
ID: 6885324
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
ID: 6885525
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
ID: 6891206
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
ID: 6891212
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Author Comment

by:FlorisMK
ID: 6892338
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
ID: 6892364
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
ID: 6899158
I'll just accept your domain function as a solution to my problem - thanks!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6899579
You're welcome, success with the application !

Nic;o)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

911 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

20 Experts available now in Live!

Get 1:1 Help Now