[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Connect Access to SQL Backend on New Server

Posted on 2015-01-01
24
Medium Priority
?
197 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 58
ID: 40527176
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
ID: 40527253
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 58
ID: 40527674
Make sure your choosing link, not import.

Jim.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:cepolly
ID: 40528145
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 58
ID: 40528320
<<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
ID: 40528654
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 58
ID: 40528675
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
ID: 40528691
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 58
ID: 40528699
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
ID: 40528708
ODBC tests successfully every time using sa credentials.
Still looking at roles and security.
0
 
LVL 1

Author Comment

by:cepolly
ID: 40528711
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
ID: 40528735
cancel that. it did not work. still working on it
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40529096
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
ID: 40530373
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 58
ID: 40531333
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
ID: 40531680
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 58
ID: 40531873
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
ID: 40533489
Ok thank you Jim.
I will work on this as soon as I can and post back.
0
 
LVL 1

Author Comment

by:cepolly
ID: 40541455
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 58
ID: 40542073
<<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
ID: 40542125
I get it when I run the fix connections string.
0
 
LVL 58
ID: 40544078
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
ID: 40554033
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 58
ID: 40555192
Glad to hear your set.

Jim.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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 …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

873 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