Cannot create foreign key in Access DB

[Wihiss]

Help!

I try to use Access DB via ADO (ADOX) under VC.Net.
I make the following steps:

1. Create an empty DB (using ADOX::Catalog->Create() method);

2. Append the tables to database (create a new ADOX::Table object and append it to ADOX::Tables collection);

3. Create a new relationship (a foreign key for any table). I use ADO::Connection->Execute method and try to use the SQL string like:
"ALTER TABLE [A] ADD FOREIGN KEY ( [A1] ) REFERENCES [B] ( [B1] )"


If I do a pause (2-3 sec) between poin 2 and 3 (using breakpoint or MessageBox), this scenario works correctly. But if I do not make any pauses between points 2 and 3, I get the error message:

"Error number 80040e37 Cannot find table or constraint."

Help!!! :confused:

[ITGURU]

Hello Wihiss,

If you are using MS-Access as backend and try to do any of the following activties in it then you have to give some interval for refreshing of data with required changes:

1. Delete any record from database Table.
2. Delete any table from database
3. Create new table in the database
4. Add/modify/delete any column from database table.
5. Any type of Changes which effect Database table except record level like insert/update.

We also faced this problem and when we try to find the reason we found that MS-Access Database is require some time to refresh after above activities and then we put some interval using DOEVENTS in VB application.

Hope this will solve your problem

[Wihiss]

Thank you for your answer, but I have found the reason of this problem. There were TWO connections to my database (stupid bug ;o), therefore this situation has occured.