Connect Access to SQL Backend on New Server

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.

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?

Make sure your choosing link, not import.

Jim.

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.

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

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.

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.

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?

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.

ODBC tests successfully every time using sa credentials.
Still looking at roles and security.

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?

cancel that. it did not work. still working on it

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?

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.

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.

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.

Ok thank you Jim.
I will work on this as soon as I can and post back.

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.

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

I get it when I run the fix connections string.

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.

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

Glad to hear your set.

Jim.