TableDefs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using the following code as an example:

Private Sub Button_Click()

Dim rst as Recordset
Dim tdf as TableDef

Set rst = Application.Currentdb.OpenRecordset("tblSuppliers")

MsgBox rst.Name

Set tdf = Application.CurrentDb.TableDefs("tblStaff")

MsgBox tdf.Name

End Sub

When run, a message box appears with the name of the recordset, but then an
error message appears saying "Object invalid or no longer set"

Any suggestions?
 
Thank you, works perfectly now.

Out of interest, any ideas why the code I was originally using would work
for recordsets, but not tabledefs?
 
CurrentDb creates a temporary database object reference that is closed after
the line is executed. It should really only be used for a single line
command like:

CurrentDb.Execute "Delete * from Table1"

I'm not sure why this works with the Recordset object, but that's the ONLY
object that it will work for. You are nearly always better off to create
and set a database variable.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
In
Roger Carlson said:
CurrentDb creates a temporary database object reference that is
closed after the line is executed. It should really only be used for
a single line command like:

CurrentDb.Execute "Delete * from Table1"

I'm not sure why this works with the Recordset object, but that's the
ONLY object that it will work for. You are nearly always better off
to create and set a database variable.
Click to expand...

The Recordset object maintains a reference to its parent Database
object, thus keeping it "alive". TableDefs and most other DAO objects
don't do that.
 
Roger Carlson said:
CurrentDb creates a temporary database object reference that is closed after
the line is executed.
Click to expand...

Yeah, that one just bit me for the third time in several years. This
time it only took a minute or so to recall my stupidity. Hopefully
next time it will only be a few seconds. Especially seeing as how
I've admitted how dumb I can occasionally be in public. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top