We just moved to Windows 10 as a company and I have VBA code that uses and ODBC to connect and pull data from an oracle database. When I try to launch the code in Excel 2016 in windows 10 I was getting the following error: MicrosoftODBC driver for OracleOracle Error while tryiing to retrieve text for error ORA-01019 So I installed an ODBC driver for Oracle I found online. Tried to run again and now get this error: MicrosoftODBC driver for OracleOracle ORA-06413: Connection not open. Any ideas of things I can try? This has worked for years in our Windows 7 environment. Note - I can make a connection to the database in excel manually using Data From Other Sources From Data Connection Wizard. I am able to query the database that way but would like to use the ODBC method.
Connect to an ODBC source Click the Data tab, then Get Data > From Other Sources > From ODBC. In the From ODBC dialog, if displayed, select your Data Source Name (DSN). Enter your connection string, then press OK. In the next dialog box, select from Default or Custom, Windows, or Database. May 15, 2016 - So appears that's the ODBC drivers in the click-to-run Office 2016 doesn't install properly or it's possible it's incorrectly setup by default.
Microsoft deprecated Oracle drivers years ago. They just sat on top of Oracle's drivers. Since you still needed the Oracle drivers installed, why not just use them? For the driver, Im using an Oracle driver.
Not if you get this in the error 'MicrosoftODBC driver for Oracle'. That is the Microsoft ODBC driver.
If you are running a 64Bit OS, the Bit version is important. It must match the Bit version of Office you are running. If you have 32Bit Office, you need the 32Bit Oracle ODBC drivers and you need to run the 32Bit ODBC admin tool: A 64-bit Windows operating system has two odbcad32.exe files:.%SystemRoot% system32 odb cad32.exe is used to create and maintain data source names for 64-bit applications.%SystemRoot% SysWOW64 odb cad32.exe is used to create and maintain data source names for 32-bit applications, including 32-bit applications that run on 64-bit operating systems. I have verified the Microsoft ODBC for Oracle driver is the there by default like you have in screenshot. I also have oracle client installed on this system 32 bit that comes with Oracles driver for ODBC. I am able to make an ODBC connection using Administrative Tools ODBC - create connection to database.
That works but is not how I want to connect to data from excel. We have multiple people that use this spreadsheet to pull data from database. Would like to get this working for them via excel/vba rather than having to setup manual ODBC connections. Not sure if that was a step in right direction or a step back It was a step in the right direction.
And that your tnsnames file points to it correctly? That connect string doesn't use a tnsnames file. OracleODBCOraORA-12 560: TNS: protocol adapter error The connect string looks good from what I can tell for a DNS-less and TNS-less connection. Unfortunately that Oracle error is a pretty generic one. If the Win7 and Win10 are on the same network with same subnetting, etc., even though the ORA-12560 typically isn't firewall related, disable the firewall on the Win10 machine and try again? Administrative Tools ODBC - create connection to database.
That works but is not how I want to connect to data from excel By 'that works', did you use the DSN in Excel to connect or just 'Test'? It has been many years since I messed with ODBC and I'm not sure if the ODBC 'Test' actually connects to the database or just verifies connectivity to the Listener.
Database is up and windows 7 system still works. Both are on same network, etc. I will try the disable of firewall on Win 10 system. I didnt use the DSN method in excel but did use Adminstrative Tools ODBC - just to create a connection to database. You may be right.
It may just connect to listener. But in excel I can connect to database using the manual method - Data From Other Sources From Data Connection Wizard. Here I can query data from the database I'm attempting to connect to via VBA/ADODB.
I dont think this method uses same method called in VBA script. Will try firewall setting on Win 10 and see what I get.
I have been using Qlik Sense desktop for a few months without any problems. Due to being forced to upgrade to version 3 I am now having problems accessing my Access databases and excel files. The connections do not load in the data load editor and when viewing the connection no tables are shown. I have decided to reformat my HDD and reinstall Windows 10, Office 2016 64bit and Qlik Sense Desktop 3.1 What do I need to install so I can set up the ODBC connections? I don;t want to have to do this again if i install the wrong data connectors.
Thanks for your assistance. Hi Simon, See I am sure you have installed the Driver for MSAccess with lets say name MYConnection I am attaching a snapshot where you can see how I go to the administrator tools = ODBC Database Administrator 64 bit = System DSN = MYConnection and then select it and done.
You will find a option test connection. If it say you OK it means your machine has connection with the database.
We Create System DSN because it will be access by system not by any particular user. Then go to Qlik Sense and go to OLEDB and you will find the name MYConnection select it and you will have your database tables. Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction. Hi Simon, As you stated, Your Application was working fine before upgrading to 3.1, Its very strange. Have your check your ODBC connection working fine by going to administrator tools = System DSN = Test Connection.
If yes m then choose the same name connection here, if it is allowing you to access the database, Then can you provide in detail what error you are facing or what exactly your issue is. Please Use OLEDB connection rather than ODBC for MS Access. I saw your did a lot of work for this. Please try OLEDB connection and let me know if you still face any issue? Hi Rohit, sorry for the late reply - i've been waiting for databases etc to update on dropbox.
I can not find where I can test the ODBC connections, where is Administrator tools? (Win 10) As i said I have reformatted my HDD and reinstalled Win 10, Office 2016 365 and MS Access Database Engine 2010 (all 64bit). Still will not work with Qlik Sense Desktop v3 (3.1)! When I go to select the data to load under Data Connections I just get the screen below. The following error occurred: SQL##f - SqlState: S1000, ErrorCode:, ErrorMsg: MicrosoftODBC Microsoft Access Driver Not a valid file name.
The error occurred here: LIB CONNECT TO 'MasterDBinQlikfolder' Data has not been loaded. Please correct the error and try loading again.
When I click on 'Load Data' I am getting this error: I created a new app and tried to connect it to another database with an ODBC connection but got the same blank screen as above. Sorry for sounding like an amateur - what provider do I need to select in the create new OLE DB window to connect to a MS Access accdb database and can I also use this to connect to an excel spreadsheet? Many thanks for your assistance, I'm desperate to get this working again. Hi Simon, See I am sure you have installed the Driver for MSAccess with lets say name MYConnection I am attaching a snapshot where you can see how I go to the administrator tools = ODBC Database Administrator 64 bit = System DSN = MYConnection and then select it and done. You will find a option test connection. If it say you OK it means your machine has connection with the database.
We Create System DSN because it will be access by system not by any particular user. Then go to Qlik Sense and go to OLEDB and you will find the name MYConnection select it and you will have your database tables. Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.