Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    16

    Unanswered: "Cannot find column ..." Error - Broken link between ADP and SQL2000?

    Hi All,

    Has anyone gotten the "Cannot find column ..." and "Can't open the table in datasheet view" errors?

    I have an Access2002 ADP linked to SQL2000. My main table, TblCases, used to have FileNumber (varchar) as its primary key. I changed that field to DocketNumber (varchar). At first, things seemed to be working well. But now I get the errors "Cannot find column FileNumber" and "Can't open the table in datasheet view" when I try to open TblCases in the ADP. Also, forms based on that table are giving me errors, mainly that FileNumber is an invalid field (which is strange since I've gone through them and deleted or changed every reference to FileNumber).

    Strangely enough, I can open TblCases just fine in Enterprise Manager, and I created a view, ViewCases, totally based on TblCases, and that opens fine in ADP.

    I checked some websites and the few references to this problem indicate that the link between the ADP and SQL2000 seems to not be working correctly, so that the table in the ADP is still looking for the old FileNumber field, while the table in SQL2000 knows that it has been replaced by DocketNumber.

    Does anybody have an idea what's going on?

    Thanks much.

    Henry

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    haven't seen that one, but have you tried unlinking and relinking the table?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by izyrider
    haven't seen that one, but have you tried unlinking and relinking the table?

    izy
    On the mark again!

    I actually have had this problem before when changing schema on the SQL server. Unlinking and relinking the table refreshes the field defs and seems to make everything happy.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hmmm. this prompts a question:

    what exactly does access cache about remote xxSQL server tables?
    how does it maintain that cache?

    reason for asking is that i have broken all the rules and have a combo driven straight from SQL (not a pass thru or mySQL-impossible stored procedure) on one of my forms.

    once in a while the damn thing freezes for ten seconds or more!

    my environment is database heaven: a test site with one humungous linux/mySQL(innodb) server, one A client, and literally zero other lan traffic or server activity. the combo is only pulling about a thousand rows and it normally does this in zero time - then for no reason - ten seconds or more!

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not terribly familiar with mySQL. Does it have performance diagnostic tools? Maybe you could monitor the db while the access app is running (or not running as is the case) and see where the load is.

  6. #6
    Join Date
    Jan 2004
    Posts
    16
    Originally posted by Teddy
    On the mark again!

    I actually have had this problem before when changing schema on the SQL server. Unlinking and relinking the table refreshes the field defs and seems to make everything happy.
    That is great news!

    I know how to unlink and relink tables in Access, but how do I do that in an ADP???

    Thanks.

    Henry

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try refreshing the connection under:

    File -> Connection

  8. #8
    Join Date
    Jan 2004
    Posts
    16
    Originally posted by Teddy
    Try refreshing the connection under:

    File -> Connection
    I tried that, but I still get the same two errors. An odd thing happened, however. Copies of all my tables appeared as views. All of them, that is, except TblCases, which is the one giving the errors. Is that supposed to happen?

    Thanks.

    Henry

  9. #9
    Join Date
    Jan 2004
    Posts
    16
    Originally posted by HenryE
    I tried that, but I still get the same two errors. An odd thing happened, however. Copies of all my tables appeared as views. All of them, that is, except TblCases, which is the one giving the errors. Is that supposed to happen?

    Thanks.

    Henry
    OK, it's now working and I'm testing to see if the ADP really is stable.

    Thanks everyone.

    Henry

Posting Permissions

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