Adding a new column in Access

[Peter Richards]

Hi,

Using the following ASP code:

<%
UpdateDB("ALTER TABLE tblConfig ADD SIMVersion varchar(10) NULL AFTER
Version" )

' Subroutine to update the database and check for any errors that
occured
Sub UpdateDB(strSQLCommand)
If Not Application("Debug") Then
On Error Resume Next
End If
If Not blnError Then
cnnDB.Execute (strSQLCommand)
If Err.Number <> 0 Then
blnError = True
intErrorNum = Err.Number
strErrorDesc = Err.Description
strErrorSrc = Err.Source
strErrorCmd = strSQLCommand
Err.Clear
End If
End If
End Sub
%>
.... results in the error msg:

Number: -2147217900
Source: Microsoft JET Database Engine
Description: Syntax error in ALTER TABLE statement.

Ideally, I want to add the new column AFTER a specific column. I note
you can do this in Fox Pro, MySQL, so thought I could do it in ASP for
Access or SQL Server ?

Peter

[Ray at]

I'm sure someone will correct me if I'm wrong, but I don't believe you can
specify "after columnname" when you add a column in Access. It'll just get
added to the end. So, this would work, although it won't do exactly what
you want.

UpdateDB("ALTER TABLE tblConfig ADD SIMVersion varchar(10) NULL" )

Ray at home

--
Will trade ASP help for SQL Server help


"Peter Richards" <jehoshua@my-deja.com> wrote in message
news:8ttbivkppd2ehscto52l50a5d6uuck9c12@4ax.com...

> Hi,
>
> Using the following ASP code:
>
> <%
> UpdateDB("ALTER TABLE tblConfig ADD SIMVersion varchar(10) NULL AFTER
> Version" )
>
> ' Subroutine to update the database and check for any errors that
> occured
> Sub UpdateDB(strSQLCommand)
> If Not Application("Debug") Then
> On Error Resume Next
> End If
> If Not blnError Then
> cnnDB.Execute (strSQLCommand)
> If Err.Number <> 0 Then
> blnError = True
> intErrorNum = Err.Number
> strErrorDesc = Err.Description
> strErrorSrc = Err.Source
> strErrorCmd = strSQLCommand
> Err.Clear
> End If
> End If
> End Sub
> %>
> ... results in the error msg:
>
> Number: -2147217900
> Source: Microsoft JET Database Engine
> Description: Syntax error in ALTER TABLE statement.
>
> Ideally, I want to add the new column AFTER a specific column. I note
> you can do this in Fox Pro, MySQL, so thought I could do it in ASP for
> Access or SQL Server ?
>
> Peter
>

[Ray at]

Yeah, same with Enterprise Manager. Obviously that's executing some t-sql
that's doing what you want. Hmm, I wonder if there's a T-SQL sniffer that I
can find. ;] I won't be surpised if someone else posts with a better
solution that will allow you to insert the column in the position you want.

Ray at home

--
Will trade ASP help for SQL Server help


"Peter Richards" <jehoshua@my-deja.com> wrote in message
news:ocvbivgltqscgm303dthemor70bptcek39@4ax.com...

> Hi Ray,
>
> On Tue, 29 Jul 2003 00:49:48 -0400, "Ray at <%=sLocation%>"
> <myfirstname at lane34 dot com> wrote:
>

> >I'm sure someone will correct me if I'm wrong, but I don't believe you

can

> >specify "after columnname" when you add a column in Access. It'll just

get

> >added to the end.

>
> That's a bother. When I'm using Access interactively, in design mode,
> I can position to a column, and then do an insert, for a new column
> and specify exactly where to place the column.
>

> >So, this would work, although it won't do exactly what
> >you want.
> >
> >UpdateDB("ALTER TABLE tblConfig ADD SIMVersion varchar(10) NULL" )

>
> Yes, it does.
>
> Thanks,
>
> Peter
>

[Bob Barrows]

Ray at <%=sLocation%> wrote:

> Yeah, same with Enterprise Manager. Obviously that's executing some
> t-sql that's doing what you want.

Yep - just like in Access, a new table is being created with the desired
column order, then the data from the old table is moved into the new table
and the old table is dropped.

> Hmm, I wonder if there's a T-SQL
> sniffer that I can find. ;]

