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

Thanks in Advance

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

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.

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.

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

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"

work like a charm to me! Thanks a bunch

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