TableDefs Field.Append does not work in Access2000

[Dan01]

Dim NQ As Database
Dim tdf As TableDef
Set NQ = OpenDatabase("mypath\NQ.mdb&quot
Set tdf = NQ.TableDefs!ECLR200D
tdf.Fields.Append tdf.CreateField("InputDate", dbDate)
tdf.Fields.Append tdf.CreateField("CompletedDate", dbDate)
NQ.Close

This no longer works since we upgraded to Access2000 from Access97. Do someone have example code in ADO or other DAO code that still works to accomplish this? Thanks, Dan.

 

[cmmrfrds]

Explicit DAO definition needed in Access 2000. Make sure you have a reference set to the DAO library probably 3.6

Dim NQ As DAO.Database
Dim tdf As DAO.TableDef

Access 2000 has both DAO and ADO data objects and some of the defaults are ADO (depending on ADP or MDB) so an explicit definition of which type of object is needed.

 

[Dan01]

'Dim NQ As Database
'Dim tdf As TableDef
Dim NQ As DAO.Database
Dim tdf As DAO.TableDef
Set NQ = OpenDatabase("mypath\NQ.mdb&quot
Set tdf = NQ.TableDefs!ECLR200D
Set tdf = NQ.TableDefs
tdf.Fields.Append tdf.CreateField("InputDate", dbDate)
tdf.Fields.Append tdf.CreateField("CompletedDate", dbDate)
NQ.Close

This also fails. I have DAO 3.6 referenced. The error message is "Run time error 3265: Item not found in this collection" the code stops at the Set tdf = NQ.TableDefs!ECLR200D. Dan.

 

[cmmrfrds]

Assuming the table name is correct, are you sure it is opening the database?

Set NQ = OpenDatabase("mypath\NQ.mdb&quot
debug.print "database = " NQ
Set tdf = NQ.TableDefs!ECLR200D

 

[Dan01]

Good point, it was pointing to an external db. I changed mypath to the path of the current db since the code and table are in the same database. Now, I'm getting VB run-time error 3734: "The database has been placed in a state .... that prevents it from being opened or locked". Please advise if you can. Thanks, Dan.

 

[Dan01]

cmmrfrds, I've got it working. Thanks for getting me back on track (it would help working with the db you think you're working with ). This is the code that works:

Dim NQ As DAO.Database
Dim tdf As DAO.TableDef
Set NQ = CurrentDb()
Set tdf = NQ.TableDefs!ECLR200D
tdf.Fields.Append tdf.CreateField("InputDate", dbDate)
tdf.Fields.Append tdf.CreateField("CompletedDate", dbDate)
NQ.Close

 

[cmmrfrds]

This was caused by opening another copy of the database you were working in.
"VB run-time error 3734"

For the database you are working in you got the correct syntax.
Set NQ = CurrentDb()

I've done that before when testing. Good luck.