64 bit windows query on linked tables problem

[dpimental]

All, I have a 64 bit version of windows 7 running access 2010.

I am trying trying to run an update query, whose tables are a combination of linked oracle tables and linked access tables.

The data is order related data.
If I run the query and filter to a specific order, it runs fine.
If I try to run it for more than 1 order it fails.

Below are the event viewer log output - that's the best I know to provide at this time. Has anyone heard of something like this before?

Log Name:      Application
Source:        Application Error
Date:          1/12/2012 9:30:39 AM
Event ID:      1000
Task Category: (100)
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      RM-REMOTE01.PublicDMZ.DistrictPhoto.com
Description:
Faulting application name: MSACCESS.EXE, version: 14.0.6024.1000, time stamp: 0x4d83e4fc
Faulting module name: oranls11.dll, version: 11.1.0.6, time stamp: 0x4b72a074
Exception code: 0xc0000005
Fault offset: 0x0003378c
Faulting process id: 0xb00
Faulting application start time: 0x01ccd136948deb27
Faulting application path: C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE
Faulting module path: C:\app\administrator\product\11.2.0\client_2\BIN\oranls11.dll
Report Id: ff50f459-3d29-11e1-b666-3cd92beb4d02
Event Xml:
<Event xmlns="[URL unfurl="true"]http://schemas.microsoft.com/win/2004/08/events/event">[/URL]
  <System>
    <Provider Name="Application Error" />
    <EventID Qualifiers="0">1000</EventID>
    <Level>2</Level>
    <Task>100</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2012-01-12T14:30:39.000000000Z" />
    <EventRecordID>2811</EventRecordID>
    <Channel>Application</Channel>
    <Computer>RM-REMOTE01.PublicDMZ.DistrictPhoto.com</Computer>
    <Security />
  </System>
  <EventData>
    <Data>MSACCESS.EXE</Data>
    <Data>14.0.6024.1000</Data>
    <Data>4d83e4fc</Data>
    <Data>oranls11.dll</Data>
    <Data>11.1.0.6</Data>
    <Data>4b72a074</Data>
    <Data>c0000005</Data>
    <Data>0003378c</Data>
    <Data>b00</Data>
    <Data>01ccd136948deb27</Data>
    <Data>C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE</Data>
    <Data>C:\app\administrator\product\11.2.0\client_2\BIN\oranls11.dll</Data>
    <Data>ff50f459-3d29-11e1-b666-3cd92beb4d02</Data>
  </EventData>
</Event>


 


Log Name:      Application
Source:        Windows Error Reporting
Date:          1/12/2012 9:37:30 AM
Event ID:      1001
Task Category: None
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      RM-REMOTE01.PublicDMZ.DistrictPhoto.com
Description:
Fault bucket , type 0
Event Name: APPCRASH
Response: Not available
Cab Id: 0


Problem signature:
P1: MSACCESS.EXE
P2: 14.0.6024.1000
P3: 4d83e4fc
P4: oranls11.dll
P5: 11.1.0.6
P6: 4b72a074
P7: c0000005
P8: 0003378c
P9: 
P10: 


Attached files:
C:\Users\dpimental\AppData\Local\Temp\2\22315755.cvr
Z:\Database\dbTables\RMOnlineClock2010_Updated.mdb
C:\Users\dpimental\AppData\Local\Temp\2\CVR5BDC.tmp.cvr


These files may be available here:
C:\Users\dpimental\AppData\Local\Microsoft\Windows\WER\ReportArchive\AppCrash_MSACCESS.EXE_43d8f08f1972147328d15b552eafef6a69f996e_0ff2c98c


