Adding columns via macros

[R34LiSM]

Does anyone know how I can setup some sort of macro that will allow me to autimatically add a series of extra named columns to an existing table?

At the moment, I'm importing the data and then adding all these columns manually - surely there's a more efficient way of doing this?

I've had a rummage through the macro functions, but nothing jumped out at me. Any pointers would be very much appreciated!

- R34LiSM

 

[WildHare]

You can rummage all you want, you'll not find a MACRO command to alter the structure of a table by adding columns to it. You'll need to use VBA.

Check out the RECORDSET collections and check in some of the more advanced Access developer books for ideas - I'm sure that someone somewhere has written a routine to programmatically ADD fields to an existing table. I don't have one handy or I'd post it myself....

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[wemeier]

At what point do you know that you have extra columns? Before you do the import or do you want to detect extra columns as you are importing?

I agree with WildHare that there is GENERALLY no way to alter a table structure using a macro, however, you could create a RunSQL entry in a macro if the field names were static.

I'll give you a quick example of what can be done using VBA coding:

Say you have a comma-delimited text file (test.txt) whose first row contains field names. You could open the file, read the first record and check each field name to see if it is already on the table. If it is not, you could execute a SQL query to update the table definition and add the field to the table. Here's the coding:

Public Sub AddTableEntries()

Dim dbs As Database ’ current database
Dim tdf As TableDef ’ tabledef in database
Dim fld As Field ’ field in tabledef
Dim fFound As Boolean ’ used to identify if field was found
Dim intFile As Integer ’ file number (for sequential file read)
Dim intStart As Integer ’ start of field name from ascii file
Dim intEnd As Integer ’ end of field name from ascii file
Dim strFieldName As String ’ name of a field extracted from ascii file
Dim strRecord As String ’ first record of ascii file

intFile = FreeFile()
Open "d:\test.txt" For Input As #intFile ’ open the ascii file

Line Input #1, strRecord ’ read the first record
If Not EOF(intFile) Then ’ if a record exists...
Set dbs = CurrentDb ’ Return reference to current database.
Set tdf = dbs.TableDefs("tblTest&quot ’ Return reference to your table.

intStart = 1
intEnd = InStr(intStart, strRecord & ",", ",&quot ’ find the first comma delimiter (if it exists)
While intEnd > 0 ’ if one is found...
strFieldName = Mid(strRecord, intStart, intEnd - intStart) ’ extract the field name
fFound = False
For Each fld In tdf.Fields ’ walk the fields already on the table
If fld.Name = strFieldName Then ’ if you find the field name...
fFound = True ’ ... set found to true
Exit For ’ ... and stop
End If
Next fld
If Not fFound Then ’ If the field name was not found...
Set fld = tdf.CreateField(strFieldName) ’ ...Create new Field object.
fld.Type = dbText ’ ...Set Type property of Field object to be text
fld.Size = 11 ’ ...Set Size property of Field object to be 11
tdf.Fields.Append fld ’ ...Append the Field to the table definition
End If
intStart = intEnd + 1 ’ start looking for next field in ascii file
intEnd = InStr(intStart, strRecord & ",", ",&quot ’ Find the next comma delimiter (if one exists)
Wend
End If

Set dbs = Nothing ’ remove database reference
Close #intFile ’ close the ascii file

End Sub

Now, of course, this is simplistic in that it assumes each field is Text. You would have to add more logic if you need to autodetect what type of data is in each new field.