Results 1 to 15 of 15
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Alter table - appending a field(add column)

    following code to add a new column to a sql table (sql is msde runtime engine)
    No errors

    refresh the links and no field added

    Dim db As Database
    Dim t As TableDef
    Dim f As Field

    Set db = CurrentDb()

    Set t = db.CreateTableDef("sheetinfotable")

    Set f = t.CreateField("Sequence")
    f.Type = dbText
    f.size = 50
    t.Fields.Append f
    Application.RefreshDatabaseWindow

    No errors - No new field
    Tried the alter table and got the cannot excute using ODBC link message.

    Any Ideas guys?
    I want to add the column "sequence" to the table "sheetinfotable" text 10 or 20 or 30 - whatever works at this point - naming my first born child after the person with the first right answer
    Dale Houston, TX

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DoCmd.RunSQL "ALTER TABLE sheetinfotable ADD sequence varchar(20) NULL"
    Oh, and I've changed my name to "Cozmo" just for you
    George
    Home | Blog

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Dear Cozmo - I will test that in a few minutes - If all goes well I will send copy of birth certificate for Cozmo Jr.
    Dale Houston, TX

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    error 3611 Geo

    Same error as I got before using db.execute

    Cannot blah blah blah with odbc connection error
    Dale Houston, TX

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I was assuming the table was part of the current database (so within access)... But I'm guessing not? Linked table?
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by axsprog
    Cannot blah blah blah with odbc connection error
    True.

    Some quick googling shows that Access can't add columns to a linked table.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    CreateTableDef doesn't appear to be what you want; have you looked at the .Append method yet?
    George
    Home | Blog

  8. #8
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    .append method is what I was using with in fact no erros - just no column added

    I created stored procedures on the sql server and it worked.

    IVON - thank you for the google tip but I trie this using a dsn-less connection as well and it failed - best way was calling the stored provedures.
    Dale Houston, TX

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I meant .Append without the .CreateTableDef - sorry for the confusion.
    George
    Home | Blog

  10. #10
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    did not try that - will try later as I am at another location now. But that is a good idea
    Dale Houston, TX

  11. #11
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    anybody know why I would not see the field in the sql server after query analyzer says it completed successfully

    I used DAO as well from the frontend and there were no errors yet I cannot see the column
    Dale Houston, TX

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What code did you run through QA?
    George
    Home | Blog

  13. #13
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Well I went into enterprise manager - table design and the fields were there!

    So I created a new dsn and the fields were visible.

    go figure.
    Dale Houston, TX

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So in effect you had to refresh your cached view of the tables?

    There's a few good things to read here. Perhaps you should look into automatically re-linking your tables on application load?

    ODBC is an area I'm a bit hazy on - but I'm sure when the likes of PK, Izy and co arrive they'll tell you what's what!
    George
    Home | Blog

  15. #15
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    No - the refresh (removing the links and relinking using the same dsn connection) did not allow me to see the new fileds - I had to create a new dsn to see the changes.

    this was both true for the msde and the true sql server data
    Dale Houston, TX

Posting Permissions

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