64 Bit Toad and 32 Bit Access

Rex · May 3, 2017, 6:07am

Hi, John.

Thank you very much for your help. I installed the 2010 version of the MS Access DB Engine (first link; AccessDatabaseEngine_X64.exe). I also confirmed that my TOAD version is 64-bit (the support information is below). I also installed Office 2010 64-bit, version 14.0.7015.1, just for good measure. Unfortunately, I still get the same error as soon as I try to export my dataset to “Access Database File.” It’s almost like TOAD can’t find the MS Access Database Engine.

Do I have to configure anything in the Access Db Engine after I install it? Do I need to point TOAD to the Access Db Engine?

I appreciate everyone’s help.

TOAD Support information:

APPLICATION INFORMATION - FULL VERSION (64-bit)

Support Bundle for Toad for Oracle 11.6.0.43
Bundle: Toad for Oracle Xpert (64-bit) Add-Ons: DB Admin Module
Toad for Oracle Executable Location : C:\Program Files\Quest Software\Toad for Oracle 11.6\Toad.exe
QSEServer DLL Location : C:\Program Files\Quest Software\Toad for Oracle 11.6\qse.dll
QSEServer Version : 11.6.0.43
QP5 DLL Location : C:\Program Files\Quest Software\Toad for Oracle 11.6\QP5.dll
QP5 Version : 5.227.12220.39754
QP5CA DLL Location : C:\Program Files\Quest Software\Toad for Oracle 11.6\QP5CA.dll
QP5CA Version : 5.227.12220.42377
TOAD Policy Location : C:\Program Files\Quest Software\Toad for Oracle 11.6\Toad.pdl

TEAM CODING

MANAGED ITEMS

Not a managed environment.

ORACLE CLIENT INFORMATION

Oracle Client Version : 11.2.0.2
Oracle Client DLL : C:\app\oracle\product\11.2.0\client_ora11r2_x64\BIN\oci.dll
TNSNAMES.ora Location : C:\app\oracle\network\admin\tnsnames.ora
NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252
SQLPATH : C:\app\oracle\product\11.2.0\client_ora11r2_x64\dbs
LOCAL :

ORACLE SERVER INFORMATION

Connection 1 : Oracle Database ** Active Session **
Oracle Server Version : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Oracle Server NLS_CHARACTERSET : NLS_CHARACTERSET
Oracle Server NLS_NCHAR_CHARACTERSET : NLS_NCHAR_CHARACTERSET
Oracle Session User : User (Normal)

SYSTEM INFORMATION

Operating System : Windows Server 2008 (Build 7601) Service Pack 1
Is Terminal Server: No
Using XP Theme: No

Physical Memory Load : 33.77 %
Physical Memory (Total) : 4095 MB
Physical Memory (Free) : 2712 MB
Virtual Memory (Total) : 8388607 MB
Virtual Memory (Free) : 8388080 MB
Swap Space (Total) : 12485 MB
Swap Space (Free) : 11094 MB
Toad Page File Usage (Virtual Memory) : 237 MB
Toad Working Set Usage (RAM) : 237 MB

Environment Variable (PATH) :
C:\Windows\system32
C:\Windows
C:\Windows\System32\Wbem
C:\Windows\System32\WindowsPowerShell\v1.0
C:\App\Oracle\product\11.2.0\client_ora11r2_x64\bin
C:\App\Oracle\product\11.2.0\client_ora11r2_x86\bin

Environment Variable (TNS_ADMIN) : TNS_ADMIN is not a declared environment variable.

ORACLE HOMES DATA

