Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Unable to View/Link Tables

    I'm hoping someone might be able to help me determine why my list of available tables has become truncated? Let me explain briefly. I use MS Access 2003 (SP3) to establish an ODBC connection to PeopleSoft tables. For some time, I've been able to Get External Data ==> Link Tables ==> select my data source (System DSN / Oracle Driver) and select from all available PeopleSoft tables A-Z - fairly straightforward.

    Now, however, my list of available tables has been truncated. It's A-I, so I can't see/select over half of the tables. I know it's not a database permissions issue b/c some of the tables no longer displayed are already linked in other MS Access dbs, and I can still access the data via the linked table after authenticating with my credentials.

    I simply can't figure out what has changed?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you link them using VBA code?
    Have a nice day!

  3. #3
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Unfortunately, I don't have a good enough command of VBA coding. I've tried a time or two with no success.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sometimes the MDAC can affect this. Have you tried installing the latest MDAC version or re-installing it?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Thank you for the suggestion. I re-installed the latest MDAC, but that, unfortunately, did not resolve the problem.

  6. #6
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    I just came across a thread in another forum which shed some light on this issue:

    OTN Discussion Forums : MS Access ODBC to Oracle missing tables ...

    It would appear my issue is related to a change in the Oracle database. The VBA code below doesn't resolve the issue, but does provide a work-around. Here's what it states:

    "I found my the answer to my own question....I used the Oracle Driver that was installed with my client install. For those of you that are trying to connect to Oracle from Access. I had to create the link using VBA, there is an extra parameter that needs passed in the connection to Oracle, the XSM Parameter. This will create a link in Access called "fsdev_PS_MASTER_ITEM_TBL", the source oracle table is SYSADM.PS_MASTER_ITEM_TBL. You must also have the Microsoft DAO 3.6 Object library installed in the Tools --> References. See the code below:

    Option Compare Database
    Option Explicit

    Function AttachTable() As Variant
    On Error GoTo AttachTable_Err

    Dim db As Database
    Dim tdef As TableDef
    Dim strConnect As String

    Set db = CurrentDb()
    strConnect = "ODBC;DSN=FSDEV;DBQ=FSDEV;DATABASE=;XSM='SYSAD M'"
    ' NOTE: DSN is your ODBC Data Source Name; DBQ is your TNSNAMES.ORA entry name
    ' NOTE: YOU MUST PASS IN THE XSM PARAMETER IN NEWER VERSIONS OF ORACLE DB

    Set tdef = db.CreateTableDef("fsdev_PS_MASTER_ITEM_TBL")
    tdef.Connect = strConnect
    tdef.SourceTableName = "SYSADM.PS_MASTER_ITEM_TBL"
    db.TableDefs.Append tdef

    AttachTable_Exit:
    Exit Function

    AttachTable_Err:
    MsgBox "Error: " & Str(Err) & " - " & Error$ & " occured in global module."
    Resume AttachTable_Exit

    End Function"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •