Pass Through Query Help 1

[djbjr]

With the assistance of this board I was able to manipulate and Access Database so it will update our Oracle tables.

I have completed my work on our development side and am ready to migrate to acceptance.

Does anyone know an easy way to change how the pass through query connects to the Oracle database?

I have changed the ODBC connection but they are still trying to connect to my development environment.

Thanks in advance

dj

 

[jsteph]

I have changed the ODBC connection but ...

DId you change the actual DSN, or the ODBC Connect property of the query?
Start with the query, check which DSN that references. Then check that dsn, see which server that references.

Access needs to close before these changes are seen--access does some local caching of dsn info, so changing a dsn while access is open will not necessarily give you an instant re-direct to the new location.
--Jim

 

[djbjr]

To clarify, I have changed the connection that prompts when I execute the query to the correct database (HR_DCP)

After I enter the user name and password, I then get an errot that says "error connecting to HRDWD" which is the development ODBC connection.

I do not know how to change the connection within the query. When I click on Design view all that comes up is the actual statement I am passing.

 

[jsteph]

You need to right-click on the titlebar of the query box, and at the bottom of the right-click-menu, the last option should be Properties.

There will be an item called something like "Odbc Connect str". It is generally like below:
ODBC;DSN=NameOfDSN;UID=SomeUserName;PWD=SomePwd

There could be other optional args, but the above is the most basic. The PWD is cleartext and not encrypted, so it's generally a good idea to have this user/pwd be a read-only with read on the limited tables you'll be allowing here--that is, don't use the db owner uid/pwd here, it's giving it away if you do.

Anyway, the NameOfDSN in the above example, is the dsn name you'd edit when you go to the ODBC connections in Control Panel/Administrative Tools/ODBC Connections.

Find it there and configure it so it points to the right db, and all should be fine.
--Jim

The

 

[djbjr]

Thanks

I couldnt find the properties because the window od the query was not maximized.

Once I maximized it, it was plain as day.