December 27, 2011 at 5:40 pm
Basics: I'm running access 2007 on windows 7. The Server is running T-SQL 2008 R2 in windows xp.
I have created the ODBC object to connect access with the SQL back end, but get an error when trying to update the linked tables (Linked Table Manager Btn).
Error Message:
{Microsoft SQL Server login
Connection failed:
SQL State: '08004'
SQL Server Error: 4060
Server rejected the connection, Access to selected database has been denied.}
I've been looking online and tried disconnecting the old database and updating...nothing. I've even tried every combination of disconnecting and connecting the old and new databases and still nothing.
I've logged into my workstation with the admin user and logged into the SQL server with sa rights and still cannot get the tables to update. I’ve even tried to create a new DB to connect to, but then I get another error with ultimately leads me back to the error above.
Any help or guidance you can provide would be greatly appreciated. I'm kind of a newbie at this...
David92595
December 27, 2011 at 7:03 pm
It sounds like the following:
The Login that was used to link to the table does not have a User Account for the Database in which the table resides.
You can identify the Login that is being used by querying the Connect Column in the MySysObjects table.
The Connect Column contains the entire connection string including the Login & DSN that is used to connect.
I would check SQL Server for the Login permissions first, GRANT Access to the DB and assign Database Fixed Server Roles or assign Object Permissions to the User Account.
Edit: If using SQL Server Authentication make sure that you have the correct Password. The password may be stored in the MySysObjects Table if when the table was linked the checkbox save password was checked.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 28, 2011 at 12:48 pm
You should consider posting your Microsoft Access issues to the Microsoft Access Forum.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 28, 2011 at 1:16 pm
I'll do that. Your advise was helpful, but I've been having trouble with the SQL permissions. I've found them, I've given myself permission, but still have had no luck.
David92595
December 28, 2011 at 5:38 pm
dkatanski (12/28/2011)
I'll do that. Your advise was helpful, but I've been having trouble with the SQL permissions. I've found them, I've given myself permission, but still have had no luck.David92595
Did you find your Login (Server Level)? Grant yourself Access to the Database from your Login.
Then you need to find your User Account (Database).
The Account that is being used to read/write will need to have at least be a Member of the the Database roles db_datareader & db_datawriter.
Try and provide a little more detail as to what you have done and what you are having trouble with.
It may seem like a lot but is relatively straight forward. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 29, 2011 at 11:20 am
I'm logging onto the server as the sysadmin, so I should have access to everything. But I am unsure how to confirm that.
December 29, 2011 at 11:27 am
dkatanski (12/29/2011)
I'm logging onto the server as the sysadmin, so I should have access to everything. But I am unsure how to confirm that.
If you log on with an Account that is a member of the Fixed Server Role sysadmin that you perform any SQL Server Operation on that instance.
Did you find out what Account & Password that was used to Link the Access Table?
If you find that out and determine that the Account has access to the Database or relink the Table with an Account that does then you will not get the error.
Have you tried looking at the Connects Column in the Access System Table that I specified?
Have you looked at the DSN?
The next logical step is to log onto SQL Server after you have a better idea what you are looking for
By simply logging on with an Account that is sysadmin by itself is not going to do anything for you.
Good luck.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 29, 2011 at 11:30 am
Don't forget to make sure that the Account that is stored in the connections column of mysysobjects matches up with the Account used by the DSN.
Also make sure that the DSN specified in the system table exists.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 29, 2011 at 11:58 am
ah, I think we have hit a key point.
Half the tables are linked to one DB and the other half are set to another DB, and I now need to link them to a third DB.
I'm not sure where to start...should I try to connect them to the orginal DB? and then change it once it if it works?
December 29, 2011 at 12:07 pm
If you have 3 different databases then you need 3 different DSN's. Use the appropriate DSN for for the table you are linking. I usually have no trouble linking tables to different SQL databases from the same Access MDB.
Todd Fifield
December 29, 2011 at 12:11 pm
tfifield (12/29/2011)
If you have 3 different databases then you need 3 different DSN's. Use the appropriate DSN for for the table you are linking. I usually have no trouble linking tables to different SQL databases from the same Access MDB.Todd Fifield
Yes and in addition to linking to different SQL Databases you can link to databases that reside on different SQL Servers as well as Oracle, DB2, Sybase, Excel Documents, Text Files, other Access Databases, etc.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply