Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Question Unanswered: Add field with ADO

    Can someone provide sample code to add a field to an existing Access 2000 database using ADO & VB6?

    Thanks in Advance

  2. #2
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    IF you mean add a field as adding a new record then do the following:
    Otherwise please rephrase your questiong

    2 ways

    First Way
    ======
    after you open a recordset
    recordset.open "Select * from TableYouWantToAddTo",connectionstring
    'you can change the * to column anmes if ti si les
    'then do recordset.add as follows
    recordset.add("FirstColumn") = firstcolumnValueYouWantToAdd
    recordset.add("SecondColumn") = SecondcolumnValueYouWantToAdd
    'after all the columns you add stated then do the next lines of code
    recordset.update
    recordset.close

    Second Way
    ========
    recordset.open "insert into TableYouWantToAddTo(FirstColumn,SecondCOlumn) values (' " & firstcolumnValueYouWantToAdd & " ',' " & SecondcolumnValueYouWantToAdd & " '),connectionstring
    Beyond Limitation

  3. #3
    Join Date
    Jul 2001
    Location
    NC
    Posts
    102
    There are some excellent ADO specific books available at computer bookstores that show how to add columns (fields) to tables using ADOX. Of course, you must have rights to modify table structure. The last time I did this I was using VB6, ADO 2.5 and Acccess '97 on NT machines. Remember to close the MDB before attempting to add data to the expanded table.

    You need to add ADO extensions for DDL and Security (ADOX) reference to your program. Check KB296173 (Create OLE Object field in Access) and KB252908 (Create table with primary key). The latter shows how to add fields once the tablespace is created. The new field will be appended AFTER the last column of the existing table - you can not position the new column within the table.

  4. #4
    Join Date
    Nov 2003
    Posts
    3

    Thanks for the replies

    Thanks to both for the replies.

    Actually, the second answer is the area I was asking about. I had already read the referenced KB articles, and the example for 'Creating a Table With A Primary Key Through ADOX' works, as long as I create a 'new' table. I can't seem to get it to work with an existing table.

    I assume that the line:

    Set objTable = New ADOX.Table

    needs to be changed to reference an existing table, but I can't seem to get it to work.

    Any suggestions would be greatly appreciated.

  5. #5
    Join Date
    Jul 2001
    Location
    NC
    Posts
    102
    It's quite simple. Again make sure you made references to ADO2.5 and ADOX2.7, at least. I understand ADO2.1 has problems.

    '-------------------------------------------------------
    'REFERENCES:
    'Microsoft ActiveX Data Objects 2.5 Library
    'Microsoft ADO Ext. 2.7 for DDL and Security
    '-------------------------------------------------------

    Public Xconx As ADODB.Connection
    Public Xcmd As ADODB.Command
    Public Xrs As ADODB.Recordset
    Public m_MDBdatabase As String
    Public m_MDBtable As String

    Option Explicit
    -----------------------------------------------------------------------------------
    Sub FORM_LOAD()
    'adds columns to existing MDB-database table
    Dim ADOXcat As ADOX.Catalog
    Dim MStbl As ADOX.Table
    Dim MScol As ADOX.Column

    'establish MDB database and tablename
    m_MDBdatabase = "c:\testDir\db_test.mdb"
    m_MDBtable = "table1"

    'make connection to MSAccess Database
    Set Xconx = New ADODB.Connection
    Set Xcmd = New ADODB.Command
    Set Xrs = New ADODB.Recordset
    Set Xconx = CreateObject("ADODB.Connection")
    Xconx.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Persist Security Info=False;" & _
    "Data Source=" & m_MDBdatabase
    Set Xrs = CreateObject("ADODB.Recordset")
    Xrs.CursorLocation = adUseServer

    'attach MDB and table to catalog
    Set ADOXcat = New ADOX.Catalog
    ADOXcat.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & m_MDBdatabase
    Set MStbl = ADOXcat.Tables(m_MDBtable)

    'add columns to existing table
    MStbl.Columns.Append "plan_id", adInteger
    MStbl.Columns.Append "misc_info", adVarWChar, 255
    MStbl.Columns.Append "rev_date", adDate

    'clean up
    ADOXcat.ActiveConnection.Close
    Set ADOXcat = Nothing
    Set MStbl = Nothing
    Set MScol = Nothing
    Set Xconx = Nothing
    Set Xcmd = Nothing
    Set Xrs = Nothing

    Me.Hide
    Unload Me
    End

    End Sub

  6. #6
    Join Date
    Feb 2004
    Posts
    1

    Question That help me with a problem... But now...??

    The code submitted worked perfectly for a problem that I was having... Now is there way to update the properties of a field?? I.e. "Required", "Allow Zero Length String", "Default Value"

  7. #7
    Join Date
    Mar 2004
    Posts
    1

    Thumbs up excel. code!

    work like a charm to me! Thanks a bunch

  8. #8
    Join Date
    Nov 2011
    Posts
    1

    add new field to an existing access table by vb6 code

    By Ado tool, I need to add a NEW FIELD to an existing access table via visual basic 6 code, (not a new record)

    kindly , try to help me
    Thank's for all
    Fkwinna
    Last edited by fkwinna; 11-17-11 at 14:59.

Posting Permissions

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