Solved: VBA to add a table field

[TheAntiGates]

I want to add a field, and this is what I came up with, but .recordcount is read-only. I'm only 4 inches away from happiness, right?

Apologies for any other bogus code below. I'm truly winging it here.:help Sub ModCentYr_insert_fld()
Dim rs As Recordset, tdfNewtable As TableDef
Dim iFieldcount As Long
Set rs = CurrentDb.OpenRecordset("tblFoo", dbOpenDynaset)
With rs
iFieldcount = .RecordCount
.RecordCount = iFieldcount + 2
.AddNew 'get set for new data ... need this??
.Fields(iFieldcount + 1) = "ACISCN" ' or +0 ??
.Fields(iFieldcount + 2) = "ACISYR" ' or +1 ??
.Update 'need this??
.Close
End With
Set rs = Nothing
End SubBonus question: I don't have a prayer of doing this on a linked table, right?

[TheAntiGates]

Well, goody, I found Mr. salvation:
dim fldNew As Field
Set fldNew = .CreateField("myfield")

Is the rest of the code kosher?

[stanl]

In the first place Recordcount is the number of Rows, not the number of fields. 2nd, use ADOX which will work on linked tables (including ISAMS).

Stan

[Norie]

Gates

You could use a ALTER (not CREATE :doh: ) TABLE SQL statement to do this.

And you could do that from code as well.

[TheAntiGates]

Definitely too much LDS in the sixties - recordcount was not intended. This worked (sans .recordcount - duh, doooh!) on the direct [unlinked]table, so I get to live one more day without ADO - woohoo!

[TheAntiGates]

Just saw yours - thanks Norie. That sounds most practical. (Ultimately I ended up just rebuilding and repopulating the table, a laSet tdfNewtable = db.CreateTableDef("myTable")
with tdfNewtable
set fldnew=.CreateField("fldname")
fldNew.Size = 5
fldNew.Type = dbText
.Fields.Append fldNew
end with
currentdb.TableDefs.Append tdfNewtable

[stanl]

so I get to live one more day without ADO - woohoo!


I assume that was intended against me. I guess if you want to stay in the 60's using 'TableDef' in your code is the way to go. ADO has its faults, but at least it is trying to not be restricted to a specific DB.
.02
Stan

[TheAntiGates]

Your ADO additions are of course worthwhile for those who wish to take advantage of it. So, no offense; the contribution is appreciated. I personally dislike ADO, even if it is faster and has greater capabilities and less limitations, because it is conceptually confusing and because it seems to be a replacement for something that "ain't broke." (Hail Microsoft! Hail Heil Gates!)

But since there are solution-achieving people willing to mess with it, and you're generous enough to aid their usage - hey, "Go Nuts!"

Ultimately the M$ counterbusinessproductivity unit will cease to recognize DAO anyway, beginning with refusal to compile it; it's only a matter of time. They will eventually get my mind right, just like Cool Hand Luke. They're the Man! We're still shakin' it, Boss!

[stanl]

I think it comes down to one expanding one's point of view. If you live and can make a living in a strictly DAO world, then your point is well taken. However, if you have been 'downsized' or laid-off, because CreateObject("Access.Application") no longer cuts it, yet ADO will still solve the basic problem with Jet 4.0 then your point is solipsism.

Please give me an example of ADO being 'conceptually confusing', as it was by far an easy thing to master, while DAO remains an arcane, outdated, application-specific holdover.
Stab