Solved

Connect Access to SQL Backend on New Server

Posted on 2015-01-01
24
177 Views
Last Modified: 2015-01-17
Hello,

I am not a SQL DBA or and Access developer.
We are in the process of moving to a new SQL server. The server is up and running and the databases are moved and up.
However we need to redirect the access database to the new SQL server.There is only 1 table affected.
When I look at the properties of the table I can easily see the ODBC connection defined to the old server.
I've tried to use the Linked Table Manager but I received an invalid procedure call or argument error. I've tried to re-register the accwiz.dll and the did not work.
I have also created a seperate System DSN for the new server and can connect. I do not know how to get the database connected though.

I have also take the step of trying to delete the table link from the front end Access and re-add it. But instead of 1 link to database,  it links to ALL the tables.

I am obviously missing something here.

Any assistance with this would be very appreciated.

Thanks.
0
Comment
Question by:cepolly
  • 13
  • 11
24 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Delete the linked table.

Do a get external data (or Import).

In the wizard, select link, ODBC, then choose your DSN that you connected with.

You should get a dialog asking which table(s) you want.   Select only the one you want.

Complete the wizard.

You should now be able to access your table.

Let me know...

Jim.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
Thanks for that. Yes that seems to be working. Didn't occur to me not to Select All.
It's a poorly designed database used to hold images. The DB is 124GB in size.

However it is taking a long time to run. I'm not sure what's normal.
It's been running about 35 minutes. It seems to be stalled.

Should I just let it run?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Make sure your choosing link, not import.

Jim.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
Thanks Jim. That did it. I did have to change the table name in access for it to work though. From dbo_name to just name.

Quick question though; now when I go to open the pictures by double-clicking the object I get an "the image document object isn't registered. the object may be calling and application that isn't installed. To register the application, reinstall it."

I believe that this is a remnant from the early development with XP in mind using the Kodak picture viewer.
Is there a way to just point it to the windows photo viewer or even adobe reader? And if so where would I look to make the change?

I could also open this to another question if you'd like.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<I believe that this is a remnant from the early development with XP in mind using the Kodak picture viewer.
Is there a way to just point it to the windows photo viewer or even adobe reader? And if so where would I look to make the change?>>

  Yes.   What's happening is Access itself doesn't know how to display a picture, so when you try to up something up in a Access field, it looks to Windows for it to provide the app that can display whatever.

 What the message is saying is that nothing is installed that can display whatever it is you have stored.

 Do you know what is stored in the field (i.e. JPEG, PNG, etc)?   If you have one of those files on disk, can you double click on it and open it?

 If so, most software has an options page that allows you to set the app as the "registered" app for that file type.  You can also do it through Windows, but fill in some details first and we'll see if we can't get you squared away.

and BTW, stripping off the dbo_ is a normal thing.  That's the default behavior when you link.  Sorry I didn't mention that.

Jim.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
Thanks Jim.

I need to backtrack a little.
I am trying to open the Access front end on other pcs but it is not working.

We're getting an "access runtime error 3151 odbc connection to'connection name' failed"
I made the system DSN as a domain administrator on our Terminal Server.
Do I need to setup a System DSN on all pcs?

Also we are set to use TCP/IP. The port is opened to the server and we can test connectivity with no issues via the DSN.
Does it need to be named pipes? I tried named pipes in the client confi in the link table wizard but it failed to connect.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
A system DSN should have worked.   Make sure though your using the correct ODBC admin applet.

On a 64 bit OS, there are two applets.  The one from the control panel is 64 bit.  If your Access install is 32 bit (look at help about), then you need to use:

C:\windows\syswow64\Odbcad32.exe

The 64 bit one (and the one that is accessed from control panel), is:

C:\windows\system32\Odbcad32.exe

Slightly confusing huh?

TCP/IP is fine.   Setup a system DSN, test that, and then Access should work.

Jim.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
I looked at that and when I ran it it showed the existing DSN.

I'm also wondering if i need to look at DB permissions and roles.

Also do I need to create the System DSN on the SQL Server?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Use the correct ODBC admin applet and make sure it connects when you hit the test button.

If it fails, then the setup is wrong.   If it works, then any errors in Access are due to permissions in SQL.

The DSN does not need to be created on the server.

Jim.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
ODBC tests successfully every time using sa credentials.
Still looking at roles and security.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
Just to clarify; if I link the table create the Machine DSN on one machine, should it work on all other machines that have the Access FE copied to their PC's?
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
cancel that. it did not work. still working on it
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
Just to clarify; if I link the table create the Machine DSN on one machine, should it work on all other machines that have the Access FE copied to their PC's?

  No, you would need to setup the DSN on every machine.   That's why many go "DNS less"; they supply everything required for the connection in the .connect property for the table, thus not requiring a DSN to exist at all.

 Setting up a DSN however is a good first step in trying to get things working because your establishing a connection outside of Access.  If you can't get it to work there, it's not going to work in Access no matter which way you do it.

Once you have that DSN, and use that in Access, then you can convert to DSN Less using the code found here:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

Jim.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
I am looking at this now but it may be a bit above my pay-grade.

There is a line:
"FixConnections "MyServer", "MyDatabase"

Do I try this first?

And then where would I run code?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
I'll walk you through that, but before we get there, do you have the linked tables working at this point for a user?

Jim.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
I did. But at this point I had to revert back to the old server's DB for the other users.

I can setup a test environment to make the changes though on a terminal server by just mapping 1 user profile to the access database on the new server and linking the table there.

This will only be an exercise as I will have to backup and restore the SQL databases from the old server to the new server again as well as copy the access databases over to the new server too as the data will be changed.

I plan to make another attempt this Friday so if I can get the test database working and can repeat the process on Friday, hopefully things will go much more smoothly.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
OK, so once you have all your linked tables working right in the DB using the DSN,  then you'd:

1. Open a standard module in the DB; click on modules, then "new module" (Not a class module).

2. Paste the code from the article into that.

3. Save the module.   Call it basDSNLess.

4. In the VBA Editor (where you see the code),  select Tools | References from the menu bar.  Find "Microsoft DAO 3.6 Object Library" and select it if it's not already in the list of references.

5. Now hit Ctrl/G, which brings up the debug or immediate window.

6.  Type:

FixConnections "<ServerName>","<DatabaseName>"

putting in the new server name and the database the tables are in, followed by a return

Now if your using Trusted Connections, that's all you need.   If your using a username and password to login to SQL, then you would type:

FixConnections "<ServerName>","<DatabaseName>","<user name>","<password>"

 Not that you would replace anything in < > with the actual values, so:

FixConnections "MyNewServer", "Manufacturing"

 The code will run through the DB and replace all the linked tables with new ones, so do this on a *copy* of the DB first.

Holler if any questions...

Jim.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
Ok thank you Jim.
I will work on this as soon as I can and post back.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
Hi Jim,

I've tried this and I'm getting an error. invalid argument 3001
I confirmed I'm running SR2b and Jet 35sp3.

It seems to also delete the table Link from the Access front end and I have to re-add the link each time I test the code.

Any thoughts on this?

Thanks.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<The code will run through the DB and replace all the linked tables with new ones, so do this on a *copy* of the DB first.>>

 That's the way the code works....if you read the comments on the page at the bottom, you'll note that is was suggested to store the links in a table because if you do have errors, you can loose the links.

<<I've tried this and I'm getting an error. invalid argument 3001>>

 Where?

Jim.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
I get it when I run the fix connections string.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Yes, but are you saying when you make the call itself, which you should be doing like this:

FixConnections "MyNewServer", "Manufacturing"

or like this

Call FixConnections ("MyNewServer", "Manufacturing")

 or is it somewhere else?   A couple of code checking techniques:

1. You can put a breakpoint (hit F9 when on a line) or a STOP in the code to have it stop.
2. Use F8 to single step line by line
3. Hover over variables or objects to see their values.
4. Use Ctrl/Shift/F8 to jump execute until the end of a procedure.
5. Use F5 to continue execution.

 So put a STOP at the top of the FixConnections routine.
 Then call it from the debug window (Ctrl/G) with:


FixConnections "MyNewServer", "Manufacturing"

followed by a return.  If you do that right, you'll hit the stop.  Then start pressing F8 to step through the code and see what it's doing.

Jim.
0
 
LVL 1

Author Comment

by:cepolly
Comment Utility
Sorry Jim. I have been very busy.
I had a friend look at the code and he verified it worked after removing some code that others had issues with as well. I attached the code with the following line rem'ed out:
'tdfCurrent.Attributes = typNewTables(intLoop).Attributes

He also changed the Fix Connections string to add the actual tables as there were 2 databases that needed to be referenced as follows:
FixConnections "SQL-SERVER", "DATABASE", "TABLE", "USERNAME", "PASSWORD”
FixConnections "SQL-SERVER", "DATABASE2", "TABLE2", "USERNAME", "PASSWORD"

We ran it once per table.

So basically, with a little tweaking, it worked.
Thanks very much for the assistance.
RelinkAccessTables.txt
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Glad to hear your set.

Jim.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
If you don't know how to downgrade, my instructions below should be helpful.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

744 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

8 Experts available now in Live!

Get 1:1 Help Now