Password prompt for ODBC-linked tables

[PP]

Hi,

Previously using Access 97, there was no password prompt
when I access any of the Oracle (ODBC-linked) tables.

After converting the database to Access 2002 (on Win XP),
I get these prompts on and off (not on all tables). We
are taking about accessing those existing linked tables.

Help needed !


Many thanks and rgds.

 

[Joe Fallon]

In code this is the key: dbAttachSavePWD

When linking manually, click the save password box when you get to the list
of tables.

Delete all linked tables and re-link using one of the ideas above.
Problem should disappear.

==============================================
I use this procedure to re-create links to Oracle.
There is a local Access table (tblODBCTables) that contains the table names
and primary key fields I want to link to on the Server.
Note: the source table name needs the Schema User prefix which is in the
code. The linked table name usually omits this.

Public Function LinkOracleTables(strDSN As String, strSchema As Variant,
strSchemaPwd As Variant) As Boolean
On Error GoTo Err_LinkOracleTables

Dim db As Database, rs As Recordset, tdfAccess As TableDef, qdf As
QueryDef
Dim dbODBC As Database, strConnect As String, strSQL As String

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

SysCmd acSysCmdSetStatus, "Connecting to Oracle..."

Call DeleteODBCTableNames

Set db = CurrentDb
Set rs = db.OpenRecordset("tblODBCTables")
Set dbODBC = OpenDatabase("", False, False, strConnect)
DoCmd.SetWarnings False

Do While Not rs.EOF
Set tdfAccess = db.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbODBC.Connect
tdfAccess.SourceTableName = strSchema & "." & rs![LinkTablename]
db.TableDefs.Append tdfAccess
'run pseudo index queries here. If the table does not exist then this
gets skipped.
strSQL = "CREATE INDEX " & rs![LinkTablename] & "Idx ON " &
rs![LinkTablename] & " (" & rs![IndexFields] & ");"
DoCmd.RunSQL strSQL
TableNotInCollection:
rs.MoveNext
Loop

LinkOracleTables = True

Exit_LinkOracleTables:
On Error Resume Next
DoCmd.SetWarnings True
rs.Close
Set rs = Nothing
Set dbODBC = Nothing
Set db = Nothing
SysCmd acSysCmdClearStatus
Exit Function

Err_LinkOracleTables:
Select Case Err.Number
Case 3151
MsgBox ("There is an ODBC datasource problem." & vbCrLf & "Please
verify the DSN and database are spelled correctly." & vbCrLf & "Note: They
can be case sensitive.")
Case 3265, 3011, 7874 'item not in collection - table does not exist, or
can't find object
Resume TableNotInCollection
Case Else
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "LogOnCode - LinkOracleTables"
End Select
LinkOracleTables = False
Resume Exit_LinkOracleTables

End Function

'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

 

[Joe Fallon]

Interesing article.
Are you using that specific combination of software?

Have you tried it with other versions of Access? (2000 or 97)

To my knowledge, the volume of data in a linked table does not affect the
deletion and re-creation of links.
I almost always delete and re-create them. (se the code below.)
--
Joe Fallon
Access MVP

Joe,

I read somewhere that even if clicking on the save
password box will not do the trick.
(http://support.microsoft.com/default.aspx?scid=kb;en-
us;275111)

it is also not really feasible to delete and re-link the
tables as we are linking a lot of tables with huge data
volume.

any other suggestions ?

many thanks and warmest rgds !

-----Original Message-----
In code this is the key: dbAttachSavePWD

When linking manually, click the save password box when you get to the list
of tables.

Delete all linked tables and re-link using one of the ideas above.
Problem should disappear.

==============================================
I use this procedure to re-create links to Oracle.
There is a local Access table (tblODBCTables) that contains the table names
and primary key fields I want to link to on the Server.
Note: the source table name needs the Schema User prefix which is in the
code. The linked table name usually omits this.

Public Function LinkOracleTables(strDSN As String, strSchema As Variant,
strSchemaPwd As Variant) As Boolean
On Error GoTo Err_LinkOracleTables

Dim db As Database, rs As Recordset, tdfAccess As TableDef, qdf As
QueryDef
Dim dbODBC As Database, strConnect As String, strSQL As String

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

SysCmd acSysCmdSetStatus, "Connecting to Oracle..."

Call DeleteODBCTableNames

Set db = CurrentDb
Set rs = db.OpenRecordset("tblODBCTables")
Set dbODBC = OpenDatabase("", False, False, strConnect)
DoCmd.SetWarnings False

Do While Not rs.EOF
Set tdfAccess = db.CreateTableDef(rs!

[LinkTablename], dbAttachSavePWD)

tdfAccess.Connect = dbODBC.Connect
tdfAccess.SourceTableName = strSchema & "." & rs! [LinkTablename]
db.TableDefs.Append tdfAccess
'run pseudo index queries here. If the table does not exist then this
gets skipped.
strSQL = "CREATE INDEX " & rs![LinkTablename] & "Idx ON " &
rs![LinkTablename] & " (" & rs![IndexFields] & ");"
DoCmd.RunSQL strSQL
TableNotInCollection:
rs.MoveNext
Loop

LinkOracleTables = True

Exit_LinkOracleTables:
On Error Resume Next
DoCmd.SetWarnings True
rs.Close
Set rs = Nothing
Set dbODBC = Nothing
Set db = Nothing
SysCmd acSysCmdClearStatus
Exit Function

Err_LinkOracleTables:
Select Case Err.Number
Case 3151
MsgBox ("There is an ODBC datasource problem." & vbCrLf & "Please
verify the DSN and database are spelled correctly." & vbCrLf & "Note: They
can be case sensitive.")
Case 3265, 3011, 7874 'item not in collection - table does not exist, or
can't find object
Resume TableNotInCollection
Case Else
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "LogOnCode - LinkOracleTables"
End Select
LinkOracleTables = False
Resume Exit_LinkOracleTables

End Function

'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

--
Joe Fallon
Access MVP

Hi,

Previously using Access 97, there was no password prompt
when I access any of the Oracle (ODBC-linked) tables.

After converting the database to Access 2002 (on Win XP),
I get these prompts on and off (not on all tables). We
are taking about accessing those existing linked tables.

Help needed !


Many thanks and rgds.

.