a couple simple questions from an access novice

  • Thread starter Thread starter dylan reid via AccessMonster.com
  • Start date Start date
D

dylan reid via AccessMonster.com

Hello and thank you for the help!

I have two questions about commmands:
1) I would like to execute a sql command that adds a column to a
particular table. Can anyone give me the syntax to do this?

2) How can I check if a column exists with a sql command?

Thank you in advance,
Dylan
 
dylan said:
Hello and thank you for the help!

I have two questions about commmands:
1) I would like to execute a sql command that adds a column to a
particular table. Can anyone give me the syntax to do this?

2) How can I check if a column exists with a sql command?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Add column to table (basic):

ALTER TABLE table_name ADD COLUMN column_name data_type

Substitute the correct data type for 'data_type.'

====

Check if column exists:

SELECT TOP 1 column_name FROM table_name

If an error occurs when you execute the above command then the column
doesn't exist. If no error occurs then the column exists.

If you're using an SQL-92 compliant DB you can use the
INFORMATION_SCHEMA (SQL Server, etc.):

SELECT 'Exists'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name'
AND COLUMN_NAME = 'column_name'

Substitute your column name for 'column_name' and your table name for
'table_name.'

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQioQOYechKqOuFEgEQIxCACfUgJ38WqYANT0v+ijZMZhyYBRXvYAoOt2
KHNDr0LmPk8LjqnvZ/ovEOST
=3Pp3
-----END PGP SIGNATURE-----
 
dylan reid via AccessMonster.com said:
Hello and thank you for the help!

I have two questions about commmands:
1) I would like to execute a sql command that adds a column to a
particular table. Can anyone give me the syntax to do this?
Click to expand...

dylan,

I use MS Access 2000 (so the below may be different for you if you use
a different version).

MS Access Menus:
Help >
Mircosoft Access Help >
Contents Tab:
Microsoft Jet SQL Reference >
Microsoft Jet SQL Reference >
Data Definition Language >
Alter Table Statement

The full syntax of ALTER TABLE is found here.

If you menus are different, search for JETSQL40.CHM on your C:
drive, and open it. In the index, type ALTER TABLE, and then press
Enter.
2) How can I check if a column exists with a sql command?
Click to expand...

I don't believe JET SQL has such a facility.

In VBA you can use:

Public Function fTableExists(TableName As String) As Boolean

Dim db As DAO.Database
Dim tbs As DAO.TableDefs
Dim TableDateCreated As String

On Error GoTo ErrorHandler:

Set db = CurrentDb()
Set tbs = CurrentDb.TableDefs

TableDateCreated = tbs.Item(TableName).DateCreated

fTableExists = True

Exit_fTableExists:

db.Close
Set db = Nothing

Exit Function

ErrorHandler:

fTableExists = False

GoTo Exit_fTableExists:

End Function

That function returns True if the Table is in the TableDefs
collection, and False if the Table is not in the collection.

Sincerely,

Chris O.
 
Back
Top