Alter Table + Add Column w/ variable field name

[JSW55]

I would like to alter a table at run time. I had planned on getting
information from the user and then by using the Alter Table SQL statement to
add a column and use the input from the user to specify the field name.
However it does not appear as though you can use a variable for the field
name in the Alter Table statement. Does anyone know if there is a way and/or
a different method of adding a column to a table and to use a variable to
specify the field name? Thanks!

 

[strive4peace]

'~~~~~~~~~~~~~~~~
'example showing how to add certain fields to a particular table
'~~~~~~~~~~~~~~~~

Sub testaddFieldToTable()
AddFieldToTable "test", "AutoID", dbLong, , "*AN*"
AddFieldToTable "test", "SomeID", dbLong, , "*Null*"
AddFieldToTable "test", "ImportLog", dbText, 255
AddFieldToTable "test", "DateCreated", dbDate, , "*Now*"

End Sub

'~~~~~~~~~~~~~~~~
'example showing how to add the same fields
'to every user table in the database
'~~~~~~~~~~~~~~~~

Sub AddDateUserToTables()
Dim tdf As dao.TableDef, i As Integer
i = 1
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then

AddFieldToTable tdf.Name, _
"UserIDc", dbLong, , "*Null*"

AddFieldToTable tdf.Name, _
"UserIDm", dbLong, , "*Null*"

AddFieldToTable tdf.Name, _
"DateCreated", dbDate, , "*Now*"

AddFieldToTable tdf.Name, _
"DateModified", dbDate

i = i + 1
End If
Next tdf
DoEvents
Set tdf = Nothing
MsgBox "Added fields to " & i & " tables", , "Done"
End Sub

'~~~~~~~~~~~~~~~~

put the following function in a general module:
'~~~~~~~~~~~~~~~~

Function AddFieldToTable( _
pTablename As String, _
pFldname As String, _
pDataType As Integer, _
Optional pFieldSize As Integer, _
Optional pDefaultValue As String) _
As Boolean

'written by Crystal
'strive4peace2007 at yahoo.com

'PARAMETERS
'pTablename --> name of table to modify structure of
'pFldname --> name of field to create
'pDataType --> dbText, dbLong, dbDate, etc
'pFieldSize --> length for text fields
'pDefaultValue --> *AN* = autonumber
' --> *Null* --> DefaultValue = Null
' --> *Now* --> DefaultValue = Now()
' --> otherwise whatever is specified

'NEEDS Reference to
'a Microsoft DAO Library

On Error GoTo Proc_Err

Dim db As Database, Fld As Field

'you could make this a passed parameter
' and open another database

Set db = CurrentDb

With db.TableDefs(pTablename)

Select Case pDataType
Case dbText
'Text
Set Fld = .CreateField(pFldname, _
pDataType, pFieldSize)

Case Else
'Long Integer, Date, etc
Set Fld = .CreateField(pFldname, pDataType)

End Select

If Len(Nz(pDefaultValue, "")) > 0 Then
Select Case pDefaultValue
Case "*AN*"
'Autonumber
Fld.Attributes = dbAutoIncrField
Case "*Null*"
'Null for DefaultValue
Fld.DefaultValue = "Null"
Case "*Now*"
'Now for DefaultValue
Fld.DefaultValue = "=Now()"
Case Else
'Now for DefaultValue
Fld.DefaultValue = "=" & pDefaultValue
End Select
End If

If pDataType = dbText Then
Fld.AllowZeroLength = True
End If

.Fields.Append Fld
End With

db.TableDefs.Refresh
DoEvents

' MsgBox "Added --> " & pFldname _
& " to --> " & pTablename, , "Done"

AddFieldToTable_exit:
On Error Resume Next
Set Fld = Nothing
Set db = Nothing

Exit Function

Proc_Err:
'if the field is already there, ignore error
If Err = 3191 Then Resume Next

MsgBox Err.Description, , _
"ERROR " & Err.Number & " AddFieldToTable"

'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume

Resume AddFieldToTable_exit

End Function
'~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[jacksonmacd]

I would like to alter a table at run time. I had planned on getting
information from the user and then by using the Alter Table SQL statement to
add a column and use the input from the user to specify the field name.
However it does not appear as though you can use a variable for the field
name in the Alter Table statement. Does anyone know if there is a way and/or
a different method of adding a column to a table and to use a variable to
specify the field name? Thanks!

What are you trying to accomplish? Adding a field at runtime is quite
unusual, and if you describe your intent, somebody may be able to
suggest a better solution.

If you **really** want to alter the table, you could use DAO commands
that will allow you to use a variable fieldname.

 

[JSW55]

I have actually figured it out. It was a syntax problem. But to try and
explain ... First let me qualify myself and say that I consider myself pretty
much of a novice. I have learned a lot but have a lot to learn. At work other
users had an Access database that they used to 'scrape' data from the
mainframe. There were a couple of different forms and tables used to
accomplish this. Everytime there was a different field they wanted scraped
(i.e. current balance) they had to change the code. I suggested, and started
working on, using variables. The user is prompted at run time to provide the
fields they want scraped. Total number of fields and the coordinates (row,
column, length). In this manner you can use the same code everytime. No need
to change because it isn't hard coded. I figured out the problem I posted
yesterday. As I mentioned it was a syntax error in the Alter Table statement.
But now I have a new problem. The first thing I do is delete the old table
and then import an Excel spreadsheet with the accounts that they want to
scrape the mainframe for. The problem is that I have to figure out how to
specify the properites of the fields. For example I want to specify that a
text field is 6 characters ... not the default 255 characters. Or that
another field is Yes/No ... not a Text field. Does any of this make sense and
does anyone know how to modify the properties of a table in VBA? Thanks!

 

[strive4peace]

Hi Jackson,

look at my previous post to you on this thread...

you can only use this code to set up new fields, not modify them (some
of that can to be done with SQL to ALTER), but using the method I gave
you, field sizes can be specified when fields are created.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*