Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Alter Table (XP)

    Trying to change field names in an access db. However when I use:

    "ALTER TABLE <tablename> RENAME COLUMN [col1] TO [col2]"

    I get a syntax error. Any ideas would be great.
    Thanks

    Kim

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Alter Table (XP)

    Jet SQL doesn't support renaming columns. You can use DAO or ADO, for example:

    CurrentDb.TableDefs("tblSomething").Fields("OldNam e").Name = "NewName"

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Alter Table (XP)

    Thanks for the response. Actually I am working in VB6 using an Access database. How does that change the equation?
    Thanks

    Kim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Alter Table (XP)

    How are you connecting to the Access database? Are you using ADO or DAO?

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Alter Table (XP)

    ADO
    Thanks

    Kim

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Alter Table (XP)

    Set a reference to the Microsoft ADO Ext. 2.7 for DDL and Security library in Project | References... You can then use code like this:

    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    cnn.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4. 0;User ID=Admin;Data Source=CatabasesTest.mdb;"
    Set cat.ActiveConnection = cnn
    Set tbl = cat.Tables("tblSomething")
    Set col = tbl.Columns("OldFieldName")
    col.Name = "NewFieldName"
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
    cnn.Close
    Set cnn = Nothing

    Substitute the correct names. If you already have a global Connection object you can use that instead of cnn.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Alter Table (XP)

    Perfect, Hans.
    Thanks

    Kim

Posting Permissions

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