Change type of Column in mdb database 1

[44nato44]

Hi

I am trying to change a column type in my mdb database, but it does not work for me.

This is my error :

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement.

/TriActis/ModifyColumn.asp, line 12

This is my code :

'Set Conn = Server.CreateObject("ADODB.Connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=" & Server.MapPath ("/_private/DB/MyDB.mdb")

Set Conn = Server.CreateObject("ADODB.Connection")
Set RsUsrID = Server.CreateObject("ADODB.Recordset")
Conn.Open DSN


strSQL = "ALTER TABLE tblToolTask"
strSQL = strSQL & "ALTER COLUMN HoursSpent Single"

RsUsrID.Open strSQL, DSN, 2

 

[vicvirk]

The syntax is:

ALTER TABLE table_name
ALTER column_name column_type;

"Single" is not a valid column type in access..

You should also need to define a size so something like this might work:

ALTER TABLE tblToolTask
ALTER HoursSpent text(25);

http://msdn.microsoft.com/en-us/library/bb177883.aspx

TIP: trying googling the answer before posting, you'll find that more times than not someone else somewhere has had the same request and posted an answer online.
----
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javascript enabled browsers

 

[44nato44]

Cheers, but I need the value to be a value where I can use decimal numbers.

Single type would work if I add a table from asp, I tried decimal and it does not work.

Do you have an idea what the decimal type would be ?

 

[vicvirk]

The column type would be "Number" the field size would be "Single":

Number(Single)



TIP: trying googling the answer before posting, you'll find that more times than not someone else somewhere has had the same request and posted an answer online.
----
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javascript enabled browsers

 

[44nato44]

Sorry, still no good.

My IIS log say that there is a syntax error.

Can it be that this is a old MS Access ?

This is the new code I tried :

strSQL = "ALTER TABLE tblToolTask"
strSQL = strSQL & " ALTER HoursSpent TEXT(10);

 

[vicvirk]

I don't know what to say - the script I provided is directly from Microsoft's site - check the link I posted earlier...

Just out of curiosity, why do you need to do this with a script, why not just open the db, adjust the col type and save it?



TIP: trying googling the answer before posting, you'll find that more times than not someone else somewhere has had the same request and posted an answer online.
----
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javascript enabled browsers

 

[44nato44]

I have kind of a log table in the db where I track what users are doing, so I might loose data if I took the db off line.

So I was hoping I could do it with the script

Thanks anyway

 

[DreXor]

my suggestion is to to do a table alter, insert a NEW field, as numeric as you're wanting
copy the old data to it, then rename the old/new fields, so the new becomes the old etc..

then you can delete the non number format.

my guess is you possibly have non numeric data in that field and hence the failures to alter the field

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

 

[vicvirk]

I have kind of a log table in the db where I track what users are doing, so I might loose data if I took the db off line.

So I was hoping I could do it with the script

Thanks anyway

ugh - this is one of the reasons I hate working with access - some of my clients still use it and I've had these issues in the past...

One thing I've done is picked a time when the activity on the site is low then replaced the site with a temp splash page saying it's undergoing maintenance.

Wait about 5 minutes to allow any user to finish what they are doing, update the db and bring the site back up.

Good luck

TIP: trying googling the answer before posting, you'll find that more times than not someone else somewhere has had the same request and posted an answer online.
----
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javascript enabled browsers