Post reply

Alter multiple Access tables through SQL

  • January 13, 2008 at 9:35 pm

    #178104

    This is what I wish to do:

    Alter 3 Access tables, by adding a column ExportToSQLDate to each of the three tables.

    I need to know how I can do this through SQL.

    This is what I tried doing in Access but it doesn't let me execute all three together, saying 'Syntax Error in ALTER TABLE statement'. When I execute them individually, it works fine.

    ALTER TABLE Locations ADD COLUMN ExportToSQLDate DATETIME;

    ALTER TABLE Campaigns ADD COLUMN ExportToSQLDate DATETIME;

    ALTER TABLE Survey ADD COLUMN ExportToSQLDate DATETIME;

    So.. I'd like to do it through SQL instead.. I have a Linked Server created too.. so is there any way of altering the external datasource table through an SQL query?

  • January 14, 2008 at 5:10 am

    #767627

    I guess I don't understand... why does the addition of columns to tables in ACCESS have to be automated to begin with?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 14, 2008 at 5:12 am

    #767629

    One might also try posting the original question on an Access forum... I do very little in Access and don't know the answer other than running the script you originally posted. You would probably have better luck if you posted the exact wording of the error you're getting and, maybe, the table schema...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 14, 2008 at 5:16 am

    #767632

    Ok,

    ONE:

    The script to add columns to Access has to be applied on several servers across the organization as we maintain individual servers for the developers, testers, and so on. Therefore, I'd like to send them all a script or query to apply.

    TWO:

    I did post this on the Access forum initially, but got no response.

    THREE:

    I was wondering if I could perform an Alter table operation on an external data source through an SQL query, which is why I thought I'd put it up here.

    FOUR:

    Thanks for replying 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply