DB2 Signon process for Ms Access Database

[Robert Nusz]

I've developed a Access Front-End application to talk to
Mainframe DB2 tables. All works fine in being able to
view and update data, but there is one issue. When you
access a form or report that links to a DB2 table, the
system flips up a signon screen, that forces the user to
enter a userid and password for access to these
tablespaces. What I would like to do is as the user of my
application signs on, entering Userid and password,
forward this information over to mainframe db2 (under the
covers, hidden to the user) and make an automatic signon
to db2. I would think that this would be done with a
stored procedure or macro, but I'm not quite sure how to
write it. Has anyone in the Ms/Access community already
done this... Is there anyone that can provide a working
model or template to follow. The screen that I get comes
from IBM's DB2 Connect software, which includes the
Dabatase ALias name already prefilled, and requires user
to enter authorized userid and password, which happens to
be the mainframe authorized userid and password as well.
Any suggestions, thoughts, templates, would be greatly
appreciated. Thanks in advance. Robert

 

[Joe Fallon]

I use code like this to link to SQL Server tables.
The idea is similar.
I like to delete and re-create links (Refresh link can be a problem with
credentials sometimes.)

Once a given user has linked correctly they no longer have to input
credentials so the linking can be a 1 time thing if they do not need to
change DBs or credentials.

Here is some code:

I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub