Wednesday, March 21, 2012

Help connecting to SQL Database

Hi. I'm totally new here, and I'm not an expert when it comes to SQL, so bear with me. This is my dilemma:

I use Access 2000 to connect to my Microsoft SQL Server 2000 database. Recently, our provider switched us from a shared server to a dedicated server. The site is up and running perfectly, but I can't connect to my database via Access anymore. Once I received the new location of the database, I ran odbcad32.exe and configured the database's DSN info where appropriate. I run the "Test Data Source..." function and the tests are seemingly successful. When I open my database using Access, I'm able to log in successfully, but when I click on any one of my linked tables, I get the following error:

Could not execute query; could not find linked table.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'NAMEOFDATABASE.NAMEOFTABLE'. (#208)

The support team at my provider are not exactly geniuses, so who knows if and when they'll be getting back to me. In the meantime, does anyone have a clue what might be causing this? Since the site is working perfectly, I assume the connection strings are okay and the database is perfectly intact (I even checked to see that the most recent entries were live), so what gives? If there's any more info I can provide, let me know. Thanks in advance.This is a pain. You have to view the linked tables in design view. Right-click on the title bar and select properties. You can then change the connection string to what it needs to be. The connection string gets hard-coded. Isn't that nice. :)

It's usually easier to just drop the linked tables and recreate all of them.|||Thank you. I was able to open each table in design view, but when I right clicked on the title bar and selected propeterties, I wasn't able to change the connection string under Description. This is so weird to me, since I'm using the same version of Access I used to access the SQL database when it was on a different server. I'm also able to make the connection and open the database using SQL Query Tool, but that obviously doesn't help me when it comes to inputing data. How do I exactly drop the tables and recreate them? :(|||delete the linked tables in access (only the linked tables!!). this does NOT delete the real tables on your server, only the links to them.

...then relink to them in their new location.

izy

LATER: i mean delete the links to the linked tables... ie in A's database window / tables, use the DEL button on all the linked tables|||Thanks izyrider. Repulsion you are just deleting the links like he said.|||Thanks derrickleggett and izyrider for bearing with me. That was a big help and with a little experimenting I was able to delete the linked talbes in Access, relinked to them in their new location, and lo and behold: I don't get the error message anymore. I can see all my tables, but now I can't input, change or delete data. The User and Group Permissions function under Tools-->Secruity seems to be locked. Is there something I can do on my end to actually use my database now, or do I have to contact my provider to deal with permission settings?|||If it's a third-party database contact your provider.sql

No comments:

Post a Comment