Modify Table Using Code

[priyammaheshwari]

Hi All,

I have a huge MS Access database in which requests are added at regular basis as in almost every minute.I have to modify the data type of one of the coloumns in this database,but i cant copy the database to my system and make this change and then put it back.So i want to make the changes through ASP code.I had written the following code to do the same but this is not working.

<%@Language=VBScript
Dim conn,rs,sConnection
    Set conn=Server.CreateObject("ADODB.Connection")
    sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("../fpdb/taxireq.mdb") & ";" & _
    "Persist Security Info=False"
    conn.Open(sConnection)
 
conn.execute("ALTER TABLE Request_detail MODIFY Namecon Memo")
response.write("done")
conn.close
%>
 

In the above code i want to change the data type of column "Namecon" from Text to Memo type because text type has limitation of storing only 255 characters.

Please suggest some changes which would help me in making the required change

Thanks in advance!!

[shweta123]

Hi,

In order to alter the table using code you have to make the following change in the query :

conn.execute("ALTER TABLE Request_detail alter column Namecon Memo")



Hope this helps.

[DrBunchman]

Hi priyammaheshwari,

I wouldn't advise changing the structure of your database through code while it is live - this could lead to all sorts of knock on problems that would suddenly affect your live users.

Of course, you might have already done this and nothing happened but I'd suggest finding some time to down the live system and testing the effect of the changes after you've made them before putting it back up.

Just my two pence worth!

Dr B