(Oracle Root)
inst_loc = C:\app\Oracle\Inventory\OraClient11gR2_x64
KEY_OraClient11gR2_x64 ***** This is the home currently used by Toad *****
ORACLE_HOME = C:\app\oracle\product\11.2.0\client_ora11r2_x64
ORACLE_HOME_NAME = OraClient11gR2_x64
ORACLE_GROUP_NAME = Oracle - OraClient11gR2_x64
ORACLE_BUNDLE_NAME = Enterprise
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
OLEDB = C:\app\oracle\product\11.2.0\client_ora11r2_x64\oledb\mesg
ORACLE_HOME_KEY = SOFTWARE\ORACLE\KEY_OraClient11gR2_x64
MSHELP_TOOLS = C:\app\oracle\product\11.2.0\client_ora11r2_x64\MSHELP
SQLPATH = C:\app\oracle\product\11.2.0\client_ora11r2_x64\dbs
TNS_ADMIN = C:\app\oracle\network\admin

OraClient11gR2_x64
C:\app\oracle\product\11.2.0\client_ora11r2_x64\bin exists
C:\app\oracle\product\11.2.0\client_ora11r2_x64\bin is in PATH
Home is valid

JohnDorlon · May 3, 2017, 7:20am

No, you shouldn’t have to do anything after it’s installed.

but…

I happen to have a VM with Toad 11.6 64 bit (and no MS Office of any kind), so I gave it a try with the 2010 engine. And it didn’t work for me either. After a bunch of scratching my head and googling, I found a page where someone noticed that the DLL was being installed to

C:\Program Files (x86)\Common Files\microsoft shared\DAO

so the guy copied that folder to

C:\Program Files\Common Files\Microsoft Shared\DAO

and suddenly it started working! So I tried that, and it worked for me too. Give it a shot.

Rex · May 3, 2017, 9:04am

Hi, John.

Thank you so much for your help. As you pointed out, I had the folder C:\Program Files (x86)\Common Files\microsoft shared\DAO, but I did not have the DAO folder in C:\Program Files\Common Files\Microsoft Shared. dao360.dll is the only file in the C:\Program Files (x86)\Common Files\microsoft shared\DAO folder.

I copied C:\Program Files (x86)\Common Files\microsoft shared\DAO to C:\Program Files\Common Files\Microsoft Shared\ (still only the one file). So now it looks like below. I tested it, and unfortunately, the error persisted. I also reregistered the dll in the new location (non-x86) location, and still get the error. This is really bugging me.

I appreciate all your help.

JohnDorlon · May 3, 2017, 9:28am

Ugh, I thought that was going to solve it! Yes, just one file in that folder. And mine has the name, size, and timestamp. hmmm…

For what it’s worth, I never registered it in either location.

JohnDorlon · May 3, 2017, 9:52am

I still get the error if I try to export to a .mdb file, but .accdb works.

Rex · May 3, 2017, 10:18am

Ta-daaa!

Silly me. I just assumed that TOAD would export to .mdb since that’s the file format it offers. I changed it to .accdb, like you suggested, and it exported like a champ! I’ve never felt so foolish and so relieved at the same time.

Thank you so much for your help, John.

JohnDorlon · May 3, 2017, 11:12am

Don’t feel bad - it SHOULD export to mdb also! That seems like a bug and although it’s way too late to do anything about it for 11.6, I’ll make sure it’s right in the current version.

tom.wickerath · May 3, 2017, 12:16pm

Hi John,

I also have a frequent (once per week) need to export data from Oracle to two different Access databases, in order to provide the data to other people. I'm using TOAD version 12.10.0.30 (64-bit). I'm also using Office 2013 Professional, 32-bit (Microsoft recommends 32-bit Office for most users). If I read this thread correct, it sounds like one should be able to export to 32-bit Access using 64-bit TOAD? I have not been able to do so, including after copying the DAO file you indicated above. It would really be nice, especially since TOAD can export to 32-bit Excel.

I did not want to "downgrade" my installation of TOAD to 32-bit just to satisfy this weekly requirement, so I created a virtual machine with the same version of TOAD, but with Office 2013, 64-bit. That allows me to export to Access just fine, but I simply have not found a way to export from 64-bit TOAD to 32-bit Access.

If your team is going to look into this for version next, please improve a few issues that are a bit of a pain.

