473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to ADD a column at FIRST in a table

126 100+
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
20 12419
NeoPa
32,556 Expert Mod 16PB
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
JFKJr
126 100+
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
32,556 Expert Mod 16PB
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
JFKJr
126 100+
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
JFKJr
126 100+
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
32,556 Expert Mod 16PB
...
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
32,556 Expert Mod 16PB
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
JFKJr
126 100+
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
JFKJr
126 100+
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
32,556 Expert Mod 16PB
...
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
JFKJr
126 100+
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
JFKJr
126 100+
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
32,556 Expert Mod 16PB
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
JFKJr
126 100+
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
32,556 Expert Mod 16PB
Sorry. I missed post #15 earlier :(

Let me read up on the thread to the end...
Aug 7 '08 #18
NeoPa
32,556 Expert Mod 16PB
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
JFKJr
126 100+
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
32,556 Expert Mod 16PB
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

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

Similar topics

11
by: Peter Foti | last post by:
Hi all, I have a form that contains a 2 column table. In the left column is the description for a particular input (ie - "First Name", "Last Name", "Phone Number", etc.). In the right column...
1
by: Swadh | last post by:
Hi, I need urgent help on Windows Form datagrid. I have to Display a datagrid that has a textbox column and another one combo box column. I have two tables in my Access database as: Table 1:...
10
by: Colleyville Alan | last post by:
I am trying to turn a short and fat (63 columns) table into one that is tall and skinny (7 columns). Basically, I am trying to create a "reverse crosstab" using a looping structure in VBA along...
20
by: Steve Jorgensen | last post by:
Hi all, I've just finished almost all of what has turned out to be a real bear of a project. It has to import data from a monthly spreadsheet export from another program, and convert that into...
16
by: Geoff Jones | last post by:
Hi Can anybody help me with the following, hopefully simple, question? I have a table which I've connected to a dataset. I wish to add a new column to the beginning of the table and to fill...
18
by: chimalus | last post by:
I am using a table with no column widths specified, letting the table layout manager do its thing for figuring out the column widths, and this works just fine. Now I want to make the table...
2
by: Hymer | last post by:
Hello, I have a small two-column table with three rows. The first column has a logo and the second column has the name of the organization. The logo's in the first column are too high. That...
5
by: Hymer | last post by:
Hello, I have a small two-column table with three rows. The first column has a logo and the second column has the name of the organization. The logo's in the first column are too high. That...
3
by: Csaba Gabor | last post by:
I have a table with 3 rows, and two pieces of data to display in each row. However, the first element of the last two rows and the 2nd element of the 1st row are very short. This would seem to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.