Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2008
    Location
    New Orleans, Louisiana, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am using an Access Database created by someone else. There are a lot of local tables due to the size of the linked tables in the ODBC. There is one Make Table query that I can not get to work though. I tried creating a new query but it didn't work either. The query to make the table is
    SELECT P.* INTO P_L
    FROM P;

    When I run it, I get an error that says "ODBC--call failed [Cache' ODBC][State : S1000][Native Code 400].....".

    Any ideas?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    There are a number of things that could cause that situation. It would help to know what the linked tables connect to - SQL Server, mySQL, Oracle, DB2, etc. It is possible that the design of the table in the ODBC data source has changed, or the table has become corrupted, etc. I would make a copy of your Access database and then try relinking that particular table using the Linked Table Manager, and see if that fixes it. Another thing to try is to change the query into a select query and see if that returns any records. It would also be useful to know what version of Access you are using. Let us know how things turn out.
    Wendell

  3. #3
    New Lounger
    Join Date
    Dec 2008
    Location
    New Orleans, Louisiana, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The tables are linked to Oracle and none of the other tables are causing a problem. I am using Office 2007 but the database was created in 2003. I have refreshed the link several times with the same results. However, I just compared the latest data dictionary to what is in the linked table and there are two fields in my database linked table that are not in the data dictionary. I tried running the Make Table Query again but got the same error. I took your advice and did a Select Query and left out those last two fields and finally got results (yahoo). Is there a way to make the P_L table or should I edit the queries that use the P_L table and replace it with the new select query?

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    One thing to check is that the field names are not reserved words.

    eg if you try to create a field called 'User' the query will fail.

    To solve this you just surround the field name with square brackets eg [User]

    I have a rule that all fields should be make up of at least two words separated by and underscore. This prevents accidental use of keyworks.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Laura9of9 View Post
    Is there a way to make the P_L table or should I edit the queries that use the P_L table and replace it with the new select query?
    I think your best approach would be to drop the linked table, and then relink to it. That should correct your table definition in Access to whatever the Oracle DB has in it's dictionary. Then your make table query should work as is, since it uses the * to select all of the fields. Refreshing the links works with some ODBC drivers, but not all - and apparently in your case not with the Oracle ODBC driver. What you've encountered is the result of design changes in Oracle messing up your Access database.
    Wendell

Posting Permissions

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