MS Access: adding column if not exist (or try/catch it)

Question

this is an easy question, but I can't find out the solution...

I have to check if a column exists and add it if the test is negative. How can I do with only sql code?

This is the syntax for SQL Server:

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[dbo].[Person]') 
         AND name = 'ColumnName'
)
BEGIN
    --STUFF HERE
END

and for MS Access, using only SQL code... what is the right syntax to do it?

UPDATE: also a way to do a try cath statement would be ok, I only need to not add anything if it doesn't exist... so, also a try catch is ok, I think. Also try catch is easily possible to use in sql server... and for access?

UPDATE 2: I have done this:

If Not Exists (Select Column_Name
           From INFORMATION_SCHEMA.COLUMNS
           Where Table_Name = 'TabTessereVeicoli'
           And Column_Name = 'TAGA')
begin

    ALTER TABLE TabTessereVeicoli ADD TAGA text(25) NULL;

end

but I get the error "SQL statement not valid. Expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE." Why? How can I do an alter table after an IF?

why the downote? who is this kind person? it is a question, only a question... — Piero Alberto, Apr 03 '15 at 08:42
I don't know, it's a plain question, though it wouldn't take that much to find out that the ALTER options of Access SQL are quite limited. The reason is, that in Access these operations are much easier caried out using VBA. — Gustav, Apr 03 '15 at 09:19

score 4 · Accepted Answer · answered Apr 03 '15 at 09:16

4

You can't do this in Access SQL. You can either run this:

ALTER TABLE TabTessereVeicoli ADD TAGA text(25) NULL;

and just ignore an error, or you can use VBA to open the TableDef and do your modifications.

answered Apr 03 '15 at 09:16

Gustav

53,498
7
29
55

1

You can do this with Access SQL in VBA. Something like this: `CurrentProject.Connection.Execute "ALTER TABLE TabTessereVeicoli ADD TAGA CHAR;` – Bobort Oct 23 '15 at 15:30

score 1 · Answer 2 · answered Feb 06 '18 at 09:13

Combined (and tested) VBA snippets from others, to check if a field exists in Microsoft Access, and to add a field to a table. (I figured I'd re-state them, partly for my own reference!)

Check if field exists in table:

Function DoesFieldExist(sTable As String, sField As String) As Boolean
    Err.Clear
    On Error GoTo setfalse:
    If (DCount(sField, sTable) = 0) And Err Then _
        DoesFieldExist = False Else DoesFieldExist = True
setfalse:
End Function

Add new field to table:

Sub AddFieldToTable(sTable As String, sField As String, sType As String)
    On Error GoTo sError
    CurrentProject.Connection.Execute "ALTER TABLE " & sTable & _
        " ADD " & sField & " " & sType & ";"
    Debug.Print "Added field [" & sField & "]"
    Exit Sub
sError:
    Debug.Print "Error adding field: " & Err, Err.Description
    Stop
End Sub

Of course, they could easily be combined into one function if necessary.

thank you for this, even though this isn't sql. exactly what i needed — lalachka, Feb 16 '21 at 01:47

MS Access: adding column if not exist (or try/catch it)

2 Answers2

Check if field exists in table:

Add new field to table: