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

How to ADD a column at FIRST in a table

100+
P: 126
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.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "ALTER TABLE TableData ADD COLUMN [Example] TEXT;"
  2. DoCmd.RunSQL "ALTER TABLE TableData MODIFY COLUMN [Example]ToMove TEXT FIRST;"
  3.  
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!
Aug 6 '08 #1
Share this Question
Share on Google+
20 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Aug 6 '08 #2

100+
P: 126
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/...umn-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.
Aug 6 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Aug 6 '08 #4

100+
P: 126
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!
Aug 6 '08 #5

100+
P: 126
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"

Expand|Select|Wrap|Line Numbers
  1. sql1 = "INSERT INTO Table1 " & _
  2.           "SELECT Table2.* " & _
  3.           "FROM Table2;"
  4. 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!
Aug 6 '08 #6

NeoPa
Expert Mod 15k+
P: 31,186
...
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.
Expand|Select|Wrap|Line Numbers
  1. Table Name=[tblStudent]
  2. Field; Type; IndexInfo
  3. StudentID; AutoNumber; PK
  4. Family; String; FK
  5. Name; String
  6. University; String; FK
  7. Mark; Numeric
  8. LastAttendance; Date/Time
Aug 6 '08 #7

NeoPa
Expert Mod 15k+
P: 31,186
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).
Aug 6 '08 #8

100+
P: 126
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!
Aug 6 '08 #9

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Public Sub CreateTable(strTable As String)
  2.     Dim tdfNew As TableDef
  3.     Dim fldThis As Field
  4.     Dim prpThis As Property
  5.     Dim strMsg As String
  6.  
  7.     With CurrentDb
  8.         Set tdfNew = .CreateTableDef(strTable)
  9.         With tdfNew
  10.             Call .Fields.Append(.CreateField("TextField", dbText))
  11.             Call .Fields.Append(.CreateField("IntegerField", dbInteger))
  12.             Call .Fields.Append(.CreateField("DateField", dbDate))
  13.         End With
  14.         Call .TableDefs.Append(tdfNew)
  15.         With tdfNew
  16.             For Each fldThis In .Fields
  17.                 Debug.Print fldThis.Name & " :"
  18.                 For Each prpThis In fldThis.Properties
  19.                     On Error Resume Next
  20.                     Debug.Print "  " & prpThis.Name & " (" & prpThis & ")"
  21.                     On Error GoTo 0
  22.                 Next prpThis
  23.             Next fldThis
  24.         End With
  25.     End With
  26. End Sub
I called this with the following code :
Expand|Select|Wrap|Line Numbers
  1. Call CreateTable("tblTest7")
and the results I got were :
Expand|Select|Wrap|Line Numbers
  1. TextField :
  2.   Attributes (2)
  3.   CollatingOrder (1024)
  4.   Type (10)
  5.   Name (TextField)
  6.   OrdinalPosition (0)
  7.   Size (255)
  8.   SourceField ()
  9.   SourceTable ()
  10.   DataUpdatable (False)
  11.   DefaultValue ()
  12.   ValidationRule ()
  13.   ValidationText ()
  14.   Required (False)
  15.   AllowZeroLength (False)
  16. IntegerField :
  17.   Attributes (1)
  18.   CollatingOrder (1024)
  19.   Type (3)
  20.   Name (IntegerField)
  21.   OrdinalPosition (1)
  22.   Size (2)
  23.   SourceField ()
  24.   SourceTable ()
  25.   DataUpdatable (False)
  26.   DefaultValue ()
  27.   ValidationRule ()
  28.   ValidationText ()
  29.   Required (False)
  30.   AllowZeroLength (False)
  31. DateField :
  32.   Attributes (1)
  33.   CollatingOrder (1024)
  34.   Type (8)
  35.   Name (DateField)
  36.   OrdinalPosition (2)
  37.   Size (8)
  38.   SourceField ()
  39.   SourceTable ()
  40.   DataUpdatable (False)
  41.   DefaultValue ()
  42.   ValidationRule ()
  43.   ValidationText ()
  44.   Required (False)
  45.   AllowZeroLength (False)
Aug 6 '08 #10

NeoPa
Expert Mod 15k+
P: 31,186
The next thing I tried (expecting failure in fact) was :
Expand|Select|Wrap|Line Numbers
  1. 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
Aug 6 '08 #11

100+
P: 126
The next thing I tried (expecting failure in fact) was :
Expand|Select|Wrap|Line Numbers
  1. 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 "ImportedData", which I imported the data from an excel spreadsheet. The "ImportedData" table may contain any number of fields.

Thank You very much for your help!
Aug 7 '08 #12

NeoPa
Expert Mod 15k+
P: 31,186
...
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.
Aug 7 '08 #13

100+
P: 126
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 "ImportedData" table using the following code:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportedData", "C:\ExportFile.xls", True
2. With the following function I added an empty text field "Extract" to the "ImportedData" 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.

Expand|Select|Wrap|Line Numbers
  1. Function Add_EmptyField()
  2. DoCmd.SetWarnings False
  3. DoCmd.RunSQL "ALTER TABLE ImportedData ADD COLUMN [Extract] TEXT;"
  4. CurrentDb.TableDefs("ImportedData").Fields("Extract").OrdinalPosition = 0
  5. DoCmd.SetWarnings True
  6. End Function
Please kindly give me an idea/help on how to proceed further. Thanks!
Aug 7 '08 #14

100+
P: 126
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 "ImportedData" table using the following code:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportedData", "C:\ExportFile.xls", True
2. With the following function I added an empty text field "Extract" to the "ImportedData" 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.

Expand|Select|Wrap|Line Numbers
  1. Function Add_EmptyField()
  2. DoCmd.SetWarnings False
  3. DoCmd.RunSQL "ALTER TABLE ImportedData ADD COLUMN [Extract] TEXT;"
  4. CurrentDb.TableDefs("ImportedData").Fields("Extract").OrdinalPosition = 0
  5. DoCmd.SetWarnings True
  6. 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! :-)
Aug 7 '08 #15

NeoPa
Expert Mod 15k+
P: 31,186
I'm sorry :(

I would have expected that to work.

What do you have at field #1 now? Is it an AutoNumber field or an Index or something?
Aug 7 '08 #16

100+
P: 126
I'm sorry :(

I would have expected that to work.

What do you have at field #1 now? Is it an AutoNumber field or an Index or something?
1. I have added an empty Text field "Extract" to "ImporedData" Table using the following code:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "ALTER TABLE ImportedData ADD COLUMN [Extract] TEXT;"
2. And, changed the position of the field to first using the following code:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("ImportedData").Fields("Extract").OrdinalPosition = 0
So, now the table contains "Extract" empty text field as field #1 followed by the fields which I imported from an excel spreadsheet.

As I said above, we can see the change in Design view of the table but not in Datasheet view.

And when we export the table into an excel spreadsheet, we can see the "Extract" empty field at first.

Thank you once again for your ideas/help.
Aug 7 '08 #17

NeoPa
Expert Mod 15k+
P: 31,186
Sorry. I missed post #15 earlier :(

Let me read up on the thread to the end...
Aug 7 '08 #18

NeoPa
Expert Mod 15k+
P: 31,186
OK - Now I've caught up.

So, other than the new field not appearing right at the beginning in the datasheet view (where it DOES appear in design view), it all works fine.

That doesn't sound too bad. I must admit that I didn't see that problem in my testing. Everything worked perfectly in both views, but I was working with a virgin table without indices etc, so maybe something about your imported table caused it not to allow the full change. I'm not really sure.

Anyway, you have what you need and that's the important thing :)
Aug 7 '08 #19

100+
P: 126
OK - Now I've caught up.

So, other than the new field not appearing right at the beginning in the datasheet view (where it DOES appear in design view), it all works fine.

That doesn't sound too bad. I must admit that I didn't see that problem in my testing. Everything worked perfectly in both views, but I was working with a virgin table without indices etc, so maybe something about your imported table caused it not to allow the full change. I'm not really sure.

Anyway, you have what you need and that's the important thing :)
Hello NeoPa,

I would like to mention one interesting note here

The reason why it worked in your case is:

1. Either you might have created a table with some fields (includes an empty field) and then moved the empty field to first using above "Ordinal position" code (line #4 in post #15).

2. OR, you might have added an empty field to a table manually and then moved the empty field to first using above "Ordinal position" code.

In both the above cases, we can see the change in both design and datasheet views.

But if you add an empty field to a table using "ALTER" command (as mentioned in my above post) and then try to move the field to first using above "Ordinal position" code, then you will see the change only in design view not in datasheet view.

Strange right ! if you want you can try it!

Hope I did not confuse you!

Let me know if any questions. Thanks!
Aug 7 '08 #20

NeoPa
Expert Mod 15k+
P: 31,186
That makes sense.

Does that not mean that you can avoid the problem completely then by using VBA code to add the field (as I did) rather than a SQL ALTER statement?

Does that sound like a plan?
Aug 7 '08 #21

Post your reply

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