Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2002
    Posts
    4

    Unanswered: rename a *.mdb table column name via VbScript?

    Hi,

    I am hoping somebody will be able to tell me how to dynamically rename a *.mdb table column name via
    VbScript? I have searched high and low for online help but have found next to nothing.

    Thanks for your help!

    Sean

  2. #2
    Join Date
    Feb 2002
    Posts
    29
    Make sure you have a reference set for a Microsoft DAO Object Library, then this code should do it:


    Dim dbs As Database
    Dim tbl As TableDef
    Set dbs = CurrentDb
    Set tbl = dbs.TableDefs("YourTableName")
    tbl("OldFieldName").NAME = "NewFieldName"
    Set tbl = Nothing
    Set dbs = Nothing

  3. #3
    Join Date
    Feb 2002
    Posts
    29
    Sorry, I just read you question again - vb*Script*. No, as far as I know you can't alter table structure with vbScript. If you find out how, let me know.

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    try

    dim oAccess

    set oAccess = createobject("Access.Application")
    oAccess.opencurrentdatabase("c:\projects\accutane\ access\db3.mdb")
    oAccess.currentdb.tabledefs("Table1").Fields("Name ").name = "NewFieldName"

    oAccess.quit
    set oAccess = nothing

    HTH,

  5. #5
    Join Date
    Feb 2002
    Posts
    4
    HI,

    I have determined that I don't need to rename the field but rather start with an empty table (other than primary key) and dynamically add columns to the table and set their data type. If you have any info with regards to this I would really appreciate it.

    Thank you!

  6. #6
    Join Date
    Nov 2001
    Posts
    336
    May you clarify the problem?
    As I understand, what you want is just to create new table in existing MDB file.

  7. #7
    Join Date
    Feb 2002
    Posts
    4
    This is a simple example of what I have done. It's working so I am happy. Thanks for your input. I do have one question. In addition to setting the char-length "CHAR(25)" how can I set the default value at the same time? Ex: create then input value.

    Cheers.


    MyStringSet = "ALTER TABLE tblServices ADD " & NewColumnSet & " CHAR(25)"

    strSQLSet = MyStringSet

    Set MyConn=Server.CreateObject("ADODB.Connection")

    MyConn.open "PROVIDER=MSDASQL; DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("MyDatase.mdb")

    MyConn.Execute (strSQLSet)

    MyConn.Close

    Set MyConn = Nothing

  8. #8
    Join Date
    Oct 2002
    Location
    Arizona
    Posts
    1
    I researched for this code approx. 8 hours. Thanks for taking the time to pass on knowledge. I expanded a little to allow dynamic iteration through all tables that have the same prefix and require a different one. I had around 120 tables in an Access 97 database linked to Oracle database for ERP reporting. The schema name changed which left all queries and reports non-functional...

    '********************************************
    'Rename GLOVIA_DEMO61_ table name to GL_PROD_
    '*****************************************
    Private Sub Command2_Click()
    Dim dbs As Database
    Dim tbl As TableDef
    Dim strNew As String
    Dim strOld As String
    Dim I As Integer
    Dim System_Prefix, Hidden_Prefix, Current_TableName
    Dim Glovia_Demo61
    Dim Gl_Prod
    Dim charQty As Integer
    Dim charKeep As Integer

    Set dbs = CurrentDb
    strNew = "GL_PROD_"
    strOld = "GLOVIA_DEMO61_"

    For I = 0 To dbs.TableDefs.Count - 1
    Set tbl = dbs.TableDefs(I)
    Current_TableName = dbs.TableDefs(I).Name
    System_Prefix = Left(Current_TableName, 4)
    Hidden_Prefix = Left(Current_TableName, 1)
    Glovia_Demo61 = Left(Current_TableName, 14)
    Gl_Prod = Left(Current_TableName, 8)
    If System_Prefix <> "MSys" And System_Prefix <> "USys" And Hidden_Prefix <> "~" And Gl_Prod <> strNew And Glovia_Demo61 = strOld Then
    charQty = Len(tbl.Name)
    charKeep = charQty - 14
    tbl.Name = Trim(Right(tbl.Name, charKeep))
    tbl.Name = strNew & tbl.Name
    End If
    Next I
    Set tbl = Nothing
    Set dbs = Nothing
    End Sub
    '*****************************************



    Originally posted by cpod
    Make sure you have a reference set for a Microsoft DAO Object Library, then this code should do it:


    Dim dbs As Database
    Dim tbl As TableDef
    Set dbs = CurrentDb
    Set tbl = dbs.TableDefs("YourTableName")
    tbl("OldFieldName").NAME = "NewFieldName"
    Set tbl = Nothing
    Set dbs = Nothing

Posting Permissions

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