• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

URGENT!!! Problem with ADO and Access


I have an Access DB with several tables connected to my Delphi App through ADO.
I'm getting strange errors when my application is inserting data into the connected database.
One table has file information (filename, extension, path and dates) and other table
has extensions info ( extension and description).
The filetable has the extension field from the extensiontable as primary-foreign-key (PFK).
When inserting the file-info into the filetable example a .DOC or .XLS file I get the error
" You cannot add or change a record because a related record is required in table 'myext' ".
NOTE! I get this error after successfully insert around 400-700 OTHER .DOC or .XLS files.
Why ? Is it an ADO releated error/bug ?
This error is wery strange because as I said I can successfully insert 400-700 other .DOC or .XLS files
into the file-info table before getting this error. THE extension table has 2 records with both DOC and XLS
in the extension field. I remove the . of the extension before inserting the record.

  Tomas Helgi
Tomas Helgi Johannsson
Tomas Helgi Johannsson
  • 3
  • 2
1 Solution
It is not an ADO issue... it is probably something you missed in the construction of your SQL's
perhaps you are trying to insert duplicate values in the detail table, anyway it is a matter of master-detail tables. I cannot be sure what your problem is unless I see the code you are trying to execute.

You could trace your error by adding a memo to your form where the inserts are called... and add inside it each SQL -BEFORE- execution... this way you'll see the exact SQL statement where it crashes.
Something like:

dm.adoquery1.SQL.Add('insert into...');

After you get the exact SQL you may analyze it and try to execute it from Access's own Query editor or something like that (WinSQL... or something)

So as I see it the myext table is the master with primary key = extension
and the detail table holds the filenames and other info about the file, and is connected to the master table by extensionfield.
The error seems to be explaied by the fact that you are trying to insert a record about a specific file (in the detail table) that has an unknown extension... an extension that does not appear in the master table.
The solution for you is prety sure the one in my previous comment... the Memo idea.

If you still have difficulties paste here the code and we'll check it.
But I am quite sure there is some file that has no extension (or a weird extension) on your hard drive and you did not treat the case
Tomas Helgi JohannssonAuthor Commented:
That's the strange thing.
As I said before I can successfully insert 400-700 other DOC or XLS files  
into the file-info table before getting this error. THE extension table has 2 records with both DOC and XLS
in the extension field.
I log each record of unsuccessful insert into a logtable and there is nothing wrong  
with the extensions in which the tables are joined. I use TADOTable to insert the records.

  Tomas Helgi
Try to use TADOQuery...
Or try to paste here your code, perhaps we can make something out of it.
And try to log the sql statements in the memo ... not the table stuff.
Just try it... and maybe you'll find the error yourself.
Maybe the file has a dot after the .doc extension, o0r maybe a space...
you just have to use the Memo

Tomas Helgi JohannssonAuthor Commented:
Thanks calinutz.
Well it has something to do with ADO or problem with Quoted strings.
my record looks like

  TMyFileRec = record
    id: Integer;
    drive: string[50];
    name: string[100];
    path: string[255];
    created: TDateTime;
    modified: TDateTime;
    accessed: TDateTime;
    extension: string[50];

In stead of using

myfilesfilepath.Value := QuotedStr(rec.path); <<-- here the use of QuotedStr solved some part of the problem but not all.

so I used

myfiles.AppendRecord([....]) and also
used a temporary string variable to store the string from ExtractFilePath and the other Extract functions
when writing the file-info data to the record and passing that record to a DataModule function.
That did the trick and the logtable is empty after inserting the records.
The records in the logtable showed that if the filename or the filepath had space in the string
then the insert failed.  

   Tomas Helgi
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now