Finding the number of fields in an Access table

[Ivan]

I am designing a DB to store the data form anumber of instruments, the
instruments (PC controlled) export their data as Excel files. Importing these
is easy. However normalized they are not. The number of columns (Fields)
varies from run to run and to build and run the queries that will normalize
the data I need to know how many columns I have. I am sure the function
exists somewhere in VBA/Access but I cannot find it.
Anybody able to help

 

[Ken Snell MVP]

Dim dbs As DAO.Database
Dim tdf As DAO.TableDefs
Dim lngFieldCount As Long
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("NameOfTable")
lngFieldCount = tdf.Fields.Count
tdf.Close
Set tdf = Nothing
dbs.Close
Set dbs = Nothing

 

[Ivan]

Thanks but this does not work, does it depend on a object library that may
not be referenced

 

[Ivan]

One line is wrong
Dim tdf As DAO.TableDefs
should be
Dim tdf As DAO.TableDef
and
tdf.Close
and
dbs.Close
prevent compilation and appear to unecessary

 

[Ken Snell MVP]

Sorry for errors in air code:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDefs
Dim lngFieldCount As Long
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("NameOfTable")
lngFieldCount = tdf.Fields.Count
Set tdf = Nothing
dbs.Close
Set dbs = Nothing
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Douglas J. Steele]

Still wrong, I'm afraid.

Dim tdf As DAO.TableDef

And if all you're trying to do is a single table,

lngFieldCount = CurrentDb.TableDefs("NameOfTable").Fields.Count

should be sufficient.

 

[Ken Snell MVP]

Drat... I deleted that "s" or at least I tried to....

 

[David W. Fenton]

Dim dbs As DAO.Database
Dim tdf As DAO.TableDefs
Dim lngFieldCount As Long
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("NameOfTable")
lngFieldCount = tdf.Fields.Count
tdf.Close
Set tdf = Nothing
dbs.Close
Set dbs = Nothing

Closing a database variable initialized with CurrentDB is a waste of
a line of code. It does nothing. It causes no harm, but it's good
not to do it, as if you, say, replaced the CurrentDB with
DBEngine(0)(0), it can cause an error (though it doesn't in all
versions of Access).

 

[David W. Fenton]

Dim dbs As DAO.Database
Dim tdf As DAO.TableDefs
Dim lngFieldCount As Long
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("NameOfTable")
lngFieldCount = tdf.Fields.Count
Set tdf = Nothing
dbs.Close
Set dbs = Nothing

The dbs.Close shouldn't be there. You can't close the database that
is currently open in the user interface.