Can't DAO Append because of RT3211

[Bill]

With the code below, I can't find what is causing the RT 3211
you see noted at the last statement.

==============================================================
Option Compare Database
Option Explicit
Dim dbTMS As DAO.Database 'Object variable for our database
Dim rsInstProp As DAO.Recordset 'Object variable for our DAO recordset
Dim fldName As DAO.Field 'Ojbect variable for members of the field
collection
Dim tdfInstProp As DAO.TableDef 'Object variable for InstProperties table
definition
Dim stSQL As String 'Working string for our SQL queries
Dim booNotFound As Boolean

Public Sub DAO_Example2()

'IPDatabase = DLookup("InstDatabase", "InstProperties") 'Fetch name TMS
backend DB

IPDatabase = "c:\gcc\gcc data.mdb"
Set dbTMS = OpenDatabase(IPDatabase) 'Ok. Open backend DB
Set rsInstProp = dbTMS.OpenRecordset("InstProperties") 'Default is Dynaset
Set tdfInstProp = dbTMS.TableDefs("InstProperties") 'Need to look at
InstProp tbldefs

booNotFound = True

For Each fldName In tdfInstProp.Fields 'For each field in the
fields collection
If fldName.Name = "InstDBVersion" Then 'This the field we're
looking for?
booNotFound = False 'YES, set
not-found-indicator to false
Exit For 'We're done searching the
collection
End If
Next fldName 'NO, step to next field


If booNotFound = True Then 'If version missing, so
are the others.
Set fldName = tdfInstProp.CreateField("InstDBVersion", dbSingle)
tdfInstProp.Fields.Append fldName <============ Run-time error
'3211'
Unable to
lock table because it's already
in use by
another person or process.
..
..
..Blah Blah Blah
==============================================================

 

[Douglas J. Steele]

Error 3211 is "The database engine couldn't lock table '|' because it's
already in use by another person or process."

Are you the only user of the database when you're trying to run the code?

 

[Bill]

Yes, I made sure of it. I ran that code without any other
windows open except Access and Outlook Express.

 

[Dirk Goldgar]

With the code below, I can't find what is causing the RT 3211
you see noted at the last statement.

==============================================================
Option Compare Database
Option Explicit
Dim dbTMS As DAO.Database 'Object variable for our database
Dim rsInstProp As DAO.Recordset 'Object variable for our DAO
recordset Dim fldName As DAO.Field 'Ojbect variable for
members of the field collection
Dim tdfInstProp As DAO.TableDef 'Object variable for InstProperties
table definition
Dim stSQL As String 'Working string for our SQL queries
Dim booNotFound As Boolean

Public Sub DAO_Example2()

'IPDatabase = DLookup("InstDatabase", "InstProperties") 'Fetch name
TMS backend DB

IPDatabase = "c:\gcc\gcc data.mdb"
Set dbTMS = OpenDatabase(IPDatabase) 'Ok. Open
backend DB Set rsInstProp = dbTMS.OpenRecordset("InstProperties")
'Default is Dynaset Set tdfInstProp =
dbTMS.TableDefs("InstProperties") 'Need to look at InstProp
tbldefs
booNotFound = True

For Each fldName In tdfInstProp.Fields 'For each field in
the fields collection
If fldName.Name = "InstDBVersion" Then 'This the field
we're looking for?
booNotFound = False 'YES, set
not-found-indicator to false
Exit For 'We're done
searching the collection
End If
Next fldName 'NO, step to next
field

If booNotFound = True Then 'If version
missing, so are the others.
Set fldName = tdfInstProp.CreateField("InstDBVersion", dbSingle)
tdfInstProp.Fields.Append fldName <============ Run-time error
'3211'
Unable
to lock table because it's already
in use
by another person or process.
.
.
.Blah Blah Blah
==============================================================

See my response (somewhat belated) in your earlier thread. You have a
recordset opened on the table, so you can't change its design.

 

[Bill]

Doug,
Dirk noticed that the DAO.Recordset was opened. I simply
needed to defer the open until after all the TableDefs activity
was completed.
Thanks,
Bill