SQL Server 2008 read-only access causes error in Microsoft Access 2007

Asked By 10 points N/A Posted on -
qa-featured

Hi.

A user has to perform on an SQL Server 2008 a "SELECT from tables" ( no update, insert or delete) command. 

She is working with the data with an Access 2007 front end.  She created access, created a blank database, clicked External Data, clicked ODBC database, then created an ODBC connection using SQL Server Native Client 10.0.  She also got a successful message after clicking the Test Connection.  Her SQL server account is appointed to her Windows login. Logging into her SQL Server is done with Windows authentication. The DSN (SQL Server Native Client 10.0) was set up with Integrated Windows authentication.

After that, when she tried to connect to one of the SQL Server tables, she received this error:

"The

Microsoft Access database engine could not find the object <name>. Make sure the object exists

And that you spell its name and the path name correctly. (Error 3011)."

I've done SELECT, UPDATE, INSERT, and DELETE queries on my own SQL server table, and it perfectly works.

She also tried the same setup on several SQL Server ODBC drivers: SQL Native Client, SQL Server, and SQL Server Native Client 10.0.  Still it didn't work.  On the other hand, she made a successful import from Excel using her read-only login.  After that, I assumed that there is a bug in Access driver that caused this error.  Or perhaps there is an undocumented reason that’s why Access 2007 won’t allow me link or copy the read-only tables from SQL Server 2008.

This is only specific to this user.  We all are using Windows 7 Enterprise along with Access 2007.  One of us is allowed with read-only ability, who can duplicate the problem on the SQL Server 2008 tables.  Also another one is allowed with full readwrite ability who can do the process without the error message above.

That's why we're thankful that Excel works like a charm; we still can import work.  When I tried to observe some control over the data on the back end, the user is forced to use Access (not Excel) from front end.

I need a solution.  Thanks in advance.

  • Answer Accepted: No
  • Question Category:
SHARE
Answered By 0 points N/A #96489

SQL Server 2008 read-only access causes error in Microsoft Access 2007

qa-featured

Hello!

I have two possible solutions to this problem. Please try out any of them and give me your feedback.

Solution 1

The jet provider (Microsoft.Jet.OLEDB.4.0) will not work on a X64 bit platform. To get around this problem, you need to access and install the x64 ACE driver from Microsoft by following this link:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

 

Before performing the installation, it is advisable to remove all 32-bit Microsoft Office applications such as Office 10 32-bit, Access 2007 install and others including their instances. This step is mandatory for you to install the Microsoft Database Engine 2010 redistributable components successfully.

The links provided above will enable you to access and install other engines that will support you in setting up linked servers and other files.

Run the following script on your SQL server. It will set all the parameters required to access and run queries related to components. Address ‘null

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’DynamicParameters’, 1
GO

After running the script, you need to put aside calls made using old jet parameters when running OPENROWSET calls.

For instance, when running a ‘select * from North…customers’ statement, remember to replace 'Microsoft.Jet.OLEDB.4.0' with 'Microsoft.Jet.OLEDB.12.0'

Finally, reinstall all your office applications. You can install 64-bit office 10 but remember that not every third party software interfaces with it.

Solution 2

The second alternative may also fix the problem but with a higher maintenance cost.

It involves running a 64-bit SQL server and SQL Express side by side. The SQL Express acts as a bridge while the SQL server provides the main server instance.

Whenever you need to use 32-bit providers, configure a link server on the SQL Express instance and link it to the main instance.

The linked server pathway would be SQL Server 64 bit -> SQL Server 32 bit (over 64 bit client) -> (32 bit client) for linked server X.

This may resolve the issue. However, you must be good at configuring link servers.

I hope these suggestions will be helpful.

About aorogo
Questions
0
Answers
1
Best Answers
0
  • Posted on -
  • Question Category:

Linking from 1 sheet to another

Previous

MS Access having error when creating web form

Next

Related Questions