Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

VB/Access Referential Integrity

Experts,

I just set up a relationship in Access between two tables that enforces one-to-many referential integrity.  When I tried to delete the parent record from the query window, I was warned that I would leave orphans in the child table, as I should be.

When I run the same query from my VB code, it doesn't delete the parent record (which is good), but it never throws any kind of error telling me I'm about to screw up the database.  Is there any way I can have the VB program let me know when a potential violation of referential integrity is about to take place?
0
NigelRocks
Asked:
NigelRocks
  • 2
  • 2
2 Solutions
 
HooKooDooKuCommented:
When you executed the query, the execute function should have at least returned something letting you know that nothing is getting changed.  I know in the case of DAO, the DB.Execute( SQL$ ) function returns a number indicating the number of records modified.
The other possibility might be bad error handling (like an "On Error Resume Next" that is catching any error getting thrown).
0
 
NigelRocksAuthor Commented:
No error is generated to be caught in "On Error".

As far as the return value on an execute, does anyone know how to do that in ADO?

0
 
HooKooDooKuCommented:
If you are using ADODB (Microsoft ActiveX Data Objects X.X Library),
ADODB.Execute has a parameter for which the number of records affected is passed by reference.

...
Dim RecordsModified as long
Dim DB as ADODB
Dim SQL$
...
DB.Execute( SQL$,RecordsModified, adCmdText )
if RecordsModified = 0 then
    MsgBox "Nothing was modified"
End If
...

Open in new window

0
 
NigelRocksAuthor Commented:
I hate to bother you again, but I don't see how this works in ADO.  I don't see that ".execute" has a return value.  What am I missing?
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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