Hello from Down Under,

Searching all over the net has lead me here and am hoping the guru's on this forum can help.

I have MS Access as the front end with reports, queries, etc connecting via ODBC to tables in DB2. Whenever opening the DB2 tables from Access I get a prompt for a username and password again and again, more so for the larger tables. Weird. This gets frustrating especially when running a macro for a report with multiple queries accessing multiple tables.

I have tried defining the username and password within the DSN which fills in my username at the prompt but I still have to enter a password. I've read about programmatically defining the username and password but I wanted to keep the design as simple as possible. (You guessed it, I'm not a programmer)

Also tried re-linking the tables and ticking the 'Save password' option. This works at first but as soon as I close the database or leave it for a while it comes up with ODBC call failures when I try open it again. :o( This happens to my colleagues as well so it's not just me.

When I access the DB2 tables using Quest Central, I only ever get prompted once for the password and can query the tables successfully so I doubt it's a permission problem at the DB2 level. I only use Access to extract data from DB2 for reports, not make any updates or changes.

Any suggestions greatly appreciated.


Cheers,
Dustin (d-fxt)


Other info:
O/S: Windows XP Pro
Application: MS Access 2003
DB2: IBM DB2 Universal Database 7.2 hosted on AIX

There are 4 things you can do that I can think of:

1. Pass the username and password in each call. You can do this by storing the username and password in a local table and using a password mask on that field so people can't read your system password. Then open a hidden form and take the data from the form each time you need it. Better still, create a user that only has read access to the back end, and then it really won't matter if they see it. Also make the table hidden.

2. When the database opens, prompt the user for the username and password to the back end, and then store these in a hidden table/form, and take the username and password from the hidden form for each call.

3. Since this is an OLAP front end, do what I do on my OLAP Access apps: Pull ALL the data you will need in one pull when the database opens and store it in local tables. Then do all the rest of your work locally. This can bloat the front end, but if it doesn't go more than 100MB or so, the users won't really see a huge problem. Load might take 20 seconds.

4. Permanently link the front end to the back end. Then all the rest of your functions are to "locaL' tables, so no username and password are required. I do this as well, but mostly for apps where the user is changing data on the back end. The user will only be prompted once for username and password.

Does this help?


mmcdonal

Some good ideas.
Thanks mmcdonal.
Option 3 sounds like the way to go in my case

Cheers mate.
Dustin