Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: Appending Fields in VBA with ADO

    I'm trying to add two new fields to an existing table using VBA with the ADO packages. I seem to be able to append the fields when I run my code, but when I check my table they haven't been added. Can someone give me an idea on where I'm going wrong? Here is what I've got coded so far.

    Dim db As Connection
    Dim tbl As New ADODB.Recordset
    Dim fld As ADODB.Fields
    Dim i, j, k, h As Integer

    'Opens the Recharge Table from the current database
    Set db = CurrentProject.Connection
    tbl.Open "Table1", db, adOpenForwardOnly, adLockBatchOptimistic
    Set fld = tbl.Fields
    tbl.Close

    k = 0
    h = 0
    i = 0
    j = fld.Count

    'Checks the recordset to make sure the fields don't already exist
    'If the fields don't exist, they are appended to the recordset
    Do While i < j
    If fld(i).Name = "NewField1" Then
    h = 1
    Exit Do
    End If
    i = i + 1
    Loop

    If h = 0 Then
    fld.Append "NewField1", adDBDate, 10, adFldUpdatable
    MsgBox ("NewField1 added")
    End If

    i = 0

    Do While i < j
    If fld(i).Name = "NewField2" Then
    k = 1
    Exit Do
    End If
    i = i + 1
    Loop

    If k = 0 Then
    fld.Append "NewField2", adDBDate, 10, adFldUpdatable
    MsgBox ("NewField2 added")
    End If

    i = 0

    'Tests to make sure the fields have been appended to the recordset
    Debug.Print tbl.Source

    Do While i < fld.Count
    Debug.Print fld(i).Name
    i = i + 1
    Loop

    I've tried things like tbl.Save and tbl.Update but I still haven't gotten the results that I'm looking for. Thanks in advance!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Uh... Sorry. You have to use SQL to do that using the ALTER TABLE command or go into the table's Design View and alter that way ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would prefer SQL everyday too but if you want to use ADO and objects then you need to check out ADOX. This is the kind of "DDL" library for ADO.

    Good access SQL ref:
    http://msdn.microsoft.com/library/de...l/acintsql.asp
    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
  •