Invalid Operation when creating TableDef

[MFB via AccessMonster.com]

I'm receiving an "Invalid Operation" error when attempting to set the
DecimalPlaces property during creation of a tabledef. The table is being
created based upon the values stored in another table. I've enclosed the code
segments below. This is an Access 2000 database.

Dim db As DAO.Database, rsClientFactor As DAO.Recordset, tdfExtraInfo As DAO.
TableDef

Set tdfExtraInfo = db.CreateTableDef("tblSSNSourceTEST")
With tdfExtraInfo
.Fields.Append .CreateField("EmpSSN", dbText, 11)
.Fields.Append .CreateField("DataSource", dbText, 10)
Do Until rsClientFactor.EOF
.Fields.Append .CreateField("Factor" & rsClientFactor!Factor,
dbDouble)
If Not IsNull(rsClientFactor!DecimalPlaces) Then
Set prpProp = .Fields("Factor" & rsClientFactor!Factor).
CreateProperty("DecimalPlaces", dbByte, 0)
.Fields("Factor" & rsClientFactor!Factor).Properties.Append
prpProp
End If
rsClientFactor.MoveNext
Loop

I've also tried the following instead of the property setting code above:

.Fields("Factor" & rsClientFactor!Factor).Properties.Append .
CreateProperty("DecimalPlaces", dbByte, rsClientFactor!DecimalPlaces)

Both methods return the error. Any help will be appreciated.
-MFB

 

[George Nicholson]

I don't think you need to create a DecimalPlaces property, it already
exists. I just create a new table with a new field (double) and the property
was already there by default.

This would explain the InvalidOperation.

HTH,

 

[MFB via AccessMonster.com]

Thanks for the quick response George. Unfortunately, I don't think that the
property is actually set. If I remove the property setting from the code, the
table is built and the new field is given a data type of double. The decimal
places is shown as "Auto". If I check to see if the property exists by
running the following, it tells me that the property does not exist.

currentdb.TableDefs("tblSSNSourceTEST").Fields("Factor3").Properties
("DecimalPlaces").Value

If I open the table in design mode and manually change the Decimal Places
property to 2 and then rerun the code above, it returns 2.

This makes me believe that the property isn't created automatically. Even if
it was, I'd like to be able to explicitly specify the value when running the
create tabledef code.

Hopefully, this makes sense.
-MFB

 

[Dirk Goldgar]

I'm receiving an "Invalid Operation" error when attempting to set the
DecimalPlaces property during creation of a tabledef. The table is
being created based upon the values stored in another table. I've
enclosed the code segments below. This is an Access 2000 database.

Dim db As DAO.Database, rsClientFactor As DAO.Recordset, tdfExtraInfo
As DAO. TableDef

Set tdfExtraInfo = db.CreateTableDef("tblSSNSourceTEST")
With tdfExtraInfo
.Fields.Append .CreateField("EmpSSN", dbText, 11)
.Fields.Append .CreateField("DataSource", dbText, 10)
Do Until rsClientFactor.EOF
.Fields.Append .CreateField("Factor" & rsClientFactor!Factor,
dbDouble)
If Not IsNull(rsClientFactor!DecimalPlaces) Then
Set prpProp = .Fields("Factor" & rsClientFactor!Factor).
CreateProperty("DecimalPlaces", dbByte, 0)
.Fields("Factor" &
rsClientFactor!Factor).Properties.Append prpProp
End If
rsClientFactor.MoveNext
Loop

I've also tried the following instead of the property setting code
above:

.Fields("Factor" &
rsClientFactor!Factor).Properties.Append .
CreateProperty("DecimalPlaces", dbByte, rsClientFactor!DecimalPlaces)

Both methods return the error. Any help will be appreciated.
-MFB

You can't create a user-defined property for the TableDef (which is what
DecimalPlaces is) until after the TableDef has been appended to the
database's TableDefs collection.

I suggest you first create all the fields, then append the tabledef, and
then go back and create the property for each "Factor" field.

 

[George Nicholson]

Agreed.
When I went back and created the field via code (rather than manually), I
recreated your results: "3270roperty not found".
And when I tried to create the property a 2nd time I got "3367 Cannot
append: an object with that name already exists in the collection.

soooo, I don't know why you're getting Invalid Operation. Sorry.

I leave you with 5 observations, I have no idea how much bearing they might
have (sort of grasping at straws)
- You are working in a Jet workspace, right? (per Help, this is a
requirement when appending Properties to Fields)
- in the code supplied, prpProp isn't specifically declared as
DAO.Property. Wouldn't hurt.
- in the code supplied, tdfExtraInfo hasn't been Appended to TableDefs
collection. Can't see any relationship between this & your error though.
- Maybe try rewriting your problem lines as if you aren't in the middle
of a "With" clause. See if verbosity helps any.

For:
.Fields("Factor" & rsClientFactor!Factor).Properties.Append
..CreateProperty("DecimalPlaces", dbByte, rsClientFactor!DecimalPlaces)
Don't you mean:
.Fields("Factor" & rsClientFactor!Factor).Properties.Append
..Fields("Factor" & rsClientFactor!Factor).CreateProperty("DecimalPlaces",
dbByte, 0)


HTH,

 

[MFB via AccessMonster.com]

Dirk,

You nailed it. Thanks for your help!

-MFB