Home | Office Dev Center | Office, SharePoint, Apps for Office and SharePoint, Office 365 APIs

Office 365 APIs Preview

Office 365 APIs extend the Office 2013 platform through REST APIs and standard OAuth flows so that websites and native applications running on Windows 8, iOS, Android, and other device platforms can consume Office 365 data.

Office 365 APIs Preview graphic

It's much simpler to write code for the Office apps store because we get to use the skills we already have being web native developers. And that means we can reach every version of Office from here on out with the same code that we already write for all major web browers.

- Poll Everywhere, Office 365 Partner

Apps for Office

An app for Office is a webpage you build and host inside an Office client application to extend what your document, spreadsheet, or email message can do.

Run them in:

  • Office desktop clients
  • Office Online
  • Mobile browsers

Apps for SharePoint

Apps for SharePoint are easy-to-use, lightweight web applications that extend what your SharePoint website can do.

Use them as:

  • Immersive full-page experiences
  • Parts of a web page
  • UI custom actions

Opportunity

Reach 1 billion + worldwide Office
users.

Learn more Right arrow in circle

Transform

Use skills you know and familiar tools
to transform your solutions.

Get started Right arrow in circle

Support

Talk to other devs, tell your app story,
and get help.

Get connected Right arrow in circle

 none
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

    السؤال

  • Hi all,

    I'm trying to change a little my Sharepoint page by changing default.master page. I want to retrive a piece of data from my Access 2003 database.

    So, the code I use in the page:

    <asp:sqldatasource
        id="SqlDataSource1"
        runat="server"
        datasourcemode="DataSet"
        ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;User Id=Admin;Password=;"
        ProviderName="System.Data.OleDb"
        selectcommand="SELECT BranchID,BranchName FROM tBranches_dic">
    </asp:sqldatasource>
    
    
    

    But after masterpage uploading I receive an error:

    The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

    All the 'googling' I've already made refers to x64 vs x86 issues. But I'm using WinXP Professional (100% 32bit). And this OleDB provider is absolutely installed on this machine. Because I can see it while creating an .udl file. And this code in MS Access 2003 works fine too:

    Public Function test()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
      Set cn = New ADODB.Connection
      cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;User Id=Admin;Password=;"
      cn.Open
      Set rs = New ADODB.Recordset
      rs.Open "SELECT * FROM tBranches_dic", cn, adOpenStatic, adLockReadOnly
      rs.MoveLast
      test = rs.RecordCount
      rs.Close
      cn.Close
      Set rs = Nothing
      Set cn = Nothing
    End Function
    
    
    
    ? test
     19 
    

    Could anybody help or move this thread to the correct forum if this one isn't appropriate.


    Andrey V Artemyev | Saint-Petersburg, Russia
    03/محرم/1432 10:34 ص

