Problems with tabledef and fields collection in Acc2000 (with DAO 6)

  • Thread starter Thread starter Bogdan Zamfir
  • Start date Start date
B

Bogdan Zamfir

Hi,

I have a strange problem here

I have the following code:

Dim tb As TableDef
Set tb = CurrentDb.TableDefs("Events")
Dim fld As Field, I As Integer
For I = 0 To tb.Fields.Count - 1

When I run the FOR line, I get the error :

Runtime error 3420 : Object invalid or no longer set.

What is wrong here? Intellisense show Fields colection for Tabledef object
(as expected)

Regards,
Bogdan
 
gilligan is correct. currentdb must be explicitly defined as a database object before the other objects may be defined.

set db=currentdb

----- Bogdan Zamfir wrote: -----

Hi,

I have a strange problem here

I have the following code:

Dim tb As TableDef
Set tb = CurrentDb.TableDefs("Events")
Dim fld As Field, I As Integer
For I = 0 To tb.Fields.Count - 1

When I run the FOR line, I get the error :

Runtime error 3420 : Object invalid or no longer set.

What is wrong here? Intellisense show Fields colection for Tabledef object
(as expected)

Regards,
Bogdan
 
Just to explain it a bit more: after the line of code

Set tb = CurrentDb.TableDefs("Events")

is executed, the (temporary) object CurrentDb is destroyed
and the tb is orphaned and therefore VBA / DAO cannot use
it anymore.

There are a few minor points:

1. The DAO version is 3.6, not 6 as per the Subject you
posted.

2. Since Field and Fields are object of both DAO and ADO
so you should adopt safe coding and disambiguate them in
your decs like:

Dim fld As DAO.Field

With the above dec, regardless whether ADO Library is
included in the References, the object fld will always be
assigned to the correct type (DAO) rather than relying on
Access VBA to default fld to a type which can be the
incorrect ADO.Field.

HTH
Van T. Dinh
MVP (Access)
 
Is this what you are trying to do?

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim intCounter As Integer
Dim strTableName As String
strTableName = "tblEvent"
Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
For Each fld In tdf.Fields
Debug.Print fld.Name
Next fld
 
Hi,
Just to explain it a bit more: after the line of code

Set tb = CurrentDb.TableDefs("Events")

is executed, the (temporary) object CurrentDb is destroyed
and the tb is orphaned and therefore VBA / DAO cannot use
it anymore.
Click to expand...

I usually define a variable for db, now was just a "quick and dirty" test
code, to see actually all properties of a Field object
Howevere, in this case, why if use an DAO recordset OBJECT, with some code
like set rs = currentdb.openrecordset("events"), I don't get that error, and
I can access Fields collection?
There are a few minor points:

1. The DAO version is 3.6, not 6 as per the Subject you
posted.
Click to expand...

You're right, I know
2. Since Field and Fields are object of both DAO and ADO
so you should adopt safe coding and disambiguate them in
your decs like:

Dim fld As DAO.Field
Click to expand...

I don't use to use both ADO and DAO in the same aplication, except in
special cases, when it's absolutelly necessary. And as I told, It was just a
small test, created in temp module.

But your point is good.

Thanks,
Bogdan
 
Bogdan Zamfir said:
Hi,


I usually define a variable for db, now was just a "quick and dirty"
test code, to see actually all properties of a Field object
Howevere, in this case, why if use an DAO recordset OBJECT, with some
code like set rs = currentdb.openrecordset("events"), I don't get
that error, and I can access Fields collection?
Click to expand...

Because a DAO recordset maintains a reference to its parent object, thus
keeping that object "alive" as long as the recordset is open. I don't
know offhand whether an ADO recordset does the same.
 
Back
Top