Access 2010 permissions issue revisited

[vba317]

I have copied an Access 2003 working database from one server to another. On the new server I created 5 new system DSN's through the SQL server with the ODBC connection program. I than created the ODBC connection in the Access database. I see all 5 ODBC connections I created from within the database. When I go to the connection wizard and I click on machine data source I see all the databases I linked. The problem is whatever database I click on I can only see system tables and not the tables I need. I tried to build an Access 2010 database with the same results. Any help is appreciated.

Tom

 

[dhookom]

Are you using a SQL Account or Windows Authentication? It seems your login doesn't have permissions to user tables in the database. Does everyone have the same issue? Have you tried different accounts/logins?

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I am using windows authentication. I will check with the admin this morning to have him review my permissions.
Tom

 

[vba317]

Here are the steps I have takes so far.
I was told by the admin that I had explicit rights. He deleted those rights in doing that he thought that would automatically raise my to the admin group.
I have added the SQL Server authentication to the SQL database. I then added a new login. After adding the new login in the user mappings I added every database in SQL to the mapping with accessadmin,owner,securityadmin and public to all the tables.

The server I am remoted into has Access 2010 installed on it. I am currently running an Access 2003 .mdb database which is having this issue.

I tried to create an Access 2010 database and link the an SQL table with no luck.

Any suggestions?

 

[dhookom]

Have you stepped through the configuration of the DSNs? This provides the option to test the connection when finishing. You should have at least the db_datareader role. If you want to be able to edit records, you will need db_datawriter also.

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

Duane,
I have logged in to the server as the admin. I went into SQL Server Management Studio >> Security>> Logins There are two logins: my specific user account and a generic login account. Under both accounts If I right click and go to properties if I click on the individual databases the database role membership the following roles are checked: db_datareader,db_datawriter,db_ddladmin,db_owner,db_securityadmin and public.


When I go to the ODBC Data Source Administrator: If I click on the System DSN tab all five databases I am interested in are there. I clicked configure the name of the database is in name, the server is correct. I have SQL authentication checked. I enter the password. Change default database is the named database. When I click on the Test Data Source I get the TESTS COMPLETED SUCCESSFULLY message. I did these steps for all five databases and I got the TESTS COMPLETED SUCCESSFULLY message.

I have copied the databases to the C: drive of the server. If I right click on a database and go to properties and go to the security tab there are 4 user names: SYSTEM, my name at the local domain, administrators and users. All four users have full control,modify,read & execute, read and write checked.

 

[dhookom]

Make sure the default database is set within SQL Server Mgmt studio.

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I verified that on all five databases that I need access to. The default database is the same as the name of the database

 

[dhookom]

What happens when you attempt to create a pass-through query to a table in one of the data sources?

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I don't know what a pass through query is?

 

[dhookom]

When creating a query, you can select Pass-Through as the query type. This allows you to set the ODBC Connect Str property to your DSN. Then simply type in a select statement like:

SELECT TOP 10 *
FROM MyUnseenTableName

Run the query to see if it works or if you get an error message.

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

When I tried to create the pass through query
I got an error message ODBC--call failed.
Microsoft ODBC SQL driver [SQL Server] invalid object name dbo_dic_Company. #208

SELECT  Top 10 *
FROM dbo_dic_Company;

 

[dhookom]

The syntax in a pass-through query is the same as would be used in the SQL Server Management studio. Use the actual table name without the "dbo_" which is added by Access.

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I took the dbo out and ran the following query

SELECT top 10*
FROM dic_Company;

When I try to execute the query a select data source box comes up and I click on Machine data Source.
Than I click on the database that has the table based on the query. I get the following error:
ODBC-call failed
[Microsoft]{ODBC SQL driver][SQL Server] Invalid object name 'dic_Company'. (#208)

 

[dhookom]

What happens if you include the fully qualified table like:

SELECT top 10 *
FROM YourDatabaseName.dbo.dic_Company

Is "dic" a schema or part of the actual table name?

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I finally got it to work!

I had to change the ODBC Connection string on the Property Sheet.

SELECT top 10*
FROM rptdata_ahs.dbo.dic_Company;

Now that I got this to work. Does this mean I will have to rewrite any code referring to this table as a pass through query?

 

[dhookom]

The P-T query was just a test to confirm you have permission to get to the table(s). If you aren't editing any records then P-T queries are a good alternative. I'm not sure if we have resolved the issue with connecting to the appropriate tables in the database.

I typically use DSN-less connections as outlined at Doug Steele's web page.

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

Well, we haven't really resolved the issue. I know we have been going back and forth all day. I went to Doug Steele's website and copied his code into a module. So Do i just call the module at the beginning of my procedure?

 

[vba317]

When I tried to compile the new module I got a compile error. Cannot define a public user defined type within an object module.

Option Compare Database

Type TableDetails
    TableName As String
    SourceTableName As String
    Attributes As Long
    IndexSQL As String
    Description As Variant
End Type

Any ideas?

 

[dhookom]

Did you attempt to create a "module" or "class Module"? Should be "module".

What version of Access?

Duane
Hook'D on Access
MS Access MVP