受欢迎的博客标签

Troubleshooting Excel and BarTender

Published

https://support.seagullscientific.com/hc/en-us/articles/217162148-Troubleshooting-Excel-and-BarTender

 

Table of content

如何在不删除32位Office的情况下安装AccessDataEngine64位

 

XLSX -- Excel Workbook (Office 2010..Office 2016)
XLSM -- Excel Macro-Enabled Workbook
XLSB -- Excel Binary Workbook
XLS -- Excel 97-2003 Workbook

如果后缀为:.xls   则这个Excel版本是97-2003版的

如果后缀为:.xlsx 则这个Excel版本是2007之后版的

.xls版本和.xlsx版本所支持最多的行和列

                             行数           列数

          .xls版本      65536         256(IV列) 

          .xlsx版本    1048576     16384(XFD列)
 

.xls格式的文件所有OFFICE程序都能打开,但.xlsx只有OFFICE2007或者更新的版本才能打开.

 

 

共同点:

Microsoft ACE driver will allow you to query Office files (Including Access database AND Excel files)

Microsoft.Jet.OLEDB.4.0和Microsoft.ACE.OLEDB.12.0 一样,都是连接到Excel对象的接口引擎;

访问ACCESS或者Excel时,会用到数据库访问的组件。

有时候会出现如下图所示的错误,即Microsoft.Jet.OLEDB.4.0,microsoft.ACE.oledb.12.0 provideer is not registered on the local machine.

不同点:

对于不同的Excel版本,有两种接口可供选择:

Microsoft.Jet.OLEDB.4.0(以下简称 Jet 引擎)

Microsoft.ACE.OLEDB.12.0(以下简称 ACE 引擎)。


Jet 引擎,可以访问 Office 97-2003,但不能访问 Office 2007。

ACE 引擎是随 Office 2007 一起发布的数据库连接组件,既可以访问 Office 2007,也可以访问 Office 97-2003。

另外:Microsoft.ACE.OLEDB.12.0 可以访问正在打开的 Excel 文件,而 Microsoft.Jet.OLEDB.4.0 是不可以的。

 

Microsoft.Jet.OLEDB.4.0由Microsoft Access Database Engine 2007 Redistributable  提供

Microsoft.ACE.OLEDB.12.0由Microsoft Access Database Engine 2010 Redistributable  提供

Microsoft.ACE.OLEDB.16.0由Microsoft Access Database Engine 2016 Redistributable提供

 

Download Microsoft Access Database Engine 2010 Redistributable

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

Version:SP2
Date Published:12/20/2019

File Name:                                                    File Size:
AccessDatabaseEngine.exe                           25.3 MB
AccessDatabaseEngine_X64.exe                   27.3 MB

Download Microsoft Access Database Engine 2016 Redistributable

from https://www.microsoft.com/en-us/download/details.aspx?id=54920

 

64位系统下.NET访问Access 数据库,最彻底的方法是安装64位的Access数据访问驱动安装程序 并使用新的Access提供程序  Microsoft.ACE.OLEDB.12.0

 

 

1.The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.

An OLE DB (Object Linking and Embedding Database) provider is a program that can retrieve information from a database and pass it to a requesting application, such as BarTender. Each type of database (such as Oracle or Access) requires its own provider program.

Resolution

These steps should be tried in the following order until the issue is resolved.

step 1:Make sure the Jet Data Manager and Excel have the same bit-rate. If Excel is 32-bit, you will need to install the 32-bit version of the Jet Data Manager.  If Excel is 64-bit, you will need to install the 64-bit version of the Jet Data Manager.

step 2:Install Microsoft Access Database Engine 2016 Redistributable  These components can be found here: Microsoft Access Database Engine 2016 Redistributable

Ensure that you explicitly run the executable as an administrator

step 3:Install 2007 Office System Driver: Data Connectivity Components. These components can be automatically downloaded here: 2007 Office System Driver: Data Connectivity Components 

step 4:remove this download

To remove this download:
To remove the download file itself, delete the file AccessDatabaseEngine.exe
On the Start menu, point to Settings and then click Control Panel.
Double-click Add/Remove Programs.
In the list of currently installed programs, select “Microsoft Access database engine 2016” and then click Remove or Add/Remove. If a dialog box appears, follow the instructions to remove the program.
Click Yes or OK to confirm that you want to remove the program.
ACE driver is available from Microsoft here:
Microsoft Access Database Engine(ACE) 2010 数据库引擎可再发行程序包
 
Microsoft Access Database Engine 2016 Redistributable
 

问题

1.BarTender打开*.BTW提示未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序
说明:
C#以 oledb的方式读取excel时,xls格式的文件读取正常,但是xlsx,xlsm格式的提示错误:未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序。
 
实战环境:
 
windows server 2016 64bit
office 2013 64bit
bartender 2016 r8 64bit
 
excel 数据源是用de office 2007 32bit 
 
解决办法:
安装 32bit的Microsoft Access Database Engine 2010 Redistributable
 
 
原因:
 Install Access Database Engine
Download AccessDatabaseEngine.exe from Microsoft

Connecting to Microsoft Excel documents requires the Microsoft Access Database Engine. This is a tool provided by Microsoft that allows applications (like BarTender) to connect to Microsoft Excel and Access.

