Submitted by gregory on 2017/07/08 01:31
I’m trying to link IQ data into Microsoft Word. To do this, I'm creating a Microsoft Office ODC file in IQ, following:

7.30 Live Links to IQ Grids from Word and Excel (node 1254)

This used to work, on another computer running Windows 7.  On 10/06/2015 Wednesday, I note that I had successfully linked InfoQube to Excel.

My current setup is a new computer running Windows 10 Creators’ Edition, Microsoft Office Subscription Product Microsoft Office 365, Version 1706 (Build 8229.2073 Click-to-Run), Office Updates are automatically downloaded and installed,  Microsoft® Word 2016 MSO (16.0.8229.2073) 64-bit.

I’m using InfoQube 0.9.98a

I get this message in Excel if when I open the ODC file I specify ‘Add this data to the data model’:

We couldn't get data from the Data Model. Here's the error message we got:
Failed to connect to the server. Reason: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

Lots of advice on StackExchange programmers’ forum. I think this is only applicable by the developer, Pierre!
https://www.google.co.uk/search?q=infoqube+https%3A%2F%2Fstackoverflow…
 
If when I open the ODC file in Excel, I do not  specify ‘Add this data to the data model’, I get this message:

Initialization of the data source failed.
Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see
this message again, create a new data source to connect to the database.
 
No improvement if I recreate the ODC file.
 
Once I get the Excel link working, I plan to link to that from Microsoft Word.
 
Is there a workaround, or is a fix required in IQ?
 
Thanks for your advice.
 

Comments

gregory

2017/07/09 01:57

Permalink

No can do - error
 
You cannnot install the 32-bit version of Microsoft Access
Database Engine 2016 because you currently have 64-bit Office
products installed. If you want to install 32-bit Microsoft Access
Database Engine 2016, you will first need to remove the 64-bit
installation of Office products. After uninstalling the following
product(s), rerun setup in order to install 32-bit version of
Microsoft Access Database Engine 2016:
Office 16 Click-to-Run Extensibility Component
 
But I run 64-bit Office and am an active user of Microsoft Access. I'm reluctant to shift to 32-bit Office unless it's the only way to keep InfoQube. In addition I'm using Office 365 and not an installed version of Office.
 
I suspect this has become a problem since I installed Windows 10 Creators Edition, but I'm not sure I've tried to use ODC on this particular computer.
 
Any suggestions?
 
Mark GREGORY, Hull, England- GMT +0/+1; EST +5

gregory

2017/07/10 11:33

Permalink

Thanks, Pierre, but still not working.
 
I downloaded the 32-bit software componen from the site you specified and installed it. I then ran it from the command line:
 
run C:\Users\markr\Downloads\AccessDatabaseEngine.exe /passive
 
This fails much as before:
 
"
Initialization of the data source failed.
Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see
this message again, create a new data source to connect to the database.
"
 
The original blog entry https://techblog.aimms.com/2014/10/27/installing-32-bit-and-64-bit-micr… suggests that a .dsn file needs to be established. This is the file needed to configure an ODBC link. Is this under my control, or is it something that you need to establish?
 
 
"DSN (Database Source Name) is the extension attached to files used by database client software to connect, via an ODBC (Open Database Connectivity) driver, to a database. The DSN file typically provides the data source name and directory, the server address, user ID and password, and other information. DSN files often store that data as plain text, although they may also store it in the system registry."
 
Mark GREGORY, Hull, England- GMT +0/+1; EST +5

Pierre_Admin

2017/07/10 10:20

Permalink

Another option to try is to connect the "good old way" using named ODBC connection:
  • Open the Control Panel (Win + X)
  • (32-bit) (strange name for ODBC Data Source Administrator under Windows 10)
  • Under the User DSN tab, click add...
  • Select Microsoft Access Driver (*.mdb)
  • Click Finish
  • The ODBC Microsoft Access Setup dialog will open
  • Locate YourIQBase.sndb file and click OK
  • In Excel connect to this data source
If this does work, within Excel, connect to an external database. The steps vary with each Excel version, but should be something like:
  • Connect to external database
  • Select driver
  • Select file
  • Select table
  • OK
HTH !
 
 
Pierre_Admin
IQ Designer
 

gregory

2017/07/10 13:48

Permalink

This all comes tantalisingly close to working, but doesn't.
 
This is the recent sequence which I've followed; since the facility to clean up Word-derived data is no longer visible on this Drupal-based website, I've gone via Notepad++:
 
PIERRE:
Re: Cannot link IQ grid to Microsoft Excel - 64-bit Windows ...
Submitted by Pierre_Admin on Mon, 2017-07-10 16:20.
Another option to try is to connect the "good old way" using named ODBC connection:
•    Open the Control Panel (Win + X)
•    (32-bit) (strange name for ODBC Data Source Administrator under Windows 10)
== On my PC, this needs to be: C:\Windows\SysWOW64\odbcad32.exe
•    Under the User DSN tab, click add...
•    Select Microsoft Access Driver (*.mdb)
•    Click Finish
•    The ODBC Microsoft Access Setup dialog will open
•    Locate YourIQBase.sndb file and click OK
•    In Excel connect to this data source
If this does work, within Excel, connect to an external database. The steps vary with each Excel version, but should be something like:
•    Connect to external database
•    Select driver
•    Select file
•    Select table
•    OK
HTH !
 
 
Pierre_Admin
IQ Designer

MARK:
    A 64-bit version of the Microsoft Windows operating system includes the following versions of the Microsoft Open Database Connectivity (ODBC) Data Source Administrator tool (Odbcad32.exe):
•    The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder

•    On my system, this is C:\Windows\SysWOW64\odbcad32.exe
 
•    Under User DSN, I click Add…
•    Choose Microsoft Access Driver (*.mdb)
•    I click Finish

In the dialogue box which opens:
 
Under Database: Select…, I Select my IQbase, then name my Data Source Name IQMain9.

In Excel, I choose Data / Existing connections / Browse for more…
Choose +Connect to new data source
Choose ODBC/DSN, then Next
The DSN that I have just created is visible as IQMain9
 
When I attempt to test the data connection, this fails:
 
"
Test connection failed because of an error in initializing provider.
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
"
 
It seems that I’m not quite succeeding in using the 32-bit code. Any ideas? .bat file?

Mark GREGORY, Hull, England- GMT +0/+1; EST +5

gregory

2017/07/10 13:37

Permalink

On this particular issue: I'm really not keen to install 32-bit Office - unless it can't be avoided. I feel sure that this will create obscure errors elsewhere in my general use.
 
Mark GREGORY, Hull, England- GMT +0/+1; EST +5

gregory

2017/07/21 00:33

Permalink

Hi Pierre. I'm not getting this to work on this 64-bit system. This is a major problem for me and a significant regression on what I was achieving as a matter of course on my earlier desktop computer. I would appreciate  knowing what to do next.
 
Mark GREGORY, Hull, England- GMT +0/+1; EST +5

Hi Pierre, thanks for that. I had been thinking that, since I have Access installed, that was enough, but I just tried installing the database driver as well (I tried with both the 2010 and 2016 versions). But when I try to open the ODC file in Excel and show the data in a sheet, I get the following:

 

The query did not run, or the database table could not be opened.
Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.

  1. Your IQBase is not password protected ?
  2. Perhaps try with a sample app first
  3. Also, if you have MS Access, try creating a sample database and try to link to it from Excel

HTH !

Pierre_Admin
IQ Designer