Results 1 to 4 of 4

Thread: Alter Table

  1. #1
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Unanswered: Alter Table

    Hello there...

    Ist possible under ACCE$$ to alter many columns in one sentence.

    currentDB.Execute "ALTER TABLE Pers ALTER COLUMN pers_titel Text(4) "
    ist functions here good, but I'd like to alter more columns at the same time
    how could that be achived?

    Tahnks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Interesting question. The answer is no I believe - ANSI SQL or JET SQL or any other flavour though I could be wrong.

    The closest you could get to cheating this is to create a function that accepts an array of column definitions and loop through this, executing the SQL at each pass. Unlikely to be worth it unless you plan to use DDL like this really heavily.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Thank you for your answer, I have already done your suggestion, I though there were an Alter for many fields in one statement

    PHP Code:
    Dim sAlter(8) as String
        sAlter
    (0) ="ALTER TABLE " TempImportData " ALTER COLUMN Field1 Text(4)"
        
    sAlter(1) ="ALTER TABLE " TempImportData " ALTER COLUMN Field2 Text(28)"
        
    sAlter(2) ="ALTER TABLE " TempImportData " ALTER COLUMN Field3 Text(28)"
        
    sAlter(3) ="ALTER TABLE " TempImportData " ALTER COLUMN Field4 Text(1)"
        
    sAlter(4) ="ALTER TABLE " TempImportData " ALTER COLUMN Field5 Text(4)"
        
    sAlter(5) ="ALTER TABLE " TempImportData " ALTER COLUMN Field6 Text(1)"
        
    sAlter(6) ="ALTER TABLE " TempImportData " ALTER COLUMN Field7 Text(25)"
        
    sAlter(7) ="ALTER TABLE " TempImportData " ALTER COLUMN Field8 Text(25)"



                
    For 0 To UBound(sAlter) - 1
                  currentDB
    .Execute sAlter(i)
                
    Next 
    By the way, do you know why ist it not possible to use DoCmd.RunSQL instead of CurrentDB.Execute

    Thanks again

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by hammbakka
    By the way, do you know why ist it not possible to use DoCmd.RunSQL instead of CurrentDB.Execute
    News to me I'm afraid. I always use the execute method.

    I think it is news to Bill too - from A Help

    The RunSQL method carries out the RunSQL action in Visual Basic.
    expression.RunSQL(SQLStatement, UseTransaction)</B>


    expression Required. An expression that returns one of the objects in the Applies To list.

    SQLStatement Required Variant. A string expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement. Include an IN clause if you want to access another database.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •