Solved

ms access 2010 + sql backend settings

Posted on 2011-02-24
9
404 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
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 84
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 84
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 84
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 84

Accepted Solution

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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

803 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