ODBC UserID and Password Window bypass

[Rob]

Hello

My Db is linked to AS/400 tables via ODBC. Everytime I query the linked
table, even though I've already saved the password during the linking
process, the Signon window is still popping up. I have a WinXP and Acc2000.

I wrote a code plus I've added some codes that I found in this forum.
However, its not doing what's suppose to do even though I've hard coded my
UID and Pw in the connectstring. What I want to do is for the AS/400 Signon
window not to show up but instead use the store UID and PW. My codes are
listed below. If someone can shed some light, I'd appreciate it very much!
-----------------------------
Private Sub Command4_Click()

Dim mydb As Database, myq As QueryDef
Set mydb = DBEngine.Workspaces(0).Databases(0)
Set myq = mydb.CreateQueryDef("")

connectstring = "ODBC;DSN=XXX System;DATABASE=;
uid=XXXX;PWD=XXXXX;SERVER=000.000.000.00;"

sqltext = "select XXXXX from XXXXXX WHERE XXXXX='xxxxx'"
myq.ReturnsRecords = False
myq.Connect = connectstring
myq.SQL = sqltext
myq.Close

'-----------------

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim strFRM As String
strFRM = Me.cboF10.Value
strFRM2 = Forms!FRMTABLENAMES.cboTableName2.Value
strFRM3 = Forms!FRMTABLENAMES.cboTableName3.Value
Set db = CurrentDb
Set qdf = db.QueryDefs("001-qextract_query")
DoCmd.SetWarnings False
DoCmd.OpenForm "frmtablenames", , , , , acHidden
DoCmd.OpenForm "frmProcessing"
DoCmd.RepaintObject acForm, "frmprocessing"
'1st SQL
strSQL = "INSERT INTO TEMPMAINDATA1 ( Field1, Field2, Field3, Field4,
Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13,
Field14, Field15, Field16, Field17, Field18, Field19, Field20, Field21,
Field22, Field23, Field24, Field25, Field26, Field27, Field28, Field29,
Field30, Field31, Field32, Field33, Field34, Field35, Field36, Field37 )" & _
"SELECT ….,….,….., etc " & _
"FROM " & strFRM & " " & _
"WHERE xxxxx ….. ";"

qdf.SQL = strSQL
'DoCmd.SetWarnings False
DoCmd.OpenQuery "001-qextract_query"

'2nd SQL
strSQL = "INSERT INTO TEMPMAINDATA2 ( Field1, Field2, Field3, Field4,
Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13,
Field14, Field15, Field16, Field17, Field18, Field19, Field20, Field21,
Field22, Field23, Field24, Field25, Field26, Field27, Field28, Field29,
Field30, Field31, Field32, Field33, Field34, Field35, Field36, Field37,
Field38, Field39, Field40, Field41, Field42, Field43, Field44, Field45,
Field46, Field47, Field48, Field49, Field50, FIELD51 ) " & _
"SELECT ……etc" & _
"FROM " & strFRM2 & " " & _
"WHERE …….. ";"

qdf.SQL = strSQL
DoCmd.OpenQuery "001-qextract_query"

'3rd SQL
strSQL = "INSERT INTO TEMPMAINDATA3 ( Field1, Field2, Field3, Field4,
Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13,
Field14, Field15, Field16, Field17, Field18, Field19, Field20, Field21,
Field22 ) " & _
"SELECT …….. etc " & _
"FROM " & strFRM3 & " " & _
"WHERE ……. ;"

qdf.SQL = strSQL

DoCmd.SetWarnings False
DoCmd.OpenQuery "001-qextract_query"
MsgBox "Appended " & strFRM & " to the Table", vbOKOnly
DoCmd.Close acForm, "frmprocessing"

End Sub

 

[Rick Brandt]

Hello

My Db is linked to AS/400 tables via ODBC. Everytime I query the
linked table, even though I've already saved the password during the
linking process, the Signon window is still popping up. I have a
WinXP and Acc2000.

What ODBC driver? In my experience the AS400 (now ISeries) does not support
saving the user and password when you link tables. Some ODBC drivers will allow
you to "pre-sign in" by entering your user name and password into the DSN as
defined in ControlPanel (Rumba supports this) and the IBM driver has a setting
that will automatically pass your Windows network credentials to the AS400 so
that you are not prompted each time.

Other than those options you will be prompted (in some cases a LOT). What I
started doing a few years ago was prompt the user at startup and then loop
through the TableDefs and QueryDefs collections appending the UserName and
Password to all of the AS400 links and pass-through queries. That persists for
the duration of that Access session, but at least the user is only prompted one
time.

In one app I provide the option to "remember" the credentials for future
sessions which I store encrypted in the user registry. If I find remembered
credentials I use them, otherwise I prompt the user.