.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Asad Ali
Post New Web Links

How to make linked server to Access 2007 db on 64-bit server

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server
 

It seems that Microsoft created ____ for a lot of people trying to migrate applications to 64-bit platforms that use an Access database.  My situation:  need to create linked server in SQL Server Express 2008 R2 to an Access 2007 database.  The provider "Microsoft.ACE.OLEDB.12.0 " might work but is not listed as one of the available providers in the linked servers properties dialog.  I have office 2007 installed on the machine, so I can't install the 2010 access database engine.

Can I somehow make the Microsoft.ACE.OLEDB.12.0 provider avialable on my system in this circumstance?




View Complete Post


More Related Resource Links

Linked Server to access Excel 2007

  

Hi

I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.


Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
go
SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]')
Exec sp_dropserver 'AB2','droplogins'


Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges

GRANT ALTER ANY LOGIN TO sqlaccount
GRANT ALTER ANY LINKED SERVER TO sqlaccount


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE

The DisAllowAdHocProcess in

Linked Server to access Excel 2007

  
Hi I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0. Apparently the query requires the sql account to have SYSADMIN privileges. Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method. Following is my code. Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1' Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL go SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]') Exec sp_dropserver 'AB2','droplogins' Now i get the following error Error.15247-User does not have permission to perform this action If I execute the query from Query Analyzer it works fine, but fails when I execute it using Windows App and encapsulate code in Stored Proc. My Excel file, Database and Windows Application run on separate machines. i have provided the following privileges GRANT ALTER ANY LOGIN TO sqlaccount GRANT ALTER ANY LINKED SERVER TO sqlaccount EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE The DisAllowAdHocProcess in Registry has value 0 Please let me know what additional permissions should i set to get it working???

Trying to add a linked server - SQL Server 2008 Express Edition to Access 2007

  
OS: Vista SP1
SQL Server 2008 Express Edition installed locally
Office 2007 installed locally

I'm trying to add a linked server to a named instance of the SQL Server 2008 that links to an Access 2007 database.

I've spent a couple of hours trying this now - many, many different ways.

Always seem to end up back at Error 7303 - http://msdn.microsoft.com/en-us/library/aa226395(SQL.80).aspx

I've tried all versions of Access database file 2000, 2002-2003, 2007
I've tried to use different drivers -

Microsoft.Jet.OLEDB.4.0
ODBC
Office 12 for Access driver

I've tried to use the upsizing wizard from Access - no good, cannot get it to connect to the SQL Server 2008 Express Edition instance

Whichever way I try it I end up with this message/error:

the upsizing wizard only works with microsoft sql server versions 6.50 sp5 or higher

I've read: http://support.microsoft.com/default.aspx/kb/838594 and followed it - same error.

I really want this to just work. Like it should already.

Any suggestions appreciated.

Matt Haddon

Linked Server From SQL 2008 R2 (32 or 64 bit) TO Access 2007 mdb

  

I have what I thought was a simple task.  Create an OLEDB connection to an Access2007 MDB data file within SQL Server 2008 R2.  Here's my environment:

    Windows 7 64-bit OS
    SQL Server 2008 R2 Standard 64-bit
    Office 2010 32-bit (installed 32-bit because all legacy data needed to be ported)

My first attempt was within SQL Server's Management Studio by navigating to Server Objects - Linked Servers and right-click selecting New Linked Server...
First indication of some trouble began when I didn't see an ODBC Provider for my Access database.  After trying a few options, I turned to Google expecting a quick resolution.  Here is the beginning of 5 days of going round in circles!!!

Eventually I found some links that led me to potential solutions:
http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/589ca193-3541-4c5e-965c-4c515d6b476b
http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/c371fb35-6bdd-42e6-b88e-d1c0003d23f6
http://social.msdn.microso

Administrator and Developer Guide to Code Access Security in SharePoint Server 2007

  
Explore configuration options, get best practices for managing CAS in SharePoint environments, and walk through a complex CAS scenario.

Linked server to access 2010

  
I keep getting an error when setting up a linked server in sql 2008 R2 x64 to a microsoft access database file (mdb or accdb file).  (The problem occurs on computers with either the 32-bit or 64-bit version of ms office installed).  What are the proper connection string settings?  (This is after right clicking linked servers in ssms and selecting "add new linked server")  The below settings don't work: Provider (from dropdown): Microsoft Office 12.0 Access Database ENgine OLE DB Provider Product name: Access Data source: C:\foopath\foo.mdb  --also doesn't work for foo.accdb Provider string: Microsoft.ACE.OLEDB.12.0 An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER" returned message "Could not find installable ISAM.". (Microsoft SQL Server, Error: 7303) I got some exotic errors when following the directions in Books Online (under sp_addlinkedserver). For example: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apa

Problem connecting to SQL Server 2008 R2 Express from Access 2007

  
I have a problem with SQL Server 2008 R2 Express with MS Access 2007. I am running Windows 7 with 8 gigs memory. I installed SQL Server 2008 R2 Express. Do I now need to install SP1 because I am running with Windows 7? I can open SQL Server using the Management Screen but I cannot connect to MS Access 2007 using SQL Server Express as the database. When trying to connect to SQL Server from Access 2007, I keep getting the message that SQL Server 2005 cannot be found. It will not allow me to sign onto sql server 2008 stating that it cannot be found. Do I have to now run the SQL Server 2008 R2 Express SP1? Is there an upgrade to MS Access 2007 which will allow me to use SQL Server 2008 R2 Express?

Problem with ODBC from MS Access 2007 to MS Sql Server 2008

  
Hi experts, I need some help with ODBC Connection Settings from MS Access 2007 to Sql Server 2008. Here is the Scenario, I have created a Database(StagingDB) with 5 tables on SQlServer 2008 and I have  Developed an MS Access 2007 Front End using Linked tables and ODBC to Insert/ Update data into these 5 tables. This is just working fine in my Dev database. ODBC Connection Settings are pointing to my DevServer. All I wanted to do now is, I want to point the ODBC Settings to the same database on my Test Server and then to Production Server. I have Changed the Server name in the ODBC File to point it to my Test Server and If I try to Insert  some data into test server its stil going into the database on Dev server. Is this the right way to do. Please help me with this. thanks In Advance..

Linked Server To MS Access using Jet Returns "unspecified error"

  
I'm have SQL SERVER 2008 on Win2008 Server. I have several linked servers to Oracle (odbc) and have just created Linked Servers to MS access 2003 databases on network shares. I can 'test connection' and select data from the linked ms access servers using 4 part naming no problem. As soon as I do this, the Oracle db links stop working. I reboot the server, test connection to Oracle dbs , all ok there, run test connection to ms access dbs and receive the following;Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYAccessLink"".OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYAccessLink" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303) Reboot the server, test connection to ms access db's, all ok, then test connection to oracle, and receive the following;A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.) (Microsoft SQL Server, Error: 109) Reboot server, perform test again, toggling the order and I get the same results.Have tried opening ms access via openrowset and "execute ('select count(*) from SomeTable') at MYAccessLink" to rule out test connection (sp_testlinkedserver) doing something to crash the links. I have checke

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access

  
Hi,I am importing Excel 2007 file into SqlServer 2005 (Sqlexpress). The office 2007 is not installed in the machine. I have downloaded and installed "AccessDatabaseEngine". When I execute following statement SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\File1.xlsx', 'SELECT * FROM [Sheet1$]');The following error is comming:Msg 7399, Level 16, State 1, Line 1The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.Msg 7350, Level 16, State 2, Line 1Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".Can any one suggest what should I do to resolve?Thanks,Parmeshthee. Learning .Net 3.5

Upsizing Database from Access 2007 to SQL Server Express 2008

  
I have recently downloaded and installed SQL Server Express 2008 on to my Windows XP SP3 machine, I have a large MS Access 2007 database (.accdb) I wish to export out to SQL Server. I am using the 'upsizing wizard' that is found on the 'Database Tools' tab of Access 2007. Clicking on the 'Database Tools' tab and then on the 'SQL Server' button starts the Upsizing wizard. On the first page of which I select the 'Create new database' option, the next page asks 'What SQL Server would you like to use for this database?' it already has '(local)' entered into the text box, so I leave that unchanged, I tick the trusted connection box and name the new SQL Server database and then click 'Next'. But I get the following error (it appears in a Windows information message box):

