Perplexing ODBC Error on Native Access Query

[sxschech]

Up until last week, a union query that exports data to excel was working fine. I was prompted to change my log-in password (not database password, but the password used for signing onto the windows network). After that, when I opened my access database (2003 format running under 2010) I received an ODBC Connection Failed. I used the Link Table manager to relink, I went into the SQL pass-thru queries and relinked those under the properties window (the password to the Oracle database is still the original password as this is not synced with windows password and I haven't contacted the dbas about how to change my oracle password to match my windows password). I manually opened each linked table and pass-thru to make sure that they would open with data and each opened fine without error. For the query that is giving the error, I opened in design view and worked my way backwards (looked at the FROM and then opened that. The query in the FROM opens fine. From what I can tell, none of the items are coming from links or pass-thrus. The data that come from linked items are saved in local tables via append queries before being used in subsequent queries. Here is the interesting part. The query with the error is a UNION query. If I copy and paste the portion before the UNION, it runs fine, likewise, with the portion after the UNION. I then copied and pasted both into separate queries and saved them, they run fine. After that, I did a union on the two new queries and received the ODBC Connection Failed. I'm at a loss to figure out why this would be or how to fix it. I suppose in the meantime I'll have to add a layer of complexity by having to export export the queries individually and create a macro to put them together as the union query did, but after the fact.

 

[SkipVought]

hi,

I work for a major aero-space manufacturer and do a lot of ODBC access to Oracle & DB2 databases.

Our IT automatcally synchronizes our network password with a number of systems, including Oracle but not BD2. So every 60 days, I must change my ODBC pasword in the DB2 driver configuration.

I would conclude that if my Oracle password were not automatically changed, that I would have a problem, since there is no password change capability in the OraHome as there is in the DB2 driver configuration.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sxschech]

If the queries and tables being used are not running against links, would it matter whether passwords are synced since Oracle is not involved in the underlying data once it has been saved to a local table via make table or append query?

Anyway, as a workaround, I created a delete query, and two append queries to take the place of the union query and that seems to get me the data without the ODBC error. Then I changed the final query used to export the data to excel to point to this new table instead of the existing query.

 

[sxschech]

Hi Skip,

Although the workaround solved one problem, other problems cropped up. Based on your recommendation, I contacted the dbas and requested them to help me sync the passwords. Now all is fine with being able to use the original Union query and export results.

 

[SkipVought]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}