alter table, please help with syntax error

[hollis via AccessMonster.com]

I am building a RUNSQL macro action for an Access 2003 database. The data is
originally downloaded from an unnormalized excel spreadsheet. My TempTable1
is generated from a series of queries that clean up the data. When the query
builds the table the expression is given the field name Expr1 with a TEXT
data type. I need to change the data type of from TEXT to NUMBER. After
repeated syntax errors, I am in need of some guidance.

My macro:

OpenTable
RunSQL
ALTER TABLE TempTable1 ALTER COLUMN Expr1 NUMBER (15);
Save

thanks in advance!
-S.

 

[Allen Browne]

What kind of Number field do you want?

If you want whole numbers, try:
ALTER TABLE TempTable1 ALTER COLUMN Expr1 LONG;
or if you need fractional numbers:
ALTER TABLE TempTable1 ALTER COLUMN Expr1 DOUBLE;

Here's a reference list of the names to use compared to the name in the
interface:
http://allenbrowne.com/ser-49.html

 

[hollis via AccessMonster.com]

I changed my data type to LONG and did not receive a syntax error (..thank
you..)

Though now when I run my macro it states: "The database engine could not
lock the table 'TempTable1' because it is already in use by another person or
process." I checked to make sure I did not have a window hidden somewhere
and the only thing I can find is the macro itself starts with open table,
then steps into runSQL...any suggestions?

What kind of Number field do you want?

If you want whole numbers, try:
ALTER TABLE TempTable1 ALTER COLUMN Expr1 LONG;
or if you need fractional numbers:
ALTER TABLE TempTable1 ALTER COLUMN Expr1 DOUBLE;

Here's a reference list of the names to use compared to the name in the
interface:
http://allenbrowne.com/ser-49.html

I am building a RUNSQL macro action for an Access 2003 database. The data
is

[quoted text clipped - 15 lines]

thanks in advance!
-S.

 

[Allen Browne]

Remove the OpenTable from the macro.

You won't be able to change the data type while the table is open.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I changed my data type to LONG and did not receive a syntax error (..thank
you..)

Though now when I run my macro it states: "The database engine could
not
lock the table 'TempTable1' because it is already in use by another person
or
process." I checked to make sure I did not have a window hidden
somewhere
and the only thing I can find is the macro itself starts with open table,
then steps into runSQL...any suggestions?

What kind of Number field do you want?

If you want whole numbers, try:
ALTER TABLE TempTable1 ALTER COLUMN Expr1 LONG;
or if you need fractional numbers:
ALTER TABLE TempTable1 ALTER COLUMN Expr1 DOUBLE;

Here's a reference list of the names to use compared to the name in the
interface:
http://allenbrowne.com/ser-49.html

I am building a RUNSQL macro action for an Access 2003 database. The
data
is

[quoted text clipped - 15 lines]

thanks in advance!
-S.