Broken Link Between ADP and SQL2000? "Can't find column..." Error

[HenryE]

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 other 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

 

[Nowell]

Hi,

I think you need to Create a new .adp and import all the objects from the old one. Then reconnect to SQL server and that should fix it......hopefully.

Nowell

 

[MichaelRed]

A common error in these cases is forgetting to change the field name in some 'obscure' place. Often this is in an Index or filter. There are commercial and shareware/freeware 'products' which are quite good at finding and replacing ALL eplicit (fully spelled out) references which might help. Another issue MAY be that you have a generated name somewhere in the code:

[tab]Dim MyFld as String
[tab]Dim MyType as String
[tab]Dim MyRef as String

MyFld = "File"
MyType = "Number"


[tab]MyRef = MyFld & MyType


So that "FileNumber" doesn't appear as a literal within the code.

Another common error is to have the name in a combo box list such as "FileNumber", and not find the element.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[cmmrfrds]

Did you try breaking the connection and then doing a compact and repair and then reestablishing the connection.

 

[cjowsey]

MichealRed,

Can you name and recommend any of these products? Alternatively are there any that you have tried and would not recommend?

Thanks,
Clive

 

[MichaelRed]

There are several threads within Tek-Tips discussing these. The only one I have ever used is "SpeedFerret", which is a (VERY) commercial product. My use was long away and far ago, so I cannot comment on its' current capabilities or compare it to the alternatives. I can only mention the name as a reasonable starting point for use as the Key Word in advanced search in these (and possibly other) fora.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[HenryE]

Thanks everyone for the ton of information.

I think I might know what went wrong. I probably was a bit careless in changing the primary key name from FileNumber to DocketNumber, and, as this primary key was a foreign key in other tables, it didn't like that. I probably should have gone into all the linked tables first and deleted FileNumber from all of them, then deleted FileNumber from TblCases and then created DocketNumber. Instead, in a moment of bad judgement, I changed the actual names. Not good.

The short-term fix I did was to recreate an empty field FileNumber, and that seems to make it work, because now it can find FileNumber again, but I seriously doubt that this is a real fix to the problem.

Anybody know if there's a relatively definitive way of checking if an ADP that seems to be working well really is stable?

Thanks.

Henry

 

[cmmrfrds]

Where did you make the changes to the tables? In the ADP or in Enterprise Manager or Query Analyzer.

 

[HenryE]

In the ADP. I know that making changes in the ADP, vs in Enterprise Manager or Query Analyser makes a difference, but does anyone know exactly what the differences are?

Thanks.

Henry