changing table structure

Hi All
I have developed an app using vb6 and sql server. App is now up and running at customer points.
I have added some more functionality in to the app for which i have added some more fields to the existing table and some new tables are also created.
Now I want to generate a query which if i run at customer system will change the structure of tables there without loosing the data.
And new tables must be created by the same query.
Is it possible to generate such query.If yes please let me know how?
Also how to run such query from vb6 app?
Please help
Thankx
Radhesham

To alter the existing tables, use the ALTER TABLE Sql statement:

Code:

---

Alter Table Table1 Add newfield1 varchar(50)

---

and the CREATE TABLE statement for your new tables:

Code:

---

Create Table NewTable1 (field1 int not null, field2 varchar(50) default(''))

---

You can look up those statements in SQL Server Books OnLine.

To rollout the changes to the remote customers, you could put your Alter/Create Table statements into a script and then have run the script via the osql utility or you can just run the statements using a connection object and pass it the statement:

VB Code:

Dim sSql As String
sSql = "ALTER TABLE Table1 ADD newfield1 varchar(50)"
oCN.Execute sSql

Thanks VB dba
I may not be explaining correctly about my problem.
What I mean to say is
I have installed an app at client system.
I have modified the app and so some tables but i do not remember
eaxctly what changes i made. So I want to write down the structure of all existing tables in say some text file or so.
The app will have a module which will perform the following task at client system.
Read the text file.
Check the database at client. If table doesnt exists, creates one.
If exists, alters that.
And all this will happen looking at the text file for every field defination.
So I want to suggestions
1>How to create such text file on my system.
2>How to execute it at client system.
You may have differant idea for doing the same task. Please let me
know if it is possible in some other way too.
Thank you
Radhesham

I want to change the field definition of an existing field.

This has worked in the past (with Access):

eg.
SQL = "ALTER TABLE myTable ALTER myField DOUBLE NOT NULL"

I have an existing VARCHAR(50) NULL field which I want to change to NOT NULL. This works OK :

SQL = "ALTER TABLE myTable ALTER myField VARCHAR(50) NOT NULL"

when I run the query from within access but when I run it via code, I get SQL syntax error. I know the query is identical bcz I am copying and pasting the SQL. The connection to the database is OK as I tried a drop table query via code which worked fine.