How to ADD a column at FIRST in a table

[JFKJr]

Hello everyone, this is probably the simple question, but it is giving me very hard time to figure out.

I am using the following "ALTER" statements to Add a column to a table and to move it to first (in Access VBA).

And, I am able to add a column to the table with the following ALTER statement at line #1, but unable to move the column to first with the ALTER statement at line #2.

DoCmd.RunSQL "ALTER TABLE TableData ADD COLUMN [Example] TEXT;"
DoCmd.RunSQL "ALTER TABLE TableData MODIFY COLUMN [Example]ToMove TEXT FIRST;"

I am getting "Syntax error in ALTER Table statement" error at line #2, can anyone suggest me how to add a column at first in a table using Access VBA?

Thanks!

[NeoPa]

This rather looks like you've just guessed at a syntax here.

There is an ALTER COLUMN version of the syntax, but the options provided simply allow changing the Type & the Size. I don't believe there is any SQL supported way to move a field within a table.

For further reading select Help (Microsoft Office Access Help F1) in the main Access window and navigate to Microsoft Jet SQL Reference within the Table of Contents.

[JFKJr]

This rather looks like you've just guessed at a syntax here.

There is an ALTER COLUMN version of the syntax, but the options provided simply allow changing the Type & the Size. I don't believe there is any SQL supported way to move a field within a table.

For further reading select Help (Microsoft Office Access Help F1) in the main Access window and navigate to Microsoft Jet SQL Reference within the Table of Contents.

Hello NeoPa, thanks for the reply.

