Updating DB with new tables etc 1

[rubikWizard]

Hello

I have created an ASP application that uses a MS Access database. I have a lot of customers using this application on their own servers. From time to time I make improvements to the application and this often requires me to add or modify table details (ie new fields etc).

When my clients update their ASP pages they then need to email me their database so that I can make the necessary modifications by hand and then email it back to them (as they obviously do not want to use the new empty database)

What I would like to know is if there is anyway to automate this process. I.e. could I send them a small program/script that they can run to make the necessary changes? Is there any software available to do this? Or could I script some ASP to do this?

Any help would be gratefully appreciated!

Thanks
Neil

 

[Remou]

You can use SQL to add fields (columns) and keys to tables:

[tt]ALTER TABLE tblTable ADD COLUMN [ID] COUNTER CONSTRAINT [ID] PRIMARY KEY;[/tt]

 

[rubikWizard]

Thanks for that Remou, greatly appreciated.

Is there a command to rename a field without removing it's data/values?

How about checking for a fields existence? I.e. if someone runs the script twice I do not want 2 columns added or an error occuring?

Thanks for your excellent help
Neil

 

[Remou]

As far as I recall, to rename a field through SQL you need to create a new field, update it with the data and then delete the old field. You can create a recordset and go through the field collection, to see if a field exists.

Tek-Tips has a forum devoted to ASP and another for VBScript.

 

[rubikWizard]

Thanks again - I will look into it, now I have ben pointed in the correct direction!

Neil