SQL queries from VB

W

WhyDonuts

I have two tables (STUDENTS and SCHOOLS) with a many-to-many, so I created a
third table between them (APPLICATIONS), where the PK is an index of two
fields, the two FKs from the first two tables. (I know concatenated keys
violate 3NF, but bear with me.)

Currently, if a user creates an application through the form, and that
application already exists in the table, the form locks up, as the DB can't
complete the update (since a PK must be unique). Any attempt to change focus
simply elicits an error message that the last change can't be made due to
data integrity rules. The only option is to close the form without saving
the last (illegal) change.

Therefore, I had thought I would, during the BeforeUpdate event, query the
APPLPICATION table to see if a record with that StudentID and SchoolID
already exists, and if it does, VB would cancel the update. I am planning to
open the current DB, select a recordset, and check the length of the
recordset to see if anything was returned. If something was, I would cancel
the update.

Does anyone have a suggestion on a more direct approach?

Can anyone explain to me why "Database" is not available as a recognized
variable type in my IDE? I can Dim "recordset"s and "connection"s, but not
the "Database" variable type I read about in the help files (see the CodeDB
method, e.g.). Do I need to include some library or module that I am
currently without?

BTW, I am working in Access 2002.
 
B

Bob Hairgrove

I have two tables (STUDENTS and SCHOOLS) with a many-to-many, so I created a
third table between them (APPLICATIONS), where the PK is an index of two
fields, the two FKs from the first two tables. (I know concatenated keys
violate 3NF, but bear with me.)
Click to expand...

Why do you believe that this violates any normal form, much less 3NF?
Currently, if a user creates an application through the form, and that
application already exists in the table, the form locks up, as the DB can't
complete the update (since a PK must be unique). Any attempt to change focus
simply elicits an error message that the last change can't be made due to
data integrity rules. The only option is to close the form without saving
the last (illegal) change.

Therefore, I had thought I would, during the BeforeUpdate event, query the
APPLPICATION table to see if a record with that StudentID and SchoolID
already exists, and if it does, VB would cancel the update. I am planning to
open the current DB, select a recordset, and check the length of the
recordset to see if anything was returned. If something was, I would cancel
the update.

Does anyone have a suggestion on a more direct approach?
Click to expand...

See the next suggestion below WRT adding the reference, then you can
use:
If DCount("*","table_name","StudentID=" & student_id & _
" AND SchoolID=" & school_id) = 0 Then
' ok to add a row
Else
' issue warning, cancel the update and exit
End If

to see if a record already exists. For one-off queries such as this,
the much-criticized domain functions are actually much faster than the
overhead of setting object variables to recordsets. Besides, you don't
have to worry about closing them when you are done (i.e. one source
less of potential bugs).

Besides this, I would suggest putting the code in the BeforeInsert
event procedure, not BeforeUpdate -- unless you allow the user to
change IDs on existing records, of course.
Can anyone explain to me why "Database" is not available as a recognized
variable type in my IDE? I can Dim "recordset"s and "connection"s, but not
the "Database" variable type I read about in the help files (see the CodeDB
method, e.g.). Do I need to include some library or module that I am
currently without?

BTW, I am working in Access 2002.
Click to expand...

You need to add a reference to the DAO library. By default, Access
uses ADO unless you have the reference. In your code, you should then
write:

Dim db As DAO.Database
Dim rs As DAO.Recordset

etc., in oder to disambiguate the types common to both DAO and ADO.
 
Top