جميع الردود

  • If it is helpful, I get 'Could not find installable ISAM' error trying to open ADODB.Connection from MS Excel via OLEDB provider (using the same database as a datasource).
    Andrey V Artemyev | Saint-Petersburg, Russia
    03/محرم/1432 12:59 م
  • Hello Andrey.

    The "local machine" is the one the error message is generated on. I am quite sure that it's not your developer computer with Windows XP but a Windows Server runing IIS and some Sharepoint website, most likely running a 64-bit operating system. You said that you "uploadad" something, didn't you? So it is very likely that there is no JET OLEDB provider istalled on the sharepoint server. If the OS on the server was 64-bit, you might run into trouble trying to find such provider software, because - as far as I know - there isn't any in 64-bit.
    So why not use a SQL database instead?

    --
    Good luck and kind regards,
    Wolfgang

    03/محرم/1432 04:25 م
  • Andrey,

    I think Wolfgang has nailed this one. You might be trying to reach "a bridge too far" here.


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    03/محرم/1432 04:37 م
  • Thanks Wolfgang! I'll try to ask our Sharepoint admin.

    About SQL Server. I have one test db on the SQL Server but all my users are still using .mdb backend. So, I want to get actual data. BUT! I've already tried to connect to my test SQL Server db. There were also some errors about 'Database was not found' etc. I'll try to reproduce this behaviour tomorrow and share the real error message. I know there is now nothing related to MS Access but many thanks for your future help!

     

    Mark,

    yes, I know. All these workarounds in terms of many restrictions within our company's policies seem to be a very long journey to the Neverland. But I'm still trying to do smth. :)


    Andrey V Artemyev | Saint-Petersburg, Russia
    03/محرم/1432 05:53 م
  • Wolfgang, some other info.

    If I try to use ODBC I get an error:

    ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    ConnectionString="Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\test.mdb;Uid=Admin;Pwd=;"
    ProviderName="System.Data.Odbc"
    

    If we go with SQL Server, I try ODBC:

    ConnectionString="DRIVER=SQL Server;UID=******;DATABASE=****;SERVER=*******"
    ProviderName="System.Data.Odbc"
    

    And I recive the following error:

    ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
    ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

    If I specify the Trusted Connection or Integrated Security it says me that these options aren't allowed at the server (I mean IIS server but not my SQL one).

    And the last attempt:

    ConnectionString="Data Source=*********;UID=******;Initial Catalog=****"
    ProviderName="System.Data.SqlClient"
    

    The error is:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

    I'm not good at such a low level (drivers, providers etc.) but have never faced problems with connection strings. This time I'm just want to give it all up but am still hoping that you can help.


    Andrey V Artemyev | Saint-Petersburg, Russia
    04/محرم/1432 09:06 ص
  • I've just found this row in the Stack Trace:

    System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)

    Does it exactly mean that the server machine is running under the x64 OS?


    Andrey V Artemyev | Saint-Petersburg, Russia
    04/محرم/1432 11:02 ص
  • You should check in you Window folder for a file call msjet40.dll

     

    c:\windows\system32\msjet40.dll

     

    Maybe a different version of the dll is installed on the PC.


    jdweng
    04/محرم/1432 03:14 م
  • Andrey,

    It sure does seem to be a hint that way, doesn't it?

    Is Sharepoint the source of the call out to connect to to the SQL Server?
    Here's a question for you - is it a 64-bit SQL Server Host OS/SQL Server instance itself you are trying to reach, or a 32-bit SQL Server/host OS that Sharepoint is trying to call out to? ...and could that be an issue (whether it is the case or not)?


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    04/محرم/1432 03:35 م
  • Hello Andrey.

    Sorry, I haven't much time now, so just a few remarks (You are still using ADODB (that is COM), aren't you? ):

    Your connections using ODBC:
    The driver name should be in curly brackets ({SQL Server}) and the provider for ODBC is "MSDASQL" which is the default value unless overwritten in the connection string. ((System.Data.Odbc and System.Data.SqlClient, without quotation marks, are .Net stuff). I think that trying the MS Access driver on the sharepoint server can't be successful unless your admins installed client software on the server.
    Don't use ODBC, use OLEDB instead:

    ConnectionString = "Provider=SQLOLEDB.1;Data Source=SQLSERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI"

    Replace SQLSERVERNAME by the server name of your SQL Server, if it is a named instance, also supply the instance name in the form
    Data Source=SQLSERVERNAME\INSTANCENAME.

    Replace DATABASENAME by the actual name of the database you want to work with.

    Don't forget the blanks.

    Don't try to connect to the IIS Server unless it's your SQL Server.

    As for the Int64: no, I don't think so. Better ask your admin about the OS on the webserver.

    --
    Kind regards and good lock (have to hurry home now),
    Wolfgang

     

     

    04/محرم/1432 04:15 م
  • Joel,

    can't access my local drive - have no rights. The only way is to copy this file via .bat file (or Shell within VBA) and see whether there is an error or a dll file is copied with success.

    Mark,

    can't 'connect' to our admin this time. :(

    Wolfgang,

    thanks for you tips. The result is the following one:

    If I use

    ConnectionString = "Provider=SQLOLEDB.1;Data Source=SQLSERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI"
    

    he gives me an error about Integrated security

    This control doesn't allow connection strings with following reserved words: Integrated Security, Trusted_Connection.

    *This is my own translation because this error is shown in Russian.

    If I add my UserName (UID=mylogin) the error is:

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    If I add my password (PWD=mypass):

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    If I write no info about security properties ("Provider=SQLOLEDB.1;Data Source=SQLSERVERNAME;Initial Catalog=DATABASENAME;)

    Invalid authorization specification

    %)


    Andrey V Artemyev | Saint-Petersburg, Russia
    07/محرم/1432 10:58 ص
  • @Joel: The problem isn't the local machine, it's the sharepoint server.

    Hello Andrey.

    So no integratet security is allowed, that must be a problem of the sharepoint server as well. To nevertheless connect to a SQL server, it (the SQL Server) must run in mixed security mode and you will have to connect using a SQL login. Ask your SQL admins if that is possible and if so to create such a SQL login that has the rights to connect to your database with sufficient permissions.

    Then, to finally use the correct connection string, create it using a UDL file. For this, if you have never done this, create a blank textfile and rename it with a filename extension of udl. Now, he file should be shown with data data related icon. Double-click this file and conect to your SQL Server using the credentials your admins have provided. When the conneciton was successful, close the file and open it in notepad (for example rename it back to have an extension of txt). You will find the correct connection string in there, and it should contain "User ID" and "Password" instead of UID and PWD like this:

    Provider=SQLOLEDB.1;Password=MyPassword;Persist Security Info=True;User ID=MyUser;Initial Catalog=DATABASENAME;Data Source=SQLSERVERNAME

    One risk in doing it this way is that username and password are part of the conneciton string, so be sure that no one but you can access the code.

    --
    Regards,
    Wolfgang

    07/محرم/1432 02:13 م
  • Mark,

    yes, the source of this call to SQL Server is SharePoing site. As always, due to restrictions and no access to the full functionality, I try to change the code of masterpage (smth like a template for all site pages). 'My' SQL Server is running under 32-bit OS. I've already tried to ask about SharePoint server itself (32 or 64) but its admin don't want to answer definetely this question. :)

    Wolfgang,

    thank for explanation. Yes, I know about .udl files, I understand what you mean. I'm negotiating with our SQL Server admin about mixed security mode. I'll continue right after getting an answer from him.


    Andrey V Artemyev | Saint-Petersburg, Russia
    10/محرم/1432 07:56 ص
  • Ok, I have an answer from admin.

    Yes, mixed mode is on and now I have a test user. Connection string is right and Test Connection in .udl works well.

    	ConnectionString="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=*******;Password=********;Initial 
    Catalog=mydb_name;Data Source=myserver_name"
    	ProviderName=System.Data.OleDb
    

    But an old well-known error appears:

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    It's now interesting for me whether it's possible to deny any connections somehow? Is this error due to SQL Server where some types of connections aren't allowed or smth else?


    Andrey V Artemyev | Saint-Petersburg, Russia
    10/محرم/1432 08:35 ص
  • Hello Andey.

    Since the Provider is included in your ConnectionString, setting the ProviderName is unnecessary.
    I wonder how the password came into your connection string when you did not check the "Persist Security Info" box.

    I found  KB article 315159 which might solve the problem, so try to add "Network Library=dbmssocn;"into your connection string,. This will enforce to use TCP/IP instead of named pipes when connecting to SQL Server from your SharePoint server, which probably runs your code under the ASPNETt account.

    --
    Kind regards,
    Wolfgang

    10/محرم/1432 10:14 م
  • Hello Wolfgang,

    If I remove  ProviderName=System.Data.OleDb, I have an error

    Keyword not supported: 'provider'.

    So, I can leave defining ProviderName in the code. BTW, it's no difference between using " around the name of the provider and not. Both ways run well.

    Yes, it's just a kind of typo. After making .udl file I didn't noticed that password removed from the 'Notepad' view and Persist Security Info had changed to False. Now, I've corrected it but no difference, the same error.(

    Thanks for the article, it seems to be the real reason for this error. I've added 'Network Library' option to my connection string, but have still the same error. I even tried to use server's IP address instead of its name. .udl still works fine with all these kinds of connstrings.

    However, Wolfgang I realy appreciate your help and the time you are spending.


    Andrey V Artemyev | Saint-Petersburg, Russia
    11/محرم/1432 07:37 ص
  • Hello Andrey.

    Oh, I see. You specified the attributes of the sqldatasource on your asp.net page. But tell me: What is causing the error - the sqldatasource or your test procedure? They probably use completely different connection strings. Your "<asp:...> element uses .Net and your test-procedure uses ADODB, which is a COM component. You should add the "Network Library" setting to the connectin string that causes the error.

    What is the connection used for? A control (like gridview) or just code that processes the records?

    One thing you also should check is whether the webserver can connect to the sql server. Ask your admin if you can test this using a udl file on the desktop of the webserver. If this fails, it is probably a network problem, maybe caused by a firewall (if the webserver sits in a DMZ).

    --
    Kind regards,
    Wolfgang

    11/محرم/1432 12:03 م
  • Wolfgang,

    I suppose there is some misunderstanding. The ADODB code in the main post was just an example that I CAN connect to specified db using OleDb provider. I don't use it anymore. Now we talk only about my SharePoint site (.aspx page).

    The connection is used for further GridView actions. As far as I have no any designer software (VS or Sharepoint Designer) I can only edit the source of a page but can't u. I download this page, make changes via Notepad and upload this page back to the SharePoint site main pages collection. The other part of my code is:

    <asp:GridView ID="GridView1" Runat="server" 
         DataSourceID="SqlDataSource1"
          DataKeyNames="BranchID" AutoGenerateColumns="False">
          <Columns>
            <asp:BoundField ReadOnly="True" 
             HeaderText="Branch code" InsertVisible="False" 
             DataField="BranchID"
              SortExpression="BranchID"></asp:BoundField>
            <asp:BoundField HeaderText="Branch Name" 
             DataField="BranchName" SortExpression="BranchName">
            </asp:BoundField>
          </Columns>
        </asp:GridView>
    
    Ok, thanks, I understand you and will try to continue my conversation with SS admin.
    Andrey V Artemyev | Saint-Petersburg, Russia
    11/محرم/1432 12:37 م
  • Hello Andrey.

    I have a Visual Studio and I created a simple sqlconnection graphically. From what I see there, your code should be as follows:

    ConnectionString = "Data Source=myserver_name;Initial Catalog=mydb_name;Persist Security Info=True;Network Library=dbmssocn;User ID=*******;Password=*******;"
    ProviderName="System.Data.SqlClient"
    
    --
    Kind regards,
    Wolfgang
    11/محرم/1432 02:09 م
  • Hello Wolfgang,

    I've already tried SqlClient provider but with another connection string. Now I have the same SqlException:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

    I've already asked our SS admin about "that SQL Server is configured to allow remote connections". And he said "yes". It seems we go circle by cirlce around this problem. :)


    Andrey V Artemyev | Saint-Petersburg, Russia
    11/محرم/1432 02:27 م
  • Hallo Andrey.

    The next step should be testing a connection from the SharePoint server (maybe using a udl file). If remote connections had been disallowed on your SQL server, you would have failed to connect via udl file from your developer PC, too.

    --
    Kind rehards,
    Wolfgang

    11/محرم/1432 03:14 م
  • Hello Wolfgang,

    I have one proposition. I know that only those users who are the members of the particular Active Directory group can connect to this SQL Server. If we talk about mixed mode, should the SharePoint Server be in this group too? Or specifing User ID and Password should be enough to connect to the database from anywhere?


    Andrey V Artemyev | Saint-Petersburg, Russia
    14/محرم/1432 07:37 ص
  • Dear Andrey.

    As you stated before, using inegrated security in yur connection string is not allowed, so you won't be able to us an AD user or group to access the SQL Server database from within your ASP.Net page. that's why I proposed to use mixed mode security. The User ID and Password provided in the connection string will have to be those of a SQL Login, which can indeed be used to connect from "anywhere" (unless a firewall blocks the network traffic).
    If that's an issue, you might talk to your sharepoint admin to change the configuration to use Windows authentication. If doing so, he might also check the impersonation setting in the web.config file, which, if set to "true", should allow to control SQL Server access using Windows Logins, which would make the mixed security mode unnecessary. Since I'm not a SharePoint expert, I can't tell where and how to configure it's settings.

    Have you already tested a "udl-connection" from the SharePoint server?

    --
    Kind regards,
    Wolfgang

    14/محرم/1432 08:29 ص
  • Wolfgang,

    ok, I understand now. No, I haven't tried it and I'm afraid I won't be able to do it at all. SP Admin doesn't want to cooperate.

    I've just tried to run the .udl from another client workstation. This user is situated in another town and isn't in this AD group and connection test was ended with success using our connection string with my test User ID and Pass. So, as I understand, the only last possibility is to run .udl from SP Server. I'll think how to make it real.


    Andrey V Artemyev | Saint-Petersburg, Russia
    14/محرم/1432 08:49 ص
Home | Office Dev Center | Office, SharePoint, Apps for Office and SharePoint, Office 365 APIs

Office 365 APIs Preview

Office 365 APIs extend the Office 2013 platform through REST APIs and standard OAuth flows so that websites and native applications running on Windows 8, iOS, Android, and other device platforms can consume Office 365 data.

Office 365 APIs Preview graphic

It's much simpler to write code for the Office apps store because we get to use the skills we already have being web native developers. And that means we can reach every version of Office from here on out with the same code that we already write for all major web browers.

- Poll Everywhere, Office 365 Partner

Apps for Office

An app for Office is a webpage you build and host inside an Office client application to extend what your document, spreadsheet, or email message can do.

Run them in:

  • Office desktop clients
  • Office Online
  • Mobile browsers

Apps for SharePoint

Apps for SharePoint are easy-to-use, lightweight web applications that extend what your SharePoint website can do.

Use them as:

  • Immersive full-page experiences
  • Parts of a web page
  • UI custom actions

Opportunity

Reach 1 billion + worldwide Office
users.

Learn more Right arrow in circle

Transform

Use skills you know and familiar tools
to transform your solutions.

Get started Right arrow in circle

Support

Talk to other devs, tell your app story,
and get help.

Get connected Right arrow in circle