Return Count of Columns in an Access Table via VBA

A

Amcbdc1

I need to return the number of columns in a table using VBA code. That way I
can tell if i need to add more columns to hold the data that will be inserted
into the database via code.
 
C

Chris O'C via AccessMonster.com

Public Function countCols()
On Error GoTo ProcErr

Dim db As Database
Dim strtbl As String

strtbl = "tablename"
Set db = CurrentDb
MsgBox db.TableDefs(strtbl).Fields.Count

ProcExit:
Set db = Nothing

Exit Function

ProcErr:
MsgBox Err.Number & vbCrLf & Err.Description
Err.Clear
Resume ProcExit
End Function


Chris
 
J

John W. Vinson

I need to return the number of columns in a table using VBA code. That way I
can tell if i need to add more columns to hold the data that will be inserted
into the database via code.
Click to expand...

Not in any rationally designed normalized database, you won't!!!

That said, CurrentDb.Tabledefs("MyTableName").Fields.Count will return the
number of fields.
 
A

Armen Stein

Not in any rationally designed normalized database, you won't!!!
Click to expand...

Hi John,

That's true. But there's a case for programmatically adding columns
to a work table (flattening the data crosstab style) before exporting
it to Excel or similar. Very handy.

But that doesn't seem to be the situation here, so your advice stands.
:)

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Top