none
Microsoft.Office.Interop.Access.Dao.dll not working on Windows 10 machine RRS feed

  • Question

  • Hello,

    I have one VB.net application which use Microsoft.Office.Interop.Access.Dao.dll to fetch data from ms sql server and insert in ms access database, this application work perfectly on windows 7 machine, but when i try to run on Windows 10 machine its giving me following error

    i have change target platform of build from "any CPU" to "x86", still its not working, anyone have solution for this problem?

    Thursday, January 17, 2019 1:18 PM

All replies

  • Hello,

    Don't use DAO on Windows 10, instead use OleDb data provider. I don't currently have a VB.NET solution but you could look at my C# solution to get an idea who to do this. At the core of the code I use the following which is taken from my MSDN code sample.

    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database={pFileName}

    The C# code (not all here but in the code sample)

    public bool ExportAllCustomersToAccess(string pFileName, string pCountry, ref int pRowsExported) 
    { 
        string fields = "CompanyName,ContactName,ContactTitle,Phone"; 
     
        using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString }) 
        { 
            using (SqlCommand cmd = new SqlCommand { Connection = cn }) 
            { 
     
                /* 
                    * If using .mdb use Microsoft.Jet.OLEDB.4.0 rather than Microsoft.ACE.OLEDB.12.0 as the provider name 
                    */ 
                cmd.CommandText = $"INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','{pFileName}';'Admin';''," + 
                                    $"'SELECT {fields} FROM Customers') " + 
                                    $" SELECT {fields}  " + 
                                    " FROM Customers"; 
     
                if (pCountry != "*") 
                { 
                    cmd.CommandText = cmd.CommandText + " WHERE Country = @Country"; 
                    cmd.Parameters.AddWithValue("@Country", pCountry); 
                } 
     
                try 
                { 
                    cn.Open(); 
                    pRowsExported = cmd.ExecuteNonQuery(); 
     
                    return pRowsExported > 0; 
     
                } 
                catch (Exception e) 
                { 
                    mHasException = true; 
                    mLastException = e; 
                } 
            } 
        } 
     
        return IsSuccessFul; 
    } 

    In VB.NET

    Public Function ExportAllCustomersToAccess(ByVal pFileName As String, ByVal pCountry As String, ByRef pRowsExported As Integer) As Boolean
    	Dim fields As String = "CompanyName,ContactName,ContactTitle,Phone"
    
    	Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
    		Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
    
    '             
    '                * If using .mdb use Microsoft.Jet.OLEDB.4.0 rather than Microsoft.ACE.OLEDB.12.0 as the provider name 
    '                 
    			cmd.CommandText = $"INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','{pFileName}';'Admin';''," & $"'SELECT {fields} FROM Customers') " & $" SELECT {fields}  " & " FROM Customers"
    
    			If pCountry <> "*" Then
    				cmd.CommandText = cmd.CommandText & " WHERE Country = @Country"
    				cmd.Parameters.AddWithValue("@Country", pCountry)
    			End If
    
    			Try
    				cn.Open()
    				pRowsExported = cmd.ExecuteNonQuery()
    
    				Return pRowsExported > 0
    
    			Catch e As Exception
    				mHasException = True
    				mLastException = e
    			End Try
    		End Using
    	End Using
    
    	Return IsSuccessFul
    End Function

    The example interface to try it out


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 17, 2019 1:35 PM
    Moderator
  • i dont want to use oledb provider,i tried that and its slowing my application, i have offline database which in access and i need to import thousands of rows from ms sql using Microsoft.Office.Interop.Access.Dao.dll  which make it faster, using oledb its take time as it do insert row by by
    Thursday, January 17, 2019 1:41 PM
  • The bulk of the operaton is under SqlClient via INSERT INTO OPENROWSET which is very fast unlike just using OleDb. DAO is old technology and should be a very last resort yet as you can see it's not easy to work with under Win10. With that I have nothing else to offer.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 17, 2019 2:28 PM
    Moderator
  • I have tried above solution, but i am getting following error

    {"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online."}

    I have one executable which run on Users machine, which connect to MS SQL server and then import data from MS SQL server to access database which is placed on users machine and not one MS SQL server. i think above solution work with access file place on SQL server and not on user machine.

    please help

    Thursday, January 24, 2019 12:16 PM
  • Get the database then:

    If you have SSMS (SQL-Server Management Studio) installed open a new query window and execute. (see docs here)

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

    If not then in Visual Studio create a new text file, name it configure.sql, drop the above query in

    1. Connect
    2. Run

    Now a advance method if not SQL-Server Express edition would be to use SMO (SQL-Server management Objects) to do the above using classes but SMO only works with full editions of SQL-Server (the paid version).

    Or write code to access the system registry (if they have write access).

    https://www.mssqltips.com/sqlservertip/4582/sql-server-ad-hoc-access-to-ole-db-provider-has-been-denied-error/


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 24, 2019 12:48 PM
    Moderator
  • above code will work with exe which run on client machine, and from client machine it connect to SQL server and get data and insert that in access database which again on client machine(user machine where exe run)?

    Thursday, January 24, 2019 3:07 PM
  • above code will work with exe which run on client machine, and from client machine it connect to SQL server and get data and insert that in access database which again on client machine(user machine where exe run)?

    Is the SQL-Server database

    • An attached database e.g. resides in a folder on the client machine.
    • SQL-Server Express
    • SQL-Server LocalDb

    Any of these would need to be updated via the code I presented. You could use TSQL as per this page, look at topic "To change a server property by using sp_configure".


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 24, 2019 3:53 PM
    Moderator