Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ms access 2010 + sql backend settings

Posted on 2011-02-24
9
Medium Priority
?
409 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:dkilby
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 9

Expert Comment

by:sshah254
ID: 34976084
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
 
LVL 85
ID: 34976262
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
 

Author Comment

by:dkilby
ID: 34976272
yes i am moving to a new server, how do i relink the table to the new server ?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 8

Expert Comment

by:infolurk
ID: 34976324
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
 
LVL 85
ID: 34976356
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
 

Author Comment

by:dkilby
ID: 34976436
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
 
LVL 85
ID: 34976483
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
 

Author Comment

by:dkilby
ID: 34976689
ok when i hover over it shows ODBC - thanks
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 34978583
Then I'd go the "recreate the DSN" route. That should work for you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question