Solved

ms access 2010 + sql backend settings

Posted on 2011-02-24
9
399 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
Comment Utility
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
Comment Utility
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
Comment Utility
yes i am moving to a new server, how do i relink the table to the new server ?
0
 
LVL 8

Expert Comment

by:infolurk
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 84
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Then I'd go the "recreate the DSN" route. That should work for you.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now