any sql peeps on?

[blacksunshine]

I mistakenly or ignorantly created a db with tables that had a column named Desc and am trying to change that column name via sql command but can't for the obvious reason....

no, I don't know sql that well but I can somewhat hack at it....

any he'p or do I just have to manually change the field in each table?

[redneck82]

Try this:

ALTER TABLE table_name RENAME COLUMN old_name to new_name

[blacksunshine]

Try this:

ALTER TABLE table_name RENAME COLUMN old_name to new_name

that's what I got but it no worky because the current column name is Desc, hence the problem...

is there a way to mark in/out that name?

[blacksunshine]

it's an access database so that makes it really sucky sucky...
I wonder if I could export it all to sql dump, replace with say wordpad, then import back in...

I have done this with mysql databases before....hmmm...

[redneck82]

Can you not open the table in Access and change the name?

Is it because Desc is a keyword in sql?

Can you remove and add a new column via the sql commands? Email me in the morning, if you still don't have it working and I will run it by our database guy that is a whiz in sql.

[JLively]

add brackets to the table name, i.e. [tbl name] this will define it as a table name rather than the keyword desc (for descending sort order).

[blacksunshine]

add brackets to the table name, i.e. [tbl name] this will define it as a table name rather than the keyword desc (for descending sort order).

its actually a column name and not a table.

Tried it anyway and it no likey...syntax error

[JLively]

Can you post your create table script so I can play with it a bit?

[blacksunshine]

Can you not open the table in Access and change the name?

Is it because Desc is a keyword in sql?

Can you remove and add a new column via the sql commands? Email me in the morning, if you still don't have it working and I will run it by our database guy that is a whiz in sql.

yes, I can open and change manually, but it isn't just one table...its about 500....and I not only need to change that column name, I need to create another, add a default value to it which is different for each of the 500+ tables and then once all that is done, pull all the data from the 500+ tables and combine them in a master table...

this was about as bad a database design as possible...but hey, that is what happens when you learn on the fly, teaching yourself...haha

I think if I could just figure how to dump this mdb to an sql.text file, I can finger the bulk of it out the easy way... find/mass replace

[JLively]

For something like that I would probably use SQL server anyways. You could ODBC from SQL management studio, but it would still be an access back end. You could always create a blank DB in SQL server and import from an MDB. Then in SQL management studio you could create a cursor that loops through the tables then exec an alter table statement using the table variable name. This would also allow you to add your other field. It would not have the flexibility to add different default values. You could always create script to do this as well. I find this copy/paste easier than manually altering the table def.

[blacksunshine]

found an app on sourceforge, freesql that is letting me run commands against the mdb, but this one command is the one I can't get to work...now if I could get this to work and then shoe horn my four separate commands together in a string...it would make it easier...as it is now, I can run one command at a time, then have to manually change that column name, then I can pull the data out of that table and pt it in the master table

[Loneaggie]

ALTER TABLE testing CHANGE `desc` `newname` INT NULL DEFAULT NULL;

Instead of INT NULL DEFAULT NULL use whatever the column attributes are. This works in MySQL, so mileage may vary.

But as you know, there's a reason why web developers throw rotten fruit at people who mention access.

[blacksunshine]

ALTER TABLE testing CHANGE `desc` `newname` INT NULL DEFAULT NULL;

Instead of INT NULL DEFAULT NULL use whatever the column attributes are. This works in MySQL, so mileage may vary.

But as you know, there's a reason why web developers throw rotten fruit at people who mention access.

baby steps...baby steps....

need to do some interim modifications to my site and this is the way I have to do it right now...once I get that done, then I can venture in to a total conversion to a different facilitation...

[blacksunshine]

found 'db converter' on sourceforge...it is converting mdb to sql for mysql....see what I can do with that....then the fun part of converting back to mdb...

[blacksunshine]

well....got it out to sql, made changes....can't find a way to get it back in to mdb form