By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,398 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Macro condition: how to determine if a column exists?

P: n/a
Greetings all,

I have a macro that should add a column to a table if the column doesn't
already exist. Using the macro condition, how can I test for whether a
specific column name in a specific table exists?

TIA for any help,
Steve Lord

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
I'm pretty sure you can't do this without writing VBA code. In VBA code, you
can get a reference to the tabledef from the tabledefs collection, then
attempt to read the name property of the field object from the fields
collection. If that generates an error, the field does not exist.

On Wed, 18 Aug 2004 10:58:26 GMT, steve lord <dr*********@hotmail.com> wrote:
Greetings all,

I have a macro that should add a column to a table if the column doesn't
already exist. Using the macro condition, how can I test for whether a
specific column name in a specific table exists?

TIA for any help,
Steve Lord


Nov 13 '05 #2

P: n/a
Hmmm ... with the macro conditions could I test a function if I make a
function that does as you suggest down below? Any ideas what the syntax
of the macro condition should look like?

Thanks again!
Steve Jorgensen <no****@nospam.nospam> wrote in
news:t8********************************@4ax.com:
I'm pretty sure you can't do this without writing VBA code. In VBA
code, you can get a reference to the tabledef from the tabledefs
collection, then attempt to read the name property of the field object
from the fields collection. If that generates an error, the field
does not exist.

On Wed, 18 Aug 2004 10:58:26 GMT, steve lord <dr*********@hotmail.com>
wrote:
Greetings all,

I have a macro that should add a column to a table if the column
doesn't already exist. Using the macro condition, how can I test for
whether a specific column name in a specific table exists?

TIA for any help,
Steve Lord


Nov 13 '05 #3

P: n/a
steve lord <dr*********@hotmail.com> wrote in message news:<Xn**********************************@63.223. 5.254>...
Greetings all,

I have a macro that should add a column to a table if the column doesn't
already exist. Using the macro condition, how can I test for whether a
specific column name in a specific table exists?

TIA for any help,
Steve Lord


AFAIK, you can't do it without code. Ummm... why are you changing
your table design on the fly? That's usually a sign of denormalized
table structures.

If you really need to do this, you can find your table in the
tabledefs collection, and then loop through the fields collection of
the table object and see if your field is listed. If not, you'd add
the field. No way to do it without VB, though.
Nov 13 '05 #4

P: n/a
With just a macro, you can't, but this can:

Option Compare Database
Option Explicit

Public Function FieldExists(ByVal strTable As String, ByVal strField As String)
'--needs error trapping for:
' 1. Table doesn't exist
' I'm sure there's something else, but I don't know what yet...

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim intCounter As Integer

'--assume failure
FieldExists = False

Set db = CurrentDb
Set tdf = db.TableDefs(strTable)

For intCounter = 0 To tdf.Fields.Count - 1
If tdf.Fields(intCounter).Name = strField Then
FieldExists = True
Exit For
End If
Next intCounter

Set tdf = Nothing
Set db = Nothing
End Function
Nov 13 '05 #5

P: n/a
Thanks for your help ... your code in the next post should get me going.
As far as doing it on the fly, I construct tables for reporting purposes
and then export them in a specific text layout. The layout requires that I
delete some of the columns. Next time I clear and then append to the table
(there's some good reason why I don't do a make - but I don't remember
offhand), I need to make sure that my columns exist again. Therefore, I
need to conditionally alter the table - the macro barfs if the column's
already there - even if I turn the warnings off!

Thanks again for the help.

pi********@hotmail.com (Pieter Linden) wrote in
news:bf**************************@posting.google.c om:
steve lord <dr*********@hotmail.com> wrote in message
news:<Xn**********************************@63.223. 5.254>...
Greetings all,

I have a macro that should add a column to a table if the column
doesn't already exist. Using the macro condition, how can I test for
whether a specific column name in a specific table exists?

TIA for any help,
Steve Lord


AFAIK, you can't do it without code. Ummm... why are you changing
your table design on the fly? That's usually a sign of denormalized
table structures.

If you really need to do this, you can find your table in the
tabledefs collection, and then loop through the fields collection of
the table object and see if your field is listed. If not, you'd add
the field. No way to do it without VB, though.

Nov 13 '05 #6

P: n/a
steve lord <dr*********@hotmail.com> wrote in message news:<Xn**********************************@63.223. 5.254>...
Thanks for your help ... your code in the next post should get me going.
As far as doing it on the fly, I construct tables for reporting purposes
and then export them in a specific text layout. The layout requires that I
delete some of the columns. Next time I clear and then append to the table
(there's some good reason why I don't do a make - but I don't remember
offhand), I need to make sure that my columns exist again. Therefore, I
need to conditionally alter the table - the macro barfs if the column's
already there - even if I turn the warnings off!


Oh! Critical piece of information there. If you ALWAYS need that
same structure, just create a table with all the right fields and then
create import specifications to import to the "scratch table". Then
process the data in the table using update queries or whatever and
then export. Once you've successfully exported all the data to
whatever format you want, run a delete query against the table. Or,
you could read Tony Toew's site about using external DB's to hold
temporary data and go that route, but as you're not a codehead, that's
probably more than you'd like to get into. If you import/delete a
lot, then you should compact your DB on close.
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.