Analysis symbol: 
Rechecking for solution: 0
Report Id: ff50f459-3d29-11e1-b666-3cd92beb4d02
Report Status: 0
Event Xml:
<Event xmlns="[URL unfurl="true"]http://schemas.microsoft.com/win/2004/08/events/event">[/URL]
  <System>
    <Provider Name="Windows Error Reporting" />
    <EventID Qualifiers="0">1001</EventID>
    <Level>4</Level>
    <Task>0</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2012-01-12T14:37:30.000000000Z" />
    <EventRecordID>2812</EventRecordID>
    <Channel>Application</Channel>
    <Computer>RM-REMOTE01.PublicDMZ.DistrictPhoto.com</Computer>
    <Security />
  </System>
  <EventData>
    <Data>
    </Data>
    <Data>0</Data>
    <Data>APPCRASH</Data>
    <Data>Not available</Data>
    <Data>0</Data>
    <Data>MSACCESS.EXE</Data>
    <Data>14.0.6024.1000</Data>
    <Data>4d83e4fc</Data>
    <Data>oranls11.dll</Data>
    <Data>11.1.0.6</Data>
    <Data>4b72a074</Data>
    <Data>c0000005</Data>
    <Data>0003378c</Data>
    <Data>
    </Data>
    <Data>
    </Data>
    <Data>
C:\Users\dpimental\AppData\Local\Temp\2\22315755.cvr
Z:\Database\dbTables\RMOnlineClock2010_Updated.mdb
C:\Users\dpimental\AppData\Local\Temp\2\CVR5BDC.tmp.cvr</Data>
    <Data>C:\Users\dpimental\AppData\Local\Microsoft\Windows\WER\ReportArchive\AppCrash_MSACCESS.EXE_43d8f08f1972147328d15b552eafef6a69f996e_0ff2c98c</Data>
    <Data>
    </Data>
    <Data>0</Data>
    <Data>ff50f459-3d29-11e1-b666-3cd92beb4d02</Data>
    <Data>0</Data>
  </EventData>
</Event>

David Pimental
(US, Oh)

 

[JonFer]

What do you mean by ordered data? If you're running an update query, you shouldn't use an order by clause (at least not on the records being updated). Updates to records from your query are not "visible" to the query as it is running. Not sure if that is what you were trying to do.

Also, if your tables are large, try to avoid joining Access + Oracle in your update query. Can you upload your Access data to a temporary Oracle table and then use a pass-through query?

 

[dpimental]

JonFer, thanks for your comments.

I wasn't as clear as I wanted to be.
Our company is running to terminal servers (to allow remote connection and application usage, including ms office 2010).

One server is a 32 bit version of windows 7 the other, a 64 bit version of windows 7.

The update query (with joins between access and oracle linked tables) works fine on the 32 bit version server; but not on the 64 bit version server.

I did not set up either server, just the odbc system dsn, oracle client and databases.

The exact same database that works on the one server, does not work on the other server.

There is one exception to this. If I were to run an update for a single record on the 64 bit version of the terminal server, it works. But when I run it for more than one, it does not.

Let me know if you have any insights into this or have heard of this before.

dpmental

David Pimental
(US, Oh)

 

[JonFer]

I see - you have tables with order data (as in sales/purchases). I mis-read your post. Here are some things to consider.

The problem might be the ODBC driver is 32-bit. I had that problem recently with SQL Server SSIS on a 64-bit Windows server.

When you update a single record, how do you change the query? Can you post the SQL?

Does it work when you run an update pass-through query (with no Access tables of course) to update multiple records?

What is the datatype on Oracle for the join field(s). Some datatypes aren't handled well in the ODBC interface and if a conversion is going on it might make the query unable to use the primary key on Oracle which could then prevent your updates.

 

[lameid]

I have read there being are issues with ACCDE files when created in 32 or 64 bit and used in the other version of the application.

Could this be your issue?

 

[dpimental]

JonFer, it would be difficult to post the query. It's really standard stuff.

There may be some issue related to the odbc manager - the 64 bit window, by default, uses the 64 bit odbc managerr; but office (access included) uses the 32 bit version of the odbc manager.

also, the database version is 2000 not the 2010 which is the version of office on the server.

dpimental

David Pimental
(US, Oh)