January 19, 2012 at 7:33 am
Hi,
Please can someone help me with the following:
I can't see any tables (or views) in MS Access (ODBC) when I attempt to add a NEW linked table to ONE of our SQL 2000 databases.
I can successfully link/import data via ODBC from other databases on the server (SQL 2000 SP3,NT4 SP6a).
Previously linked tables in MS Access work fine, it's only new links ones which are the problem.
It was working fine on the 22-Dec-11 and nothing has been knowingly changed on the server.
I've tried 5 different machines (all XP) with various versions of MS Access (2000,2002,2003); all of them have the same problem.
How can I see the SQL tables/views in MS Access (Get External Data, Link Tables..)?
Thanks
Simon
January 19, 2012 at 7:38 am
What did you specify as the default database in your DSN?
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 20, 2012 at 3:20 am
Hi,
The default database is set by the SQL server user login to our 'planning' database.
I've also manually set the database to 'planning' in the ODBC connection but still no SQL tables/views are visible.
I've recreated the ODBC connection and just in case it was a client issue also set an XP machine up from scratch, but this didn't solve the problem.
Simon
January 20, 2012 at 5:26 am
gweiho4xd2 (1/20/2012)
Hi,The default database is set by the SQL server user login to our 'planning' database.
I've also manually set the database to 'planning' in the ODBC connection but still no SQL tables/views are visible.
I've recreated the ODBC connection and just in case it was a client issue also set an XP machine up from scratch, but this didn't solve the problem.
Simon
What Login are you using and are they a user in your database.
Is the user a member of the fixed Database Role db_datareader and db_datawriter?
At least read is required. It sounds like a permissions issue if you have the default database set correctly.
I have seen this a lot.
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 20, 2012 at 10:05 am
Hi,
I've tried using the 'sa' login and another login which had been working fine for years. The permissions for the other login are:
public
db_ddladmin
db_datareader
db_datawriter
I can access the data via ODBC assuming that I use an MS Access database which already contains linked tables. It's only a problem when I attempt to link (or import) a table.
Simon
January 20, 2012 at 12:54 pm
gweiho4xd2 (1/20/2012)
Hi,I've tried using the 'sa' login and another login which had been working fine for years. The permissions for the other login are:
public
db_ddladmin
db_datareader
db_datawriter
I can access the data via ODBC assuming that I use an MS Access database which already contains linked tables. It's only a problem when I attempt to link (or import) a table.
Simon
If you want to look at the Connection String information it is stored in the mySysObjects System Table in the Connections Column. You can create a query and Specify NOT NULL on the COnnections.
This may give you some hints. It should tell you what DSN, Login ,etc that are being used for the tables that are already linked.
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 23, 2012 at 8:11 am
Hi,
In the blank MS Access database I've created the MSysObjects table contains the default 17rows, none of which have a value in the Connect column.
I'm unable to link/import any tables in our 'planning' SQL database because I can't see the tables in the Link/Import Tables screen.
I've checked an old database which contains linked tables and can see the following in the MSysObjects table:
DSN=planning;APP=Microsoft Office XP;WSID=SYSTEM_ACCESS;DATABASE=planning
I appreciate your suggestions. Do you have any other ideas?
Simon
January 23, 2012 at 10:30 am
gweiho4xd2 (1/23/2012)
I've checked an old database which contains linked tables and can see the following in the MSysObjects table:
DSN=planning;APP=Microsoft Office XP;WSID=SYSTEM_ACCESS;DATABASE=planning
I appreciate your suggestions. Do you have any other ideas?
Simon
Simon,
ok, now you want to go the Control Panel>> Administrative Tools >> Double Click on Data Sources (ODBC).
Look for the DSN Named Planning. You will probably find it on the System Tab if it is not on the User Tab.
Select the DSN and Click Configure.
You will see the name of the DSN, possibly a description and the Server that it is connecting to.
Click next and note the Authentication Method and the User Account that the DSN is using.
If it is using SQL Server Authentication then you probably want to use that Account.
If it is using Windows Authentication what Account is it using? You want to make sure the
Account that you are using when you attempt to link to a table has at lease read and not
deny permissions.
Click next and note that the Default Database is set to planning.
Click Finish and click Test Data Source.
My guess is that the account that you are using to link to the tables does not have permissions.
Can you verify that? Do you have SQL Server Management Studio (SSMS) or SQL Server Express?
Try connecting to the Server, open the Database and selecting from a table.
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 24, 2012 at 9:22 am
Hi,
The planning DSN was listed under the System DSN tab.
The authentication was (and still is) set to SQL Server Authentication.
The default database was 'greyed out' and set to planning.
When I tested the datasource it worked fine. I've never had it fail at this point unless I've made a mistake with the SQL user account details.
All of the above was done from a Win XP machine.
The server I'm having problems with is NT4 SP6a running SQL 2000 SP3.
I can see all the tables/views in the planning database when I run 'SQL Server Enterprise Manager' on the server.
If I run 'Microsoft SQL Server Management Studio Express 2005' from the Win XP machine and authenticate using the same SQL Server user account I used for the ODBC connection I can see all the tables and views.
Simon
January 24, 2012 at 9:36 am
You want to configure the DSN on the machine that you are having trouble with to use the same Login as the machine that is working.
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 25, 2012 at 8:13 am
Hi,
I don't have any client machines (all Win XP) which are able to see the list of tables/views on the SQL 2000 server over an ODBC connection.
I can see the SQL tables/views if I use 'Microsoft SQL Server Management Studio Express 2005 or 2008' on two of the client machines.
But ODBC connections on the two machines fail to list the table/views.
Simon
January 25, 2012 at 12:14 pm
gweiho4xd2 (1/25/2012)
Hi,I don't have any client machines (all Win XP) which are able to see the list of tables/views on the SQL 2000 server over an ODBC connection.
I can see the SQL tables/views if I use 'Microsoft SQL Server Management Studio Express 2005 or 2008' on two of the client machines.
But ODBC connections on the two machines fail to list the table/views.
Simon
The reason that the ODBC connections (DSN's) do not show any tables is because the Login that you are using does not have permissions.
Configure the DSN tro use the SQL Server Account that worked for you and be done with it.
This is not that hard, I would have thought with all of the information that I provided you that you would be up and running.:w00t:
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 30, 2012 at 9:31 am
Hi,
I agree that it shouldn't be that hard, but it is.
I have configured the DSN to use the SQL Server Account which worked fine with 'Microsoft SQL Server Management Studio Express 2005' but I still don't see any tables/views via ODBC.
Simon
January 30, 2012 at 9:57 am
gweiho4xd2 (1/30/2012)
Hi,I agree that it shouldn't be that hard, but it is.
I have configured the DSN to use the SQL Server Account which worked fine with 'Microsoft SQL Server Management Studio Express 2005' but I still don't see any tables/views via ODBC.
Simon
Go to ODBC Connections.
Click on the DSN that you created and Select Configure, Click Next and verify that SQL Server Authentication is Selected. Make sure it is the Same Account that you used when you launched SSMS.
Click Next and the Change to Default Database should be checked and the Database that you are attempting to connect to should be selected.
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 30, 2012 at 9:58 am
Have you Linked any tables?
What happens when you try that?
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 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply