Re: question re: linked table list

From: Joe Fallon <jfallon1_at_nospamtwcny.rr.com>
Date: Thu, 6 Mar 2003 00:21:38 -0500
Message-ID: <emlRBB64CHA.2240@TK2MSFTNGP10.phx.gbl>

I can't answer your questions.

But, why present the list at all?

I use this procedure to re-create links to Oracle. 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 Schema User prefix which is in the code. The linked table name usually omits this.

Public Sub LinkOracleTables(strDSN As String, strSchema As String) On Error GoTo Err_LinkOracleTables

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=xyz" & ";PWD=abc;" End If

SysCmd acSysCmdSetStatus, "Connecting to Oracle..."

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 = strSchema & "." & rs![LinkTablename]   dbs.TableDefs.Append tdfAccess
  rs.MoveNext
Loop

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

Exit_LinkOracleTables:

    SysCmd acSysCmdClearStatus
    Exit Sub

Err_LinkOracleTables:

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

    Resume Exit_LinkOracleTables

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

-- 
Joe Fallon
Access MVP



"Ed Stevens" <nospam_at_noway.nohow> wrote in message
news:g5bc6v49bii3ujrejeg1aepneun016bb2f_at_4ax.com...


> Sorry for the crosspost, but this is both an Oracle and MS question,

> as it involves odbc drivers from both.

>

> We have a new app being developed, using MS-Access as a front-end to

> an Oracle 8.1.7 database.  We have been playing with both the MS ODBC

> driver for Oracle and the Oracle ODBC driver.

>

> One thing we have noticed is particularly curious.  During the process

> of defining a linked table, you are presented with a list of objects

> (tables/view/synonyms) from the underlying data source.  This list is

> different, depending on which odbc driver we are using.  Further, in

> both cases, most of the objects on the list are objects for which the

> connected user does not have any rights.  In our case the odbcuser has

> only the system privilege of 'create session' and an object privilege

> of 'select' on 3 particular application tables.  That being the case,

> I would expect the list to contain only those 3 tables, plus all

> PUBLIC objects, plus the 'ALL_' views.

>

> So my questions are, how are these lists generated, why do they vary

> by odbc driver, and are there any driver configurations to control

> what is presented?

>

Received on Wed Mar 05 2003 - 23:21:38 CST