A couple of ADO questions...

[elziko]

I have two questions on ADO. The first one is the most necessary as the second one is only for optimising working code.

1) Using ADO how do I create a new field in an Access table?

2) I got this code and reworked it to put all the field names in a table into a list box. How ever, to do this it cycles through EVERY field in EVERY table in the database (I think). Clearly, there must be a more efficient way although my code work fine.

Private Sub Tables_Click()
'ONCE A TABLE HAS BEEN SELECTED, A LIST OF FIELDS FOR THAT TABLES IS DISPLAYED
Fields.Clear 'clear list from previous display
Dim adoConnection As ADODB.Connection
Dim adoRsFields As ADODB.Recordset
Dim sConnection As String
Set adoConnection = New ADODB.Connection
sConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" + DatabaseDir.Path + "\" + DatabaseFile.Text 'connects to selected database
adoConnection.Open sConnection
Set adoRsFields = adoConnection.OpenSchema(adSchemaColumns)
sCurrentTable = ""
sNewTable = ""
Do Until adoRsFields.EOF 'run through every field in EVERY table (not good, should only select one table)
If adoRsFields!TABLE_NAME = Tables.List(Tables.ListIndex) Then 'wait until table matches the selected one
Fields.AddItem adoRsFields!COLUMN_NAME 'add the column name of that field (ie field name) to the list box
End If
adoRsFields.MoveNext 'onto next field
Loop
adoRsFields.Close
Set adoRsFields = Nothing
adoConnection.Close 'close connection
Set adoConnection = Nothing
End Sub

 

[oneshadow]

Hi elziko,
In your case, I thought it'll be easier using DAO. In my code below, I want to add new field 'Cust_Type' in Table3.
I'm using Access 97 and DAO 3.51


Dim Maindb As DAO.Database
Dim TblDeff As DAO.TableDef

Dim fld As DAO.Field

Dim bFieldIsExist As Boolean

Set Maindb = DBEngine.OpenDatabase("C:\My Temp\Dummy.mdb&quot

For Each TblDeff In Maindb.TableDefs

'looping for every table in database.
'just internal check, in case table doesn't exist

If Trim(UCase(TblDeff.Name)) = Trim(UCase("TABLE3&quot) Then

'check is field already exist?
For Each fld In TblDeff.Fields
If Trim(UCase(fld.Name)) = Trim(UCase("Cust_Type&quot) Then
bFieldIsExist = True
Exit For
End If
Next
If Not bFieldIsExist Then
'add new field
TblDeff.Fields.Append TblDeff.CreateField("Cust_Type", dbText, 30)
TblDeff.Fields("Cust_Type&quot.DefaultValue = Chr(34) & Chr(34)
TblDeff.Fields("Cust_Type&quot.AllowZeroLength = True
TblDeff.Fields("Cust_Type&quot.Required = False
End If

Exit For
End If
Next

Set Maindb = Nothing


If you do not want to check does table/field exist or not, you can skip the looping and compress the code like below :

With Maindb.TableDefs("TABLE3&quot
.Fields.Append .CreateField("Cust_Order", dbInteger)
.Fields("Cust_Order&quot.DefaultValue = chr(34) & chr(34)
.Fields("Cust_Type&quot.AllowZeroLength = True
.Fields("Cust_Type&quot.Required = False
End With


Me myself, I never succesfull changing structure of an Access table via ADO/ADOX. That's why I use DAO.

Hope this helps.

 

[chiph]

You can also do a "ALTER TABLE" command in SQL. But I don't know if MS-Access understands it or not (works in Oracle, SQLServer, DB2, MSDE, etc).

Chip H.