January 30, 2012 at 10:39 am
Hi,
I've checked the DSN settings and it's using the correct SQL user account and points to the correct database.
None of the tables/views show up regardless of whether I attempt to import or link to them.
Simon
January 30, 2012 at 10:48 am
gweiho4xd2 (1/30/2012)
Hi,I've checked the DSN settings and it's using the correct SQL user account and points to the correct database.
None of the tables/views show up regardless of whether I attempt to import or link to them.
Simon
List step by Step the Step the actions that you take to link a table. Include the Login Name, Database = Planning?,etc.
I suspect that you are leaving some information out or you would not be having this problem.
Can't you ask someone to help you?
I'm sure that you are making a simple mistake or you said/thought that you did something a particular way but you did not.
If I could see what you are doing, or not doing I could fix it in minutes or less.
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/
January 31, 2012 at 8:51 am
Hi,
Here's what I've done.
The following was done on an XP (32bit SP3) machine.
I deleted all existing system DSNs.
Clicked Start, Control Panel, Administrative Tools.
Double clicked Data Sources (ODBC).
On the ODBC Data Source Administrator window click the System DSN tab
Click Add....
From the Create New Data Source window I scroll down and double click the SQL Server line.
In the Create a New Data Source to SQL Server window I entered the following:
Name: Planning
Description: left blank
Server: IP address of our SQL 2000 server
Click Next
On the second screen I selected the With SQL Server authentication using a login ID and password entered by the user. option.
I left the default tick in the Connect to SQL Server to obtain default settings for the additional configuration options. check box.
Login ID: planning
Password: ************
I didn't change any of the settings in the Client Configuration button screen.
Click Next.
I selected the Change the default database to box.
I can see the following databases in the drop down list:
master
msdb
pubs
tempdb
planning
I selected planning.
No other options were changed from their default settings on this screen.
Click Next.
No settings on the next screen were changed from their default.
Click Finish.
I'm then shown a summary of the ODBC configuration.
If I click Test Data Source... I see:
Microsoft SQL Server ODBC Driver Version 03.85.1132
Running connectivity tests...
Attempting connection
Connection established
Verifying option settings
Disconnecting from server
TESTS COMPLETED SUCCESSFULLY!
Clicked OK.
Clicked OK again.
Clicked OK to close the ODBC Data Source Administrator window.
I then opened MS Access 2002 SP3 and created a new database on the desktop called db1.mdb.
Click File, Get External Data..., Link tables....
A Link window opens.
I change the Files of type drop down box to ODBC Databases().
A Select Data Source window opens up with two tabs (File Data Source and Machine Data Source).
I clicked on the Machine Data Source tab and double-clicked the Planning DSN I created earlier.
A small SQL Server Login dialog box appears.
I left the Use Trusted Connection checkbox unchecked.
The Login ID: was already populated with planning.
I entered the same password as I had earlier when I created the DSN.
Clicked OK.
A Link Tables window opened which should display a list of tables/views on the server.
This is where the problem is; there's nothing in the list.
As a further test I created a new database on the SQL server called odbcproblems which contained one table called Table1.
I didn't create any user accounts because I wanted to use the sa (system administrator) account.
I then created a new DSN on an XP machine called odbcproblems pointing at the new database.
When I attempted to link the table in MS Access the list of tables was blank.
I then restored a backup of the planning database to a copy of the SQL server. When I attempted to link to the copy of the database I could see the list of tables/views.
At this point I'm thinking of moving the database from the SQL 2000 server to our SQL 2008 R2 server. If I go down this route is there anything I need to be aware of?
Simon
January 31, 2012 at 4:17 pm
Sounds like your Planning User Account is orphaned or is not a member of the Fixed Database Role db_datareader role or does not have object permissions to any tables.
Login with a Login Account that is a member of the fixed server role sysadmin.
I can't remember exactly how to do this in 2000 but it is very similar:
Expand the Planning Database Node.
Expand the Security Node
Expand the Users Node.
Right click on the Planning User and Select Properties.
Make sure the User Name is not blank, if it is you have an orphaned user and you need to run a script to fix it.
Check to see if the user is a member of any database roles.
If not you can run a script to identify which permissions on which objects the user has.
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/
February 3, 2012 at 1:45 pm
Hi,
I've checked the planning account and it's not orphaned, has the role db_datareader and has permissions for all tables.
As I've mentioned before I have the same problem if I configure the DSN to use the 'sa' account.
It's now been decided to move the database to our faster SQL 2008 R2 server.
Thank you for the time you spent on this, it is appreciated.
Simon
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply