By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,702 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,702 IT Pros & Developers. It's quick & easy.

How to rename field with link table in access 2003

Alias90
P: 23
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!!!
Nov 26 '12 #1

✓ answered by 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"

Share this Question
Share on Google+
10 Replies


zmbd
Expert Mod 5K+
P: 5,397
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.
Nov 26 '12 #2

Alias90
P: 23
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.
Nov 26 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
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"
Nov 26 '12 #4

NeoPa
Expert Mod 15k+
P: 31,769
@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?
Nov 26 '12 #5

zmbd
Expert Mod 5K+
P: 5,397
NeoPa:
OP was trying to change the field in a linked table within Access front-end; this is a prohibited action.
Nov 26 '12 #6

NeoPa
Expert Mod 15k+
P: 31,769
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).
Nov 27 '12 #7

Alias90
P: 23
Zmbd@!
Thank U so much!
Nov 27 '12 #8

NeoPa
Expert Mod 15k+
P: 31,769
@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).
Nov 27 '12 #9

Alias90
P: 23
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
Nov 27 '12 #10

NeoPa
Expert Mod 15k+
P: 31,769
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.
Nov 27 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.