VBA for adding and renaming fields in tables already in use.

[Louverril]

I need to add some fields to existing data tables (and do one rename).

I believe you can do this using OpenDatabase() and CreateField() and one for
renaming? I have looked at the VBA help for these commands and can't figure
out how I could create a field and its attributes (field size etc.)

Can anyone point me in the right direction to use the above method or
suggest a better way.

I could do the changes manually but I know this will happen again with other
systems and I would like an automated solution. Also the risk of doing it on
site manually is too high.

Thanks
Lou

 

[Ken Sheridan]

I need to add some fields to existing data tables (and do one rename).

I believe you can do this using OpenDatabase() and CreateField() and one for
renaming? I have looked at the VBA help for these commands and can't figure
out how I could create a field and its attributes (field size etc.)

Can anyone point me in the right direction to use the above method or
suggest a better way.

I could do the changes manually but I know this will happen again with other
systems and I would like an automated solution. Also the risk of doing it on
site manually is too high.

Thanks
Lou

 

[Louverril]

Ken,

Thanks but the post was blank !

 

[Ken Sheridan]

Sorry about the empty reply!

The following example would create a text field MyNewColumn of 20 characters
size in the table MyTable in the current database:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("MyTable")
With tdf
Set fld = .CreateField("MyNewColumn", dbText)
fld.Size = 20
.Fields.Append fld
End With

Note that with number data types you do not set the Size property, unlike in
table design view. Instead use the relevant data type constant as the second
argument of the CreateField method, dbLong.dbInteger, dbDouble etc.

For an external database return a reference to it with the OpenDatabase
method instead of using CurrentDb.

To rename a column use a function like this:

Public Sub RenameColumn(strtable As String, strOldName As String, strNewName
As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strtable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

Ken Sheridan
Stafford, England

 

[Peter Hibbs]

Lou,

Assumimg your database is split into a front-end and back-end (if it
isn't, you need to do that first) then have look at the Back End
Update Utility which may be what you want :-

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=410

HTH

Peter Hibbs.

 

[Louverril]

Ken thanks very much - certainly gets me started.

Thanks.

 

[Louverril]

Thanks Peter - I will also have a look at this.

Lou,

Assumimg your database is split into a front-end and back-end (if it
isn't, you need to do that first) then have look at the Back End
Update Utility which may be what you want :-

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=410

HTH

Peter Hibbs.