Title: Microsoft SQL Server Login
Message Text:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen(Connect()).
Connection failed:
SQL State: '08001'
SQL Server Error: 17
[Microsft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

PS. I have successfully used the upsizing wizard to export the same Access database into SQL Server Express 2005 on another machine.

What am I doing wrong this time?

Access 2007 upsizing wizard can't connect to SQL Server 2005 Express

  
Hi,

When trying to use the Access 2007 upsizing wizard to move a database to SQL Server 2005 Express, I get the following error message:

Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQ Server Driver][Share
Memory][ConnectionOpen(Connect()).
Connection failed:
SQLState:'08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server
does not exist or access denied.

I get the same message regardless of whether I configure SQL Server to run as a local system or service.

I am the only user on my Vista 32 PC and am therefore the administrator. As far as I can tell I have sufficient authority since I can start and stop SQL Server.

As a newbie to all this, I can't find any step by step instructions as to how a local copy of SQL Server should be configured to work with the upsizing wizard. In fact I can't even find any Access 2007 help walk-throughs on this upsizing wizard, let alone troubleshooting its error messages.

Any help would be appreciated, as well as links to step by step instructions (which I can't find in the MSDN Library materials).

Thanks!
Richard
PS Does MS understand how inadequate and inefficient its built-in documentation really is? Maybe that's what keeps MS Press in business!

Linked Server Mapping Error "Access to the remote server is denied because no login-mapping exists"

  

SQL Server 2005 SP3 32bit server
Connecting to an Oracle 10g server
Loaded OracleClient 10g

I'm able to ping the Oracle Server, and ODBC connect and able to run an open query with a single remote user id.

Issue: When I use the security context "Be made using this security context" it connects.
When I choose the "Not Be Made" WITH THE SAME USER ID & PASSWORD, I get the mapping error.


Linked Server to Password Protected Access Database

  

I want to create linked server in SQL 2005 with Ms Access 2003 Database. I have used following commands to create linked server.

EXEC sp_addlinkedserver
@server = N'TestLinkServer',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'Access',
@datasrc = N'D:\Test.mdb',
@provstr = Null

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'TestLinkServer',
@useself=N'False',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL

With above command linked server successfully create and can run SQL query against linked server. But if i set Database password to MS Access 2003 Database, then it gives following error message.

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkServer" reported an error. Authentication failed.

Any ideas?

Thanks,
Rajesh


Linked Server Error 7303 for Access 2003 Database

  
I am having difficulty creating a linked server to and Access 2003 database on my SQL 2005 server.  Whenever I create a linked server through the either the “New Linked Server” tool in the SQL Management Studio or using the sp_addlinkedserver method, receive and error indicating “Cannot initialize the data source object of the OLE DB provider…(Microsoft SQL Server Error: 7303)”.  Although my ultimate goal is to link to a secure Access 2003 database file, the current MDB file I am trying to link to is “unsecured”.  I have already verified that the SQL Service account has read and write access to the folder containing the MDB file as well as the temp directories on the SQL server (suggestions from other forums).  Is there any SQL Server configuration that I need to modify in order to use the Jet 4.0 linked server provider (Microsoft.Jet.OLEDB.4.0)?

How to check (login Credentials) user name and password to access sharepoint server 2007 programmati

  
my issue is...

while user entered his/her Username:xyz
                                      password:abcd
i need to check programmatically either username & password exists ot not in sharepoint server 2007 from windows form application.

and i know how to check username,but i don't know how to check password.

you can check username in below ways..

 SPSite SharepointSiteObj = new SPSite(SiteUrl);
 SPWeb SharepointWebObj = SharepointSiteObj.OpenWeb("/");

 SPUserToken token=SharepointWebObj.AllUsers[userName]

from this i can get user name..

BUT I DON'T KNOW HOW TO CHECK USER PASSWORD....

PLEASE GIVE YOUR VALUBLE SUGGESTIONS AND SOME COADING..

THANKS FOR ANY HELP:
jilani

Linked server access issue when hosting application in IIS

  

Hi,

I have two instance of database and connected the other with linked server. When i run the application locally its working fine. It gets valur from linked server tables. After hosting the application in IIS im getting following eror.

The OLE DB provider "SQLNCLI" for linked server "LINK" does not contain the table ""90"."dbo"."table"". The table either does not exist or the current user does not have permissions on that table.

 

Can anyone specify what user should be mapped to solve this issue?


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend