ms access 2010 + sql backend settings

Let's say the front end points to table1 and you are going to move it to table2.

Create table2 as select * from table1 (just as an example).

Drop table table1;

Now, create view table1 as (select * from table2).

This should work for you.  No need to mess with the front end.

Ss

Your question is somewhat hard to understand.

Are you moving (i.e. "renaming") a TABLE? Or are you moving to a new SERVER?

Either way, IMO the best method to use is to make database changes, and then relink ALL tables. If you've moved the "server", then you must do this anyway, but even if you're referrring to just renaming/moving a table I think you are best served by doing a relink. It's quick and easy, and it's a one-time thing.

yes i am moving to a new server, how do i relink the table to the new server ?

Yes, there is a little man in the Access front end who can do this for you. He is a wizard...

Just kidding.

The easiest way to change a table link is to rename or delete the existing link, then link to the new table using the original name so that any Access objects refer to the new table.

How are you connected to the server now? Are you using a DSN to do this?

If you are, then the simplest way is to delete all the LINKED tables, then build a new DSN that points to the new server. To do that, click the External Data group - ODBC Databases, and then select the Link option. Click the OK button and you'll see the applet that allows you to either select an existing DSN, or build a New DSN. Choose which type is appropriate for you, and follow the prompts to build a new DSN.

Note that you _can_ modify the existing DSN via the ODBC applet (Control Panel - System - Administrative Tools - Data Sources, locate and modify your existing data source) but this is often more trouble than it's worth, and in any case you always end up deleting and recreating the links. May as well do it right from the start.

If you're not using a DSN, then you'll have to dig a little deeper and determine exactly how you're connecting. Many Access linked apps use a "DSNless connection", which are built via code.

I think is DSNless connection - where would I find the code for it - I originally used the export to SQL and select link tables. I didn't create a ODBC connection

I'm pretty sure that created a DSN for you. You can tell by reviewing the connection for one of your linked tables. Easiest way is to "hover" the mouse over the linked table, and Access will pop up a tooltip showing you the connection for that table. If you see the "DSN" keyword, you're dealing with a DSN.

If not, then you'll have to review the code to determine where (and when) the relink is made. This is generally done in Startup code, but not every developer relinks tables each time.

ok when i hover over it shows ODBC - thanks