ms access 2010 + sql backend settings

If i am using sql server on the back end of a ms access front end, and want to move the table to a different table, is there someone in the front end to change the sql server name to point it to the new server?
dkilbyAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Then I'd go the "recreate the DSN" route. That should work for you.
0
 
sshah254Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
dkilbyAuthor Commented:
yes i am moving to a new server, how do i relink the table to the new server ?
0
 
infolurkCommented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

0
 
dkilbyAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
dkilbyAuthor Commented:
ok when i hover over it shows ODBC - thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.