1.) I have two "template" Access database files, which have empty tables with the correct table names, column names and data types. I had to do this because in some cases TOAD was converting numbers to text data type. These templates also include some saved queries for the user. The names are "Drawing_PMP_Report.accdb" and "IRCS_Records.accdb":

If I use the Export Dataset feature, and I need to export to the other database (Drawing_PMP_Report.accdb, in this example), I cannot use the Build Button (blue arrow) to select the database:

If I do, I get a prompt that demands that I replace the database!

No, I do not want to replace it, because it is a saved template. I simply want to select it as the target for an export.

Instead, I have to manually edit the name of the database, by typing it in, as indicated below:

Notice also, in the above image, that the table name is detected (correctly) and I have the option selected to Append rows (if columns match). However, I always get the following confirmation prompt. I have to be careful to click on Append Tables, instead of accidentally hitting Overwrite Tables. Why is the above option not honored?

Thank You.

JohnDorlon · May 3, 2017, 12:34pm

Hi Tom,

I didn’t mean to imply that you could export to 32 bit access from 64 bit Toad. The reason that exporting to Excel is more flexible is that Toad is creating the Excel file from scratch. In fact, you don’t even need to have Excel (or any kind of excel engine) installed to export to Excel.

The reason for the prompt (in your last question, not when clicking the … button) is that you have “If file exists” set to “prompt for append/overwrite/insert”. (Sounds like you’d prefer to have that one set to “create tables in MDB file”) The “Append rows” option that you have selected below refers what to do if the table exists within the file, not what to do if the file exists.

Regarding the prompt when you click the ‘…’ button at the top, well, that’s just a “file already exists” warning from the Save File dialog. You could actually tell it Yes if you wanted instead of manually editing. It doesn’t know that the export action will later be smart enough to append if you so choose (but maybe it should know that, to avoid confusion!)

-John

JohnDorlon · May 3, 2017, 12:48pm

Rex, the .mdb bug was fixed in Toad 12.5.

tom.wickerath · May 3, 2017, 1:08pm

Hi John,

I didn't mean to imply that you could export to 32 bit access from 64 bit Toad.

Okay, my misunderstanding of the thread.

(Sounds like you'd prefer to have that one set to "create tables in MDB file")

I’m using the .accdb file format, not the .mdb file format.

In addition, I do not want TOAD to create the tables; I only want TOAD to append to existing tables in each template file.

Regarding the prompt when you click the '...' button at the top, well, that's just a "file already exists" warning from the Save File dialog.

It seems to be an overwrite warning. I absolutely do not want TOAD to replace the file—just append to it.

I think this process could be engineered to be a bit more fluid for the user.

Tom

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Wednesday, May 03, 2017 12:36 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] 64 Bit Toad and 32 Bit Access

RE: 64 Bit Toad and 32 Bit Access

Reply by John Dorlon

Hi Tom,

I didn't mean to imply that you could export to 32 bit access from 64 bit Toad. The reason that exporting to Excel is more flexible is that Toad is creating the Excel file from scratch. In fact, you don't even need to have Excel (or any kind of excel engine) installed to export to Excel.

The reason for the prompt (in your last question, not when clicking the ... button) is that you have "If file exists" set to "prompt for append/overwrite/insert". (Sounds like you'd prefer to have that one set to "create tables in MDB file") The "Append rows" option that you have selected below refers what to do if the table exists within the file, not what to do if the file exists.

Regarding the prompt when you click the '...' button at the top, well, that's just a "file already exists" warning from the Save File dialog. You could actually tell it Yes if you wanted instead of manually editing. It doesn't know that the export action will later be smart enough to append if you so choose (but maybe it should know that, to avoid confusion!)

-John

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

JohnDorlon · May 3, 2017, 1:13pm

I’m using the .accdb file format, not the .mdb file format.

I know. It works for either. I'll change the text so it is not specific to one file extension or another. I see how that can be misleading.

It seems to be an overwrite warning. I absolutely do not want TOAD to replace the file—just append to it.

Yes it does look that way. Test it.

I think this process could be engineered to be a bit more fluid for the user.

agreed.