Code To Add and Name New Field To Table ?

[The Brown Growler]

Hi,

I would like to be able to run some code from within a macro that will add a new text field to a table and also give a name to the new text field. I do not wish the new field to be populated with data, just an empty field is OK.

Would anyone please be able to help me with this, ideally it would be run from a module so that I can use the macro command "OpenModule" to activate the code.

Alternatively, if there are any other solutions that will add and name the new field automatically to a table then I am happy to look at other options.


Thx & Rgds
Growlos

 

[boblarson]

More info if possible, please. Why are you going to be creating a new field in a table? Normally, one designs the database and then adds RECORDS and NOT fields (if properly designed).

 

[The Brown Growler]

More info if possible, please. Why are you going to be creating a new field in a table? Normally, one designs the database and then adds RECORDS and NOT fields (if properly designed).

Bob,

The table that I wish to add the new field to is automatically created from a large set of excel files via a macro "TransferSpreadsheet" command. The field names in the table are preset in the excel workbooks that come from data sources outside of my control.

Once all the excel workbooks have been imported to the table I wish to add a new named text field that is not populated with data. I will then use a macro "OpenQuery" command to populate the new field with data via an update query. Once this has been done, the table is then joined with other tables for queries etc and the new field is used in the joins.


Rgds
Growlos

 

[boblarson]

Something like this:

Public Function uAddColumn(strTableName As String, strColumnName As String, strColType As String, Optional strLength As String)
'---------------------------------------------------------------------------------------
' Procedure : uAddColumn
' DateTime  : November 28, 2007 12:53
' Author    : Bob Larson
'---------------------------------------------------------------------------------------
'
Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb
   
    If strLength = "" Then
        strSQL = "ALTER TABLE " & strTableName & " ADD COLUMN " & strColumnName & " " & strColType
    Else
        strSQL = "ALTER TABLE " & strTableName & " ADD COLUMN " & strColumnName & " " & strColType & "(" & strLength & ")"
    End If
    db.Execute strSQL, dbFailOnError
End Function

And you would call it like this:

uAddColumn "MyTableName", "MyColumnNameHere", "Text", "255")

That would add a column of text set to 255 characters.

 

[boblarson]

I took this, by the way, from my Backend Auto Updater code. The documentation on my Website has the other options you can use for the different datatypes.

 

[The Brown Growler]

Bob,

Many thx for the code.

I have a question due to my lack of knowledge in this area, if I wish to run the code/function using a row in a macro how would I get the "uAddFunction" to work, presumably, I have to add the table name and new field name to the function to get it to work ?

I can see how the function will do the job, its just that I am not clear how to automatically activate the function using a line in a macro ?

Thx & Rgds
Growlos

 

[boblarson]

You would use the action RunCode and you would supply the arguments (not change the function) in the arguments area of the macro line.

 

[The Brown Growler]

Bob,

Many thanks for the pointer re how to activate the function.

I have place the runcode command as a line in my macro and entered the arguments etc, however, when I come to run the macro it halts at that line with the message box:

"The expression you entered has a function name that microsoft office access can't find"

I have checked the name, ie, "uAddColumn" in the actual module that I placed the code in and also the module name and the macro and it is correct, ie, "uAddColumn"

I pasted the code into a module and called the module "uAddColumn" and it is present as a public function in the module collections. I then selected the "runcode" option for the macro line and then in the function box entered the name and arguments. Am I doing something wrong, ie, is my use of a module to store the code in wrong ?

I have attached an image of the expression builder with the arguments etc.


Rgds
Growlis

 

[boblarson]

Did you, when you pasted it into a module, make sure that the name of the module is not the same as the function?

 

[The Brown Growler]

Bob,

Oops, novice error, I actually made sure that the name of the module was the same as the name of the function. However, I have now renamed the module and kept the name of the function.

The macro line now activates the function, however, I then get a VBA compile error. I am sure that I am almost there, its just my lack of knowledge with modules/VBA/custom functions that is the hindrance.

I have attached an image of the VBA compile error in the hope that you can look at it if you get a moment.


Rgds
Growlos

 

[boblarson]

Okay, while in the VBA Window, go to TOOLS > REFERENCES and then scroll down until you find Microsoft DAO 3.x Library (where .x is either .51 or .6 and select the one you have - if you have both then .6 is good).

 

[The Brown Growler]

Bob,

Many thanks, it is all working perfectly now after selecting the DAO Library (3.6), your help is most appreciated.



Rgds
Growlos

 

[boblarson]

Glad to hear you got it working. Glad we could help.