Change table property by code

[Ian King]

I need a module to change a table field data type from integer to text (255
chars) to update previous versions of our software.

I've tried setting the Type and Size properties, but I get an 'Invalid use
of property' error message.

Can anyone kelp?

Many thanks

Ian King

 

[Mark]

Ian

I have an app that needs to allow users to add/change table fields at
runtime and it is problematic as you can't just change the data type or
field size on the fly. The only reliable solution I found (for Access 2002)
was to add a new field with the properties you want, copy the data into it
from the old field then delete the old field and finally rename the new
field to match the original.

Regards
Mark

 

[Ian King]

Bit embarrasing really.

Found the following example in Access Help (Jet)

Sub AlterTableX2()

Dim dbs As Database


' Modify this line to include the path to Northwind

' on your computer.

Set dbs = OpenDatabase("Northwind.mdb")


' Add the Salary field to the Employees table

' and make it a Money data type.

dbs.Execute "ALTER TABLE Employees " _

& "ALTER COLUMN Salary CHAR(20);"


dbs.Close


End Sub

Hope this helps Mark as well



Ian King

 

[Peter Hibbs]

Ian,

If you are likely to make further changes to the Back-End file you
might want to look at :-
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE Update Utility.mdb'
which will do that for you. Just import the form, table and code
module into your database, add one line of code to your 'start up'
form and you are done. To add a new table or field to the Back-End you
just call up the BE Update form, enter the details of the table,
field, relationship, etc and the Back-End file is updated without
affecting the client's existing data.

You will also need to add relinking code to relink the Front-End file
to the tables in the Back-End file. See this site to do that
automatically :-
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE_ReLink.mdb'

There is full documentation on both sites to explain the procedure
fully.

HTH

Peter Hibbs.

 

[Mark]

Hi Ian

Thanks for the update. That looks nice and simple if it does what you need.
Our app allows complete change of data type... e.g. text to numeric and we
hit problems in runtime environment. Hopefully you won't need to go to the
lenghts we had to.

Mark.