Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: Doesn't show all columns...

    I have a big problem...

    I'm supposed to import data from an Oracle database and insert into several Access databases.

    My BIG problem is that Access doesn't show more than 256 columns!!!
    It seems it doesn't support more than 256 columns at all...

    The table that I'm supposed to import from has 328 columns! And no it can't be redesigned... Even if I know the columnname Access doesn't work..

    Is there any way around this? I have a SQL Server database that I figured I could use but I'm not allowed to have a linked server in it so I can't link to the tables in the Accessdatabases and the Accessdatabases can't link to SQL-server because there are other accessdatabases that are linked to these tables and links to linked tables are not allowed!

    Frustrated...

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    If you can create views, you could make 2 views both including the PK and half the fields. Link to them and your problem is solved after you re-connect the data...

    Regards

  3. #3
    Join Date
    Jul 2003
    Posts
    123
    I'm not allowed to do anything in the Oracle database, its a read-only connection... Or can I do that in Access?

    I've asked for a SQL Server to use as backend and will use Access as a shell for it.

    The boring thing is that I wanted everybody to be able to do small changes in how the data is selected...

    It feels like double work to first import the data to SQL Server and then select from that datasource with Access, but maybe that is the easiest solution in this case...

    Any other suggestions?

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    How about you use a Pass-through query instead of linked tables?

    Regards

  5. #5
    Join Date
    Jul 2003
    Posts
    123
    Now I'm afraid I don't understand, what do you mean by "pass through query"?

    The scenario is this:

    1. Bunch of Access Databases linked together like spagetti

    2. Data is on an Oracle Database in a 338 columns wide table

    3. Access Import Tool imports data into several tables in the spagettibunch
    from Oracle via ODBC

    Problem: Access doesn't show all columns in the table with 338 columns. I can't choose data even if I know which column name to use.

    The only thing that can be changed in this mess is the Import Tool. Right now its an Access database but I'm leaning towards a two layer solution with sql-server beneath.

  6. #6
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    In access you can send querys to oracle using pass-thruogh querys.
    Create a new query, dont add any tables...
    Now in the menu, click Query=>SQL Specific=> Pass-through query
    Note: you are now in SQL view in the query builder
    If you get the properties (right click the title bar of the query, select properties)
    you can set all kinds off things, including the connection string (which you can steal/copy of you linked tables).
    Thus you will not need to use linked tables at all and you can pick and choose what field you want in the query from what table.... even linking the data and stuff...

    Regards

  7. #7
    Join Date
    Jul 2003
    Posts
    123
    Always time to learn something new!

    Thanks! Works very good now... :-D

  8. #8
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    As am I from other people in other parts of the forum, just "returning the favor"

    Happy to help you and anybody on the forum

    Regards

Posting Permissions

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