D Douglas Voltin Nov 29, 2005 #1 Can anyone tell me what the Access VBA code is for deleting, inserting, and rename column heading in a table?
Can anyone tell me what the Access VBA code is for deleting, inserting, and rename column heading in a table?
B Brendan Reynolds Nov 29, 2005 #2 You can execute DDL queries via ADO or DAO, or you can use the DAO or ADOX object models ... Public Sub AddRemoveCols() 'execute DDL query via ADO CurrentProject.Connection.Execute _ "ALTER TABLE tblTest DROP Column TestText", , adCmdText 'execute DDL query via DAO CurrentDb.Execute _ "ALTER TABLE tblTest ADD Column TestText TEXT (25)", dbFailOnError 'use DAO object model CurrentDb.TableDefs("tblTest").Fields.Delete ("TestText") 'use ADOX object model Dim cat As ADOX.Catalog Set cat = New ADOX.Catalog Set cat.ActiveConnection = CurrentProject.Connection cat.Tables("tblTest").Columns.Append "TestText", adVarWChar, 30 End Sub
You can execute DDL queries via ADO or DAO, or you can use the DAO or ADOX object models ... Public Sub AddRemoveCols() 'execute DDL query via ADO CurrentProject.Connection.Execute _ "ALTER TABLE tblTest DROP Column TestText", , adCmdText 'execute DDL query via DAO CurrentDb.Execute _ "ALTER TABLE tblTest ADD Column TestText TEXT (25)", dbFailOnError 'use DAO object model CurrentDb.TableDefs("tblTest").Fields.Delete ("TestText") 'use ADOX object model Dim cat As ADOX.Catalog Set cat = New ADOX.Catalog Set cat.ActiveConnection = CurrentProject.Connection cat.Tables("tblTest").Columns.Append "TestText", adVarWChar, 30 End Sub
