tabledef becomes invalid (1 Viewer)

[joebater]

Hi all,

I would be grateful if anyone could enlighten me.

The database I'm writing is to hold information on the technical specifications on components of products used by the engineering company I work for. There is one table per specification document, and one table linked by a one-to-many relation for the component information. Occasionally new specifications (and revised ones) appear so I've written VBA code to add new tables and establish relations between them. This all works fine and the world is a happy place.

Now as part of the audit trail I have a separate (unrelated) table whose fields represent the componentID, and then a field for each technical specification document. Each entry is then the count of reponses to the documents for a component.

The code I'm writing then is to take this table and append a new field to it. And now things go horribly wrong.

Sanitised (i.e. with all information about the company etc gone) code follows:

Private Function AddFieldToResponseTable(strName as String) as Boolean
'On Error Goto Err_AddFieldToResponseTable 'deliberate comment

Dim tdf as Tabledef, fld as Field
Set tdf = CurrentDb.TableDefs("tblResponseCounts")
Set fld = tdf.CreateField(strName, dbInteger)
tdf.Fields.Append fld

AddFieldToResponseTable = True

Exit ....

When the code gets to the line Set fld = tdf... I get an error "3420 Object invalid or no longer set.", which when I try to examine the tdf object in the immediate window is true enough.

What I can't figure is where it's gone and how I lost it!

All suggestions/comments/criticisms gratefully accepted.

Many thanks in advance.
j.

 

[ritchieroo]

The problem is using CurrentDb directly. Here's what the help file says about the funtion:

The CurrentDb function returns an object variable of type Database that represents the database currently open in the Microsoft Access window.

...

In order to manipulate the structure of your database and its data from Visual Basic, you must use Data Access Objects (DAO). The CurrentDb function provides a way to access the current database from Visual Basic code without having to know the name of the database. Once you have a variable that points to the current database, you can also access and manipulate other objects and collections in the DAO hierarchy.

You can either declare a Database object, and set it equal to CurrentDB. Or, use the DAO collection directly, i.e. DBEngine(0)(0).

I got this code working

  With DBEngine(0)(0).TableDefs("tblTest")
    .Fields.Append .CreateField("test2", dbInteger)
  End With

hth

 

[joebater]

Absolutely brilliant. I also see that I have go back to the books with DAO! Many thanks again.