How to rename field with link table in access 2003

[Alias90]

Hi Everybody!

I have a program combine database to SQL server 2005. I used
DOBC to connect to sql.I want to rename the field in sql database.I tried on testing code in local table in access. The code is OK, but do it in link table. The access is announce "Operation not supported on linked tables. (Error 3057) ". So how to solve it? Please help me!!!

[zmbd]

Alias90:
You can not rename linked tables.
You have to kill the link and then either go into the backend directly or in this case I believe that you can do a pass-thru query to send the SQL to the backend to rename the table... sorry, I don't do enough with the "SQL server 2005" to confirm that action however, that how I've had to handle things with the other servers.

Biggest thing to keep in mind is that the rename will not cascade thru relationships nor in queries so you take a huge risk in renaming a table in that doing so might break your frontend application at best and at worst can cause your entire backend concept to implode.

In any case, you can then relink to the renamed table.

[Alias90]

huhu! (crying)
Can we change another solution?'Cause the field which i want to change isn't the primary key.In this case, renaming field will not cascade throught relationships.

[zmbd]

You can try to just go into the backend directly to rename the field. However, with an existing link... it might break the link in the frontend. Easy enough to fix by relinking.

As for being primary key or not... really doesn't matter. The rename will not, as far as I know, cascade thru the structure. You will have to edit all the queries, views, etc... that refer to the old field name.

As this deals more with the SQL Server than Access I'm moving this thread to the MS SQL Server forum where maybe someone that understands this a little better can help.

I'm also renaming the thread to reflect the question at hand: "MS Access 2003 Front End to rename Field in SQL Server Back End"

[NeoPa]

@Alias90 - May I ask why changing the name of the field on the SQL Server would be a problem?

The linked table in the FE should pick up the change automatically and you'd be good to go. What further problems are you dealing with?

[zmbd]

NeoPa:
OP was trying to change the field in a linked table within Access front-end; this is a prohibited action.

[NeoPa]

I don't believe so Z.

Alias90:
I want to rename the field in sql database.I tried on testing code in local table in access. The code is OK, but do it in link table.

I read that to mean the intention was to rename the source field but the code tried to rename the linked field instead. This triggered an error (naturally enough).

[Alias90]

Zmbd@!
Thank U so much!

[NeoPa]

@Alias90:
There are outstanding questions posted for you (See post #5 below). Even when you get answers you're happy with it really isn't a good idea to ignore anyone who has shown an interest in your problem (or any other posts really).

[Alias90]

Neopa@!
'Cause our sql database has table A(A1,A2,A3,date) . my colleagues used to create the name field is "date". .But it's same key "date" in Access (We haven't permission rename field in sql).So when i edit the code, i used "insert into " structure, the Access is informed " systax error ".After, I coded( rename field "date" by "date1"),it run well . However, this is link table,the code cann't run in link table.That why i want to connect to sql database to rename field. My Eng isn't good.I hope everybody can understand my comment

[NeoPa]

The English is a bit of a problem, but let me say what I understand from that and you can tell me if I'm wrong.

NeoPa:
May I ask why changing the name of the field on the SQL Server would be a problem?

It wouldn't. That seems to be the required solution.

NeoPa:
What further problems are you dealing with?

None. All good from post #4.

In view of this understanding I will set Best Answer for you.