Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    3

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

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    May 2004
    Posts
    3
    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?
    Last edited by repulsion; 05-03-04 at 03:27.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Thanks izyrider. Repulsion you are just deleting the links like he said.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    May 2004
    Posts
    3
    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?

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If it's a third-party database contact your provider.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •