UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Odbc Passthrough Query Problem With Ibm Iseries (db2) And Multiple Systems    
 
   
Derevon
post Jan 9 2020, 10:05 AM
Post#1



Posts: 3
Joined: 1-July 18



Hi everyone,

So I am using some pass-through queries in Access connecting to our finance system via ODBC (IBM iSeries etc). The connection string looks like this:

ODBC;DRIVER={iSeries Access ODBC Driver};TRANSLATE=1;SYSTEM=XXXXX

So this works just fine for the first system. If I just open Access and run this query I'm prompted for login and password.

The problem is if I try to run another query that is connecting to another SYSTEM, then it's trying to use the previously saved connection rather than prompting me for a new username/password, even though the SYSTEM is specified to something else in the connection string.

The only work-around I have found is to restart Access, but that's obviously not a very good solution when you need to connect to some different systems.

Does anyone know of a way to get around this problem?

Thank you
Go to the top of the page
 
GroverParkGeorge
post Jan 9 2020, 10:19 AM
Post#2


UA Admin
Posts: 36,549
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

That is, indeed, the down side to Access caching of connections. Normally, that's a good thing because, once the connection is established, you don't have to repeat the credentials during that session. Here, of course, you need to use different credentials for different passthrus and the caching is not helpful.

Is it undesirable to create and SAVE the credentials for each passthru? In other words, does your security context require that you sign in each time you start Access, or could you work with stored credentials in the Access accdb?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Derevon
post Jan 9 2020, 10:54 AM
Post#3



Posts: 3
Joined: 1-July 18



Thanks,

Storing the login and password would be acceptable.

I now tried adding the credentials to the connection string, but it didn't seem to make any difference unfortunately.

It seems as long as there is an "active" connection, it won't bother trying to make a new one.
Go to the top of the page
 
GroverParkGeorge
post Jan 9 2020, 11:10 AM
Post#4


UA Admin
Posts: 36,549
Joined: 20-June 02
From: Newcastle, WA


You should be able to add the different connections to the properties of the passthrus like this:

Attached File  ptcreds.png ( 19.02K )Number of downloads: 3


This one, of course, connects to SQL Azure so the syntax is different. The principle, though, should be the same.

Each PT would have its relevant connection string stored with it.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 9 2020, 11:11 AM
Post#5


UA Admin
Posts: 36,549
Joined: 20-June 02
From: Newcastle, WA


There are probably other ways to dynamically select and employ different connection strings for different passthrus, using VBA. However, that would probably be more complicated than necessary.

See if this works first.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Derevon
post Jan 9 2020, 11:34 AM
Post#6



Posts: 3
Joined: 1-July 18



Same issue when specifying the credentials in the connection string unfortunately.

I now tried to create DSNs in Windows 10 instead of using a connection string with driver etc, and that seems to work actually.

Although, if possible I would rather avoid using DSNs as they need to be setup, configured, etc.
Go to the top of the page
 
GroverParkGeorge
post Jan 9 2020, 11:39 AM
Post#7


UA Admin
Posts: 36,549
Joined: 20-June 02
From: Newcastle, WA


If you create File DSN's, Access will store the credentials in an internal table. See this discussion.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th January 2020 - 09:55 AM