Access driving me NUTS!!

[amateursRus]

Hi Folks,

I appreciate you may not consider this the correct forum but with the wealth of knowledge here I was hoping some kind soul would take pity on me...

I've a Delphi program which connects to an Access DB using ADO.
The program works fine, making multiple connections and re-connections, right up to the point where it tries to connect to a table called 'CareerRejectTemp'.

Now, I know this table exists because I've put specific code into the module which retrieves a list of DB tables into a stringlist, reads through this and - if it finds the required table (which I've hard-coded into the program as I know it's there) - connects and retrieve the contents.

Part of the code is enclosed...

sl:= TStringList.Create;
with ADOConnection1 do
GetTableNames(sl, False);

for i:= 0 to sl.Count-1 do
if (sl= 'CareerRejectTemp') then begin
UserSQL:= 'SELECT * FROM ' + sl;
AdoQ:= ADOQuery1;
Result:= AdoQueryGeneral(AdoQ, UserSQL);
end;

AdoQueryGeneral is just that - a generalised Query that runs all the other queries the program throws at it with no problems.

The error I'm getting is -

"Item cannot be found in the collection corresponding to the requested name or ordinal".

But if it wasn't there the "If (sl= 'CareerRejectTemp') then " wouldn't fire.....

Can anyone help?

 

[ssatech]

AmateursRus,

Did you create this Access BE to go with your Delphi program? Is this your Access BE or are you just connecting to an Access BE?
Is there an original Access FE to this Access BE DB? queries? forms etc.

Could it be this temp table "CareerRejectTemp" is created or deleted when certain code runs (e.g. Code that runs a query to delete the table and query that creates this table)
Its possible the reason you don't see the table is a certain code/query has not been run to create this table.

 

[amateursRus]

Thanks for the help.

"CareerRejectTemp" (despite the name!) is actually a permanent table in the Access Db.
The ridiculous situation is arising where I'm actually looking at the table (which has just had data inserted by an earlier piece of the code) and still getting this error message....

ANY ideas welcome!!

Regards

 

[jadams0173]

What field are you looking for in the table? I get that error most of the time when I'm looking for a field in the table does not exisit. Most of the time it's a typo on my part.

HTH

 

[cmmrfrds]

A couple of things. You say that you had just inserted a record. Is the Table/Record still locked?

Maybe there is an underlying error buried a little deeper. Try looping the errors collection. Something like.

Public Function yourfunction()
On Error GoTo Errhandler

Your code.

Exit Function
Errhandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.description
Debug.Print "err source = "; Err.Source
Next
End Function