CODEWITHSUNDEEP
ms-access
canyon289
canyon289

Reputation: 3495

ALTER TABLE Syntax Error

I'm having trouble with an update query in Access.

I'm trying to do two things, add a field to a table, and change all values in another field to the same value. 

ALTER TABLE 103 ADD COLUMN test TEXT;
UPDATE 103 SET [103].Workcenter = "103";

When I run these two lines independently, they work fine, but when I put them in the same query I get "Syntax Error in ALTER TABLE" statement. Does anyone know why I can't do this?

It would also be great if I could add a column and update all values in that field to a default value. I've tried DEFAULT in the ALTER TABLE command but it's not working either.

Thanks in advance for suggestions!

Upvotes: 2

Views: 5398

Answers (2)

HansUp
HansUp

Reputation: 97101

Some table design features are only available when using the DAO object model to modify the TableDef. Others are only available when executing a DDL statement from an ADO connection.

Your table design change involves features which are available with either method. Use whichever you wish, but I would personally choose this way:

Dim strDdl As String
strDdl = "ALTER TABLE 103 ADD COLUMN test TEXT(128) DEFAULT ""000"";"
CurrentProject.Connection.Execute strDdl

Upvotes: 1

Renaud Bompuis
Renaud Bompuis

Reputation: 16776

AS ron tornambe said, you can't have more than a single command in an Access Query. They do not support batching.

VBA code is your friend when doing alterations on tables: The Data Definition Language used in Access is more limited than what is available from VBA when directly manipulating the database objects.

For instance, to do exactly what you seek:

Public Sub AddFieldAndUpdate()
    ' Initialise '
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Dim fd As DAO.Field
    Set db = CurrentDb()

    ' Get the 103 table '
    Set tb = db.TableDefs("103")
    ' Create a new 'test' field, 128 char long '
    Set fd = tb.CreateField("test", dbText, 128)
    ' Set the Default value for the new field '
    fd.DefaultValue = "000"
    ' Add the new field to the 103 table
    tb.Fields.Append fd

    ' Now do the update
    db.Execute "UPDATE 103 SET [103].Workcenter = '103';", dbFailOnError
    Debug.Print "Number of Updated records: " & db.RecordsAffected

    ' Cleanup
    Set fd = Nothing
    Set tb = Nothing
    Set db = Nothing
End Sub

This is the jest of it, although you probably want to do more than that, for instance, set indexes, default formatting, etc as required.

Upvotes: 3

Related Questions