Please kindly click the following link (MySQL Reference manual) and scroll down to "User Comments" section to see the above ALTER statement (line #2).

http://dev.mysql.com/doc/refman/5.0/en/change-column-order.html

SQL supports the way to move a field within a table, but I am not sure why Access VBA is not supporting the sql statement (line #2).

Am I missing something? Please let me know.

[NeoPa]

Well, I guess that the fact that it's a MySQL manual says everything. Clearly it wasn't a simple guess though - I can accept that. I'm sorry I suggested that.

However, the reference material for Access (or Access related material on the web) is really what you need for this.

Is there something specific about Access?
YES!

Access is not a fully grown-up SQL server. Compare it to Oracle; MSSQL; MySQL or any of the other fully functional SQL servers and you'll almost certainly be disappointed.

Access's strengths really lie in its programmability and ease of use, and particularly its ease of starting with for the lightly qualified.

In short, you do need to be especially careful with Access where it comes to back-end functionality. Your requirement being missing is entirely unsurprising to me, and I would suspect many others who deal with it a lot. I don't say I'm particularly happy or impressed, but not too surprised either. It's definitely a "Baby-Brother" product where it comes to back-end processing I'm afraid.

[JFKJr]

Well, I guess that the fact that it's a MySQL manual says everything. Clearly it wasn't a simple guess though - I can accept that. I'm sorry I suggested that.

However, the reference material for Access (or Access related material on the web) is really what you need for this.

Is there something specific about Access?
YES!

Access is not a fully grown-up SQL server. Compare it to Oracle; MSSQL; MySQL or any of the other fully functional SQL servers and you'll almost certainly be disappointed.

Access's strengths really lie in its programmability and ease of use, and particularly its ease of starting with for the lightly qualified.

In short, you do need to be especially careful with Access where it comes to back-end functionality. Your requirement being missing is entirely unsurprising to me, and I would suspect many others who deal with it a lot. I don't say I'm particularly happy or impressed, but not too surprised either. It's definitely a "Baby-Brother" product where it comes to back-end processing I'm afraid.

Hello NeoPa,

This is what I am trying to do, I have a table called "Table1" which contains several fields.

Now I would like to add an empty field "Field1" at first to the "Table1" table. Since it is not possible in Access VBA, I would like to move both the field "Field1" and "Table1" table fields to another table "Table2", which contains "Field1" field at first.

Please let me know how to write a query in Access VBA for the above solution.

Hope I did not confuse you!

Feel free to suggest any other alternative solutions to my problem

Thanks!

[JFKJr]

Hello NeoPa,

This is what I did :

1. Created "Table1" with an empty field "Field1"
2. "Table2" contains multiple fields

Now I would like to append "Table2" data to Table1.

I used the following query to perform the above operation, but it is displaying "INSERT INTO statement contains the following unknown field: 'Account Number'. Make sure you have typed the name correctly and try the operation again"

sql1 = "INSERT INTO Table1 " & _
          "SELECT Table2.* " & _
          "FROM Table2;"
DoCmd.RunSQL sql1

I know the error is occurring due to a space in column name "Account Number".

But I cannot change the column name, because I imported an excel spreadsheet data into "Table2", and the spreadsheet may contain any number of columns and any type of data each time I import data from excel to "Table2".

I do not have any control on the columns I import from the spreadsheet to the table "Table2".

I am really stuck with this, please kindly let me know what to do?

Thanks!

[NeoPa]

...
I am really stuck with this, please kindly let me know what to do?

Thanks!

I wish it were that easy JFKJr.

I can explain that it is NOT the space in the fieldname causing a problem in this instance.

At the point this is run (the SQL) is the layout of Table1 simply as you've stated above? With just the single field [Field1] in it?

If so then we have our problem. If not, can you provide the MetaData for BOTH tables (at the point where the SQL is executed) for me please.

The meta-data (info about the layout / structure) of the tables should be posted in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively.

Table Name=[[U]tblStudent[/U]]
[I]Field; Type; IndexInfo[/I]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[NeoPa]

I missed your post #5 earlier (my bad).

I would just add that, although this is not possible simply by using Jet SQL (Access's version of SQL), it is nevertheless quite likely to be possible using other functions in VBA.

Personally I would only ever consider working in this way for strictly temporary tables, but that may well be what you're working with.

As it's getting late now, let me know if you're interested in progressing further with this idea and I will see what I can dig up for you tomorrow (or maybe later tonight if things are quiet at home).

[JFKJr]

I missed your post #5 earlier (my bad).

I would just add that, although this is not possible simply by using Jet SQL (Access's version of SQL), it is nevertheless quite likely to be possible using other functions in VBA.

Personally I would only ever consider working in this way for strictly temporary tables, but that may well be what you're working with.

As it's getting late now, let me know if you're interested in progressing further with this idea and I will see what I can dig up for you tomorrow (or maybe later tonight if things are quiet at home).

Hello NeoPa, thank you very much for your help.

It will be much appreciated if I can know if there are any other ways in Access VBA, which lets me add an empty field at first to a table.

I am really very much interested to move further on my problem.

I will keep in touch with the post. Thanks!

[NeoPa]

It took a little digging, but I managed to get what you want I think.

Firstly, to better understand what's going on, review this code and the printed results :

Public Sub CreateTable(strTable As String)
    Dim tdfNew As TableDef
    Dim fldThis As Field
    Dim prpThis As Property
    Dim strMsg As String
    
    With CurrentDb
        Set tdfNew = .CreateTableDef(strTable)
        With tdfNew
            Call .Fields.Append(.CreateField("TextField", dbText))
            Call .Fields.Append(.CreateField("IntegerField", dbInteger))
            Call .Fields.Append(.CreateField("DateField", dbDate))
        End With
        Call .TableDefs.Append(tdfNew)
        With tdfNew
            For Each fldThis In .Fields
                Debug.Print fldThis.Name & " :"
                For Each prpThis In fldThis.Properties
                    On Error Resume Next
                    Debug.Print "  " & prpThis.Name & " (" & prpThis & ")"
                    On Error GoTo 0
                Next prpThis
            Next fldThis
        End With
    End With
End Sub

I called this with the following code :

Call CreateTable("tblTest7")

and the results I got were :

TextField :
  Attributes (2)
  CollatingOrder (1024)
  Type (10)
  Name (TextField)
  OrdinalPosition (0)
  Size (255)
  SourceField ()
  SourceTable ()
  DataUpdatable (False)
  DefaultValue ()
  ValidationRule ()
  ValidationText ()
  Required (False)
  AllowZeroLength (False)
IntegerField :
  Attributes (1)
  CollatingOrder (1024)
  Type (3)
  Name (IntegerField)
  OrdinalPosition (1)
  Size (2)
  SourceField ()
  SourceTable ()
  DataUpdatable (False)
  DefaultValue ()
  ValidationRule ()
  ValidationText ()
  Required (False)
  AllowZeroLength (False)
DateField :
  Attributes (1)
  CollatingOrder (1024)
  Type (8)
  Name (DateField)
  OrdinalPosition (2)
  Size (8)
  SourceField ()
  SourceTable ()
  DataUpdatable (False)
  DefaultValue ()
  ValidationRule ()
  ValidationText ()
  Required (False)
  AllowZeroLength (False)

[NeoPa]

The next thing I tried (expecting failure in fact) was :

CurrentDb.TableDefs("tblTest7").Fields("DateField").OrdinalPosition=0

and it worked a treat.

I opened the table up and, blow me if the DateField wasn't first in the list (It had been added as the third of course).

PS. Now to delete all the extra tables I added into my database :D

[JFKJr]

The next thing I tried (expecting failure in fact) was :

CurrentDb.TableDefs("tblTest7").Fields("DateField").OrdinalPosition=0

and it worked a treat.

I opened the table up and, blow me if the DateField wasn't first in the list (It had been added as the third of course).

PS. Now to delete all the extra tables I added into my database :D

Hello NeoPa, thanks for the above code.

Unfortunately I haven't followed your code, could you please explain me in detail, I have the following questions:

a) Is "tblTest7" is the table with multiple fields imported from an excel spreadsheet (as I said in my earlier post) and are we adding the three empty fields (Text, Integer and date fields) to the table?

b) OR, are we creating a table with three empty fields and then appending to the table with multiple fields imported from an excel spreadsheet.

Could you please explain me in detail, I am clueless!

I would like to add an empty TEXT field at first to the table, which I imported the table data from an excel spreadsheet.

for example, say I would like to add "Extract" empty Text field to the table "ImportedDa ta", which I imported the data from an excel spreadsheet. The "ImportedDa ta" table may contain any number of fields.

Thank You very much for your help!

[NeoPa]

...
a) Is "tblTest7" is the table with multiple fields imported from an excel spreadsheet (as I said in my earlier post) and are we adding the three empty fields (Text, Integer and date fields) to the table?
...

tblTest7 is simply the name I invoked the procedure with.

The procedure : Creates a table; Creates three fields (all of different types) within the new table; Displays all the properties of all the new fields in order (shown lower down in the same post).

This is all for illustrative purposes. It doesn't do your job for you, but provides useful template code for you to use.

...
b) OR, are we creating a table with three empty fields and then appending to the table with multiple fields imported from an excel spreadsheet.
...

No. Just creating the basic table.

However, later on (in the following post actually (#11)) I show how to change the position of a field within an existing table.

Clearly, the example code shows how you can get information about existing fields in existing tables. With all that available I would have thought you had the ammunition you need to get your job done.

[JFKJr]

tblTest7 is simply the name I invoked the procedure with.

The procedure : Creates a table; Creates three fields (all of different types) within the new table; Displays all the properties of all the new fields in order (shown lower down in the same post).

This is all for illustrative purposes. It doesn't do your job for you, but provides useful template code for you to use.

No. Just creating the basic table.

However, later on (in the following post actually (#11)) I show how to change the position of a field within an existing table.

Clearly, the example code shows how you can get information about existing fields in existing tables. With all that available I would have thought you had the ammunition you need to get your job done.

Hello NeoPa,

I am not sure of how to use the above code but this is what I have done:

1. Imported the excel spreadsheet data into "ImportedDa ta" table using the following code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportedData", "C:\ExportFile.xls", True

2. With the following function I added an empty text field "Extract" to the "ImportedDa ta" table.

3. But when I tried to move the "Extract" field to first using the code at line #4, the Extract field is moved to Second column but not to first.

Function Add_EmptyField()
DoCmd.SetWarnings False
DoCmd.RunSQL "ALTER TABLE ImportedData ADD COLUMN [Extract] TEXT;"
CurrentDb.TableDefs("ImportedData").Fields("Extract").OrdinalPosition = 0
DoCmd.SetWarnings True
End Function

Please kindly give me an idea/help on how to proceed further. Thanks!

[JFKJr]

Hello NeoPa,

I am not sure of how to use the above code but this is what I have done:

1. Imported the excel spreadsheet data into "ImportedDa ta" table using the following code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportedData", "C:\ExportFile.xls", True

2. With the following function I added an empty text field "Extract" to the "ImportedDa ta" table.

3. But when I tried to move the "Extract" field to first using the code at line #4, the Extract field is moved to Second column but not to first.

Function Add_EmptyField()
DoCmd.SetWarnings False
DoCmd.RunSQL "ALTER TABLE ImportedData ADD COLUMN [Extract] TEXT;"
CurrentDb.TableDefs("ImportedData").Fields("Extract").OrdinalPosition = 0
DoCmd.SetWarnings True
End Function

Please kindly give me an idea/help on how to proceed further. Thanks!

Hello NeoPa,

The above code which I posted, it works absolutely fine!

The code moves the new empty field to first position using line #4. In design view of the table we can see this change.

But in Datasheet view of the table, somehow the new empty field is not moving to the first column.

Anyway, when I exported the table data to an excel spreadsheet, I am able to see the empty column at first, which is exactly I want.

Thank You very much for your help! :-)