Shouldn't be too hard to find it: it's called SQL Profiler and it can be
found in your Windows Start menu in the SQL Server program group. :-)

Actually, there's an easier way. There's a button you can click in the EM
table designer that allows you to see the script being used to effect the
change.

> I won't be surpised if someone else
> posts with a better solution that will allow you to insert the column
> in the position you want.

I would :-)

Using DAO, there's a property called OrdinalPosition, that sets or reads the
relative position of the fields in the table. However, this does not affect
the physical order of the fields: only the order in which the fields will
retrieved when doing a selstar query, which should not be used in production
code, anyways, right? :-)

HTH,
Bob Barrows

[Ray at]

It did. Thanks Bob!

Ray at home

> "Bob Barrows"
> HTH,

[Aaron Bertrand - MVP]

> Using DAO, there's a property called OrdinalPosition, that sets or reads
the

> relative position of the fields in the table. However, this does not

affect

> the physical order of the fields: only the order in which the fields will
> retrieved when doing a selstar query,

Really? Is this true for Access only?

[Bob Barrows]

Aaron Bertrand - MVP wrote:

>> Using DAO, there's a property called OrdinalPosition, that sets or
>> reads the relative position of the fields in the table. However,
>> this does not affect the physical order of the fields: only the
>> order in which the fields will retrieved when doing a selstar query,

>
> Really? Is this true for Access only?

I'm 90% sure it's only read/write for Jet databases. As I said, it's a DAO
property: it is not available in ADOX. I suspect that it will not have any
effect on queries run by ADO.

Bob

[Peter Richards]

Hi,

Thanks everyone for your replies. The bottom line is that it can't be
done in ASP on an Access db.

Peter

[Peter Richards]

Hi Aaron,

On Tue, 29 Jul 2003 09:17:40 -0400, "Aaron Bertrand - MVP"
<aaron@TRASHaspfaq.com> wrote:

>Why does it matter where a column sits?

I simply prefer it that way, as I find once a table gets more than
about 30 columns, from a design point of view, I would prefer an
ordered view of columns, it simply makes sense and makes maintenanance
easier. Being restricted by having to add new columns at the end of
the table moves away from a structured view of the table, even if the
'view' is only a logical one, that would help a lot.

The columns I need to add are adding two more dimensions to a
hierarchy, therefore (for me) from a design point of view, I prefer to
"see" all the hierarchy represented, logically at least (physical
order doesn't concern me) in a meaningful and structured manner.

>If you want to pull data out of the
>table with columns in a specific order, list them that way in the SELECT
>statement. You shouldn't care about the physical structure... but if you
>want to do it that way, you can do what SQL Server, and surely Access, do:
>create a dummy table with the new column in the correct place, move all the
>data, disable constraints, swap table names, re-enable constraints, and drop
>the original table. Yeah, that sounds like fun.

Yes, rather a painful exercise indeed.

Peter

[Aaron Bertrand [MVP]]

> >Why does it matter where a column sits?

>
> I simply prefer it that way, as I find once a table gets more than
> about 30 columns, from a design point of view, I would prefer an
> ordered view of columns, it simply makes sense and makes maintenanance
> easier.

Shouldn't you have designed the table from the ground up? As soon as you
start adding columns on the fly after the fact, you can smell lack of good
design... also, once a table gets more than about 30 columns, I think it
has too many columns and some normalization may be in order...

[Peter Richards]

Hi,

On Tue, 29 Jul 2003 21:49:32 -0400, "Aaron Bertrand [MVP]"
<aaron@TRASHaspfaq.com> wrote:

>Shouldn't you have designed the table from the ground up?

Yes, I wish I had have, but I'm making changes to someone elses
design, a GNU product. The changes I'm making have been requested by
many people for a year or more, so they (the changes) are long
overdue. But hey, you get what you pay for. :)

>As soon as you
>start adding columns on the fly after the fact, you can smell lack of good
>design... also, once a table gets more than about 30 columns, I think it
>has too many columns and some normalization may be in order...

I agree.

Peter

[Aaron Bertrand - MVP]

> Have you never worked in a changing environment?

Uh, yes. Currently, in fact.