By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,525 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

SQL: Add a column in a table AFTER a certain column

P: 16
Hi all,

I am using Access 2003.

I am trying to insert a column "test" in "data table" right after the column "category".

My code adds the column "test", but if I add the AFTER statement it tells me:
"syntax error in ALTER TABLE statement"

DoCmd.RunSQL "ALTER TABLE [Data table] ADD test text AFTER category;"

Any ideas?

Thanks
Dec 20 '06 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I'm afraid you can't do it that way it's a bit more complicated. Have a look at the following.

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database 
  2. Dim tbl As TableDef 
  3. Dim fld As Field 
  4.  
  5.     Set db = CurrentDb() 
  6.     Set tbl = db.TableDef("Data Table") 
  7.  
  8.     Set fld = tbl.CreateField("test", dbText) 
  9.    ' you will have to figure out the position of category and make this the next position
  10.     fld.OrdinalPosition = 2 
  11.     tbl.Fields.Append fld 
  12.  
  13. Set fld = Nothing
  14. Set tbl = Nothing
  15. Set db = Nothing
  16.  
  17.  
Mary
Dec 21 '06 #2

P: 16
Great! This works like a charm.

I editted it some what so that it uses the fieldname and fieldtype selected by the user in a form, to create the fields.

Btw: I also found a way to set "allow zero length" to yes.

A nice to have would be to be able to create a popup with the text "Fieldname already exists" when a user adds a field that already exists.
When that happens Access gives the error message =

Error message '3191'
cannot define field more than once

Thanks!!!!!


Private Sub Add_Click()
Dim db As Database
Dim tbl As TableDef
Dim fld As field
Dim fieldname As String
Dim Fieldtype As String
DoCmd.SetWarnings True
Set db = CurrentDb()
Set tbl = db.TableDefs("[Data Table]")
Fieldname = Forms![Add field]![fieldname]
Fieldtype = Forms![Add field]![fieldtype]

If (Fieldtype = Forms![Add field]![veldtype]) = "dbmemo" Then
Set fld = tbl.CreateField(fieldname, dbMemo)
Else
Set fld = tbl.CreateField(fieldname, dbText)
End If
fld.OrdinalPosition = 3
With fld
If .type = dbText Then
If Not .AllowZeroLength Then
.AllowZeroLength = True
End If
End If
End With
tbl.Fields.Append fld
Me.Refresh
MsgBox "Field has been successfully added to all tables", vbInformation, "Added"
DoCmd.GoToRecord , , acNewRec
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing

End Sub



I'm afraid you can't do it that way it's a bit more complicated. Have a look at the following.

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database 
  2. Dim tbl As TableDef 
  3. Dim fld As Field 
  4.  
  5.     Set db = CurrentDb() 
  6.     Set tbl = db.TableDef("Data Table") 
  7.  
  8.     Set fld = tbl.CreateField("test", dbText) 
  9.    ' you will have to figure out the position of category and make this the next position
  10.     fld.OrdinalPosition = 2 
  11.     tbl.Fields.Append fld 
  12.  
  13. Set fld = Nothing
  14. Set tbl = Nothing
  15. Set db = Nothing
  16.  
  17.  
Mary
Dec 21 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Add_Click()
  2. Dim db As Database
  3. Dim tbl As TableDef
  4. Dim fld As field
  5. Dim rs As Recordset
  6. Dim fieldname As String
  7. Dim Fieldtype As String
  8.  
  9.    DoCmd.SetWarnings True
  10.    Set db = CurrentDb()
  11.    Set tbl = db.TableDefs("[Data Table]")
  12.  
  13.    Fieldname = Forms![Add field]![fieldname]
  14.    Fieldtype = Forms![Add field]![fieldtype]
  15.  
  16.    Set rs = db.OpenRecordset("[Data Table]"]
  17.  
  18.    For Each fld In rs.Fields 
  19.       If Fieldname = fld.Name Then
  20.          Msgbox "This field name already exists in the table"
  21.          GoTo Exit_Sub
  22.       End If
  23.    Next 
  24.  
  25.    If (Fieldtype = Forms![Add field]![veldtype]) = "dbmemo" Then
  26.       Set fld = tbl.CreateField(fieldname, dbMemo)
  27.    Else
  28.       Set fld = tbl.CreateField(fieldname, dbText)
  29.    End If
  30.    fld.OrdinalPosition = 3
  31.  
  32.    With fld
  33.    If .type = dbText Then
  34.       If Not .AllowZeroLength Then
  35.          .AllowZeroLength = True
  36.       End If
  37.    End If
  38.    End With
  39.  
  40.    tbl.Fields.Append fld
  41.    Me.Refresh
  42.    MsgBox "Field has been successfully added to all tables", vbInformation, "Added"
  43.    DoCmd.GoToRecord , , acNewRec
  44.  
  45. Exit_Sub:
  46.    rs.Close
  47.    Set rs = Nothing
  48.    Set fld = Nothing
  49.    Set tbl = Nothing
  50.    Set db = Nothing
  51.  
  52. End Sub
  53.  
  54.  
Merry Christmas

Mary
Dec 21 '06 #4

P: 16
Thanks Mary,

But when I now create a new field it tells me that the "Data Table" is in use. After a debug it highlights "tbl.Fields.Append fld"

Could this be because the Data table is "set" twice, once as tbl and once and rs?

Set tbl = db.TableDefs("[Data Table]")

fieldname = Forms![Add field]![kolomnaam]
Fieldtype = Forms![Add field]![veldtype]

Set rs = db.OpenRecordset("[Data table]")



BTW: the form is set to "Data Entry: yes" (if that matters at all)

Thanks




Try this ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Add_Click()
  2. Dim db As Database
  3. Dim tbl As TableDef
  4. Dim fld As field
  5. Dim rs As Recordset
  6. Dim fieldname As String
  7. Dim Fieldtype As String
  8.  
  9.    DoCmd.SetWarnings True
  10.    Set db = CurrentDb()
  11.    Set tbl = db.TableDefs("[Data Table]")
  12.  
  13.    Fieldname = Forms![Add field]![fieldname]
  14.    Fieldtype = Forms![Add field]![fieldtype]
  15.  
  16.    Set rs = db.OpenRecordset("[Data Table]"]
  17.  
  18.    For Each fld In rs.Fields 
  19.       If Fieldname = fld.Name Then
  20.          Msgbox "This field name already exists in the table"
  21.          GoTo Exit_Sub
  22.       End If
  23.    Next 
  24.  
  25.    If (Fieldtype = Forms![Add field]![veldtype]) = "dbmemo" Then
  26.       Set fld = tbl.CreateField(fieldname, dbMemo)
  27.    Else
  28.       Set fld = tbl.CreateField(fieldname, dbText)
  29.    End If
  30.    fld.OrdinalPosition = 3
  31.  
  32.    With fld
  33.    If .type = dbText Then
  34.       If Not .AllowZeroLength Then
  35.          .AllowZeroLength = True
  36.       End If
  37.    End If
  38.    End With
  39.  
  40.    tbl.Fields.Append fld
  41.    Me.Refresh
  42.    MsgBox "Field has been successfully added to all tables", vbInformation, "Added"
  43.    DoCmd.GoToRecord , , acNewRec
  44.  
  45. Exit_Sub:
  46.    rs.Close
  47.    Set rs = Nothing
  48.    Set fld = Nothing
  49.    Set tbl = Nothing
  50.    Set db = Nothing
  51.  
  52. End Sub
  53.  
  54.  
Merry Christmas

Mary
Dec 22 '06 #5

P: 16
Hi Mary,

After some reshuffeling and editting I have arrived at the code below that checks for the fieldname in the datatable first before adding it.

Thanks a lot for all the help and a Merry Christmas to you too!

Expand|Select|Wrap|Line Numbers
  1. Private Sub Add_Click()
  2. Dim db As DAO.Database
  3. Dim tbl As TableDef
  4. Dim fld As field
  5. Dim rs As DAO.Recordset
  6. Dim fieldname As String
  7. Dim Fieldtype As String
  8.  
  9.    DoCmd.SetWarnings True
  10.     If Not IsNull(Forms![Add field]![kolomnaam]) Then
  11.     fieldname = Forms![Add field]![kolomnaam]
  12.     Fieldtype = Forms![Add field]![veldtype]
  13.     Else
  14.      MsgBox "No fieldname was provided. Please provide a valid fieldname and try again.", vbInformation, "No fieldname found"
  15.       Exit Sub
  16.     End If
  17.  
  18.     Set db = CurrentDb()
  19.  
  20.     Set rs = db.OpenRecordset("Select * from [Data table]")
  21.  
  22.    For Each fld In rs.Fields
  23.       If fieldname = fld.Name Then
  24.          MsgBox "This fieldname already exists in the table", vbCritical, "Fieldname Exists"
  25.          Exit Sub
  26.       End If
  27.     Next
  28.    rs.Close
  29.  
  30.    Set tbl = db.TableDefs("[Data Table]")
  31.    If (Forms![Add field]![veldtype]) = "dbMemo" Then
  32.       Set fld = tbl.CreateField(fieldname, dbMemo)
  33.    Else
  34.       Set fld = tbl.CreateField(fieldname, dbText)
  35.    End If
  36.    fld.OrdinalPosition = 3
  37.  
  38.    With fld
  39.       If Not .AllowZeroLength Then
  40.          .AllowZeroLength = True
  41.       End If
  42.    End With
  43.  
  44.    tbl.Fields.Append fld
  45.    Me.Refresh
  46.    MsgBox "Field has been successfully added to all tables", vbInformation, "Added Field"
  47.    DoCmd.GoToRecord , , acNewRec
  48.  
  49. End Sub
  50.  
  51.  

Thanks Mary,

But when I now create a new field it tells me that the "Data Table" is in use. After a debug it highlights "tbl.Fields.Append fld"

Could this be because the Data table is "set" twice, once as tbl and once and rs?

Set tbl = db.TableDefs("[Data Table]")

fieldname = Forms![Add field]![kolomnaam]
Fieldtype = Forms![Add field]![veldtype]

Set rs = db.OpenRecordset("[Data table]")



BTW: the form is set to "Data Entry: yes" (if that matters at all)

Thanks
Dec 22 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Glad you got it working.

Merry Christmas

Mary
Dec 22 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.