You can install the latest version of the Microsoft Access Database Engine like this:

Download AccessDatabaseEngine.exe from Microsoft.
Run AccessDatabaseEngine.exe.
Click through the installation wizard.
Close and reopen BarTender.
Re-create the connection to the Excel file using the Database Connection Setup wizard.
 
解决:
BarTender and Microsoft Office  32/64 must be match

你是64位的Office,你就安装AccessDatabaseEngine_X64.exe。

Download Accessdatabaseengine from Microsoft. 
If your bartender version is 64-bit, please download accessdatabaseengine_x64.exe;

你是32位的Office,你就安装AccessDatabaseEngine.exe。

If your bartender version is 32-bit, please download AccessDatabaseEngine.exe.
2) execution of AccessDatabaseEngine.exe or accessdatabaseengine_x64.exe;

3) Click Install Wizard;

4) Close and re-open bartender;

5) Re-establish the Excel tag database.

 

只能二选一,装了一个之后另一个就装不上。

我是64位Office,那必然是安装AccessDatabaseEngine_X64.exe

用c#写了个控制台应用实现使用 OleDbConnection 读取Excel(支持.xls与.xlsx文件)

 

 

 

2.Developers number one Connection Strings reference(Excel connection strings)

https://www.connectionstrings.com/excel/

class Program
    {
        //函数用来读取一个excel文件到DataSet集中  
        public static DataSet ExcelToDataSet(string filename, string tableName) 
        {
            //获取文件扩展名
            string strExtension = System.IO.Path.GetExtension(filename);
            OleDbConnection myConn = null;
            switch (strExtension)
            {
                case ".xls":
                    myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
                    break;
                case ".xlsx":
                    myConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");
                    //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串) 
                    //"HDR=yes;"是说Excel文件的第一行是列名而不是数,"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。 
                    break;
                default:
                    myConn = null;
                    break;
            }
            if (myConn == null)
            {
                return null;
            }
            string strCom = " SELECT * FROM ["+ tableName + "$]";      
            myConn.Open();
            //获取Excel指定Sheet表中的信息
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            DataSet ds;
            ds = new DataSet();
            myCommand.Fill(ds, tableName);
            myConn.Close();
            return ds;
        }
        static void Main(string[] args)
        {
            var tablename = "sheet1";
            //括号中为表格地址  
            DataSet ds = ExcelToDataSet("D:\\get.xls", tablename);            
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string str = ds.Tables[tablename].Rows[i]["价格"].ToString();  //Rows[i]["col1"]表示i行"col1"字段  
                Console.WriteLine(str);
            }
            Console.WriteLine("正确执行...");
            Console.ReadKey();
        }
    }

 

Microsoft Access 2010 Runtime

The Microsoft Access 2010 Runtime enables you to distribute Access 2010 applications to users who do not have the full version of Access 2010 installed on their computers.

AccessRuntime.exe
AccessRuntime_X64.exe

https://www.microsoft.com/en-gb/download/details.aspx?id=10910

 

useful links

在不删Office的情况下安装AccessDataEngine64位

由于某些64位应用程序需要访问Access数据库,而访问数据库须使用AccessDataEngine即Access数据库引擎64位。而正常情况下,如果有32位Office安装在系统中,AccessDataEngine64位将会提示无法安装.

https://blog.csdn.net/DiXinWang/article/details/104438803

Make sure that you download the Access Database Engine 2016 Redistributable and not the Microsoft Access 2016 Runtime.
If the computer already has a 32-bit version of Office, then you have to install the 32-bit version of the components. You also have to ensure that you run the SSIS package in 32-bit mode, or run the 32-bit version of the Import and Export Wizard.
If you have a Microsoft 365 subscription, you may see an error message when you run the installer. The error indicates that you can't install the download side by side with Office click-to-run components. To bypass this error message, run the installation in quiet mode by opening a Command Prompt window and running the .EXE file that you downloaded with the /quiet switch. For example:

C:\Users\<user_name>\Downloads\AccessDatabaseEngine.exe /quiet

If you have trouble installing the 2016 redistributable, install the 2010 redistributable instead from here: Microsoft Access Database Engine 2010 Redistributable. (There is no redistributable for Excel 2013.)

https://docs.microsoft.com/en-us/sql/integration-services/load-data-to-from-excel-with-ssis?view=sql-server-ver15

最新的Access 2013 安装后,已经不能对dbf文件进行链接读取了,Access 2010以前的版本都可以,如果以前的项目用了Access链接大量的dbf文件的话,

升级Access 2013后,访问链接dbf文件的表,就会出错,具体的解决办法是,安装Access 2010的运行时环境,在改注册表。

 

我的环境是win7 64bit + Office 2013 plus 64bit。

1,下载Access 2010的支持环境AccessDatabaseEngine_X64.exe

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

2,修改注册表,将下面的文件保存为一个merge.reg文件,双击这个文件导入注册表。

https://www.cnblogs.com/xiashengwang/p/5694650.html

http://jonwilliams.org/wordpress/2014/03/21/enabling-xbase-dbf-support-in-microsoft-office-access-2013/

问题不在于是Access 2010还是Access 2013而已,不必钻牛角尖,何不求其次,安装一个Access 2007/2010的Database Engine就行

https://www.cnblogs.com/bluedoctor/p/3925871.html