Access 2003 BE with VB2005.net FE

[johnb]

Hi All

I'm unsure which approach I should use when designing a FE/BE. In the Access
BE I have 2 tables which are related by primary and foreign key with
referential integrity enforced. Should I create a one query in Access based
on those tables and link the VB.net FE to that query or should I create 2
queries and base the FE on them and enforce the referential intergity in the
VB FE. Or is the another approach?

Should I ask this question elsewhere?
TIA
johnb

 

[S.Clark]

The answer is... [drumroll]... It depends.

What are you going to do to the data in the FE?

View, Add, Edit, Delete or all of the above?

 

[johnb]

Hi Steve

The usual culprits. I'm Selecting, Inserting, Deleting, Updating and
printing data. Inserting only ocurres on the many side (subform).

cheers
johnb

The answer is... [drumroll]... It depends.

What are you going to do to the data in the FE?

View, Add, Edit, Delete or all of the above?

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

Hi All

I'm unsure which approach I should use when designing a FE/BE. In the Access
BE I have 2 tables which are related by primary and foreign key with
referential integrity enforced. Should I create a one query in Access based
on those tables and link the VB.net FE to that query or should I create 2
queries and base the FE on them and enforce the referential intergity in the
VB FE. Or is the another approach?

Should I ask this question elsewhere?
TIA
johnb

 

[S.Clark]

If records are only added to the child side of the R.I. relationship, then
there's no way for the R.I. to get wacked from the FE, given that your FE
properly allows inserts to only existing parent records. So, there shouldn't
be a concern there.

If R.I. is setup to prevent parent records from being deleted if they
contain child records, then I would enforce that in the VS data layer, rather
than count on an error message coming back from Access.

There may be other R.I. use cases I haven't explored, but I guess in any
case, I wouldn't rely on the Access error message coming back, but try it and
see what you get for free, then focus on rolling your own in the VS data
layer.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

Hi Steve

The usual culprits. I'm Selecting, Inserting, Deleting, Updating and
printing data. Inserting only ocurres on the many side (subform).

cheers
johnb

The answer is... [drumroll]... It depends.

What are you going to do to the data in the FE?

View, Add, Edit, Delete or all of the above?

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

Hi All

I'm unsure which approach I should use when designing a FE/BE. In the Access
BE I have 2 tables which are related by primary and foreign key with
referential integrity enforced. Should I create a one query in Access based
on those tables and link the VB.net FE to that query or should I create 2
queries and base the FE on them and enforce the referential intergity in the
VB FE. Or is the another approach?

Should I ask this question elsewhere?
TIA
johnb

 

[johnb]

Cheers Steve
So if understand this correctly. Its ok connect directly to the Access BE
tables but do all the R.I together with data validation in the VB.net FE.
That the right approach?

johnb

If records are only added to the child side of the R.I. relationship, then
there's no way for the R.I. to get wacked from the FE, given that your FE
properly allows inserts to only existing parent records. So, there shouldn't
be a concern there.

If R.I. is setup to prevent parent records from being deleted if they
contain child records, then I would enforce that in the VS data layer, rather
than count on an error message coming back from Access.

There may be other R.I. use cases I haven't explored, but I guess in any
case, I wouldn't rely on the Access error message coming back, but try it and
see what you get for free, then focus on rolling your own in the VS data
layer.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

Hi Steve

The usual culprits. I'm Selecting, Inserting, Deleting, Updating and
printing data. Inserting only ocurres on the many side (subform).

cheers
johnb

The answer is... [drumroll]... It depends.

What are you going to do to the data in the FE?

View, Add, Edit, Delete or all of the above?

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



:

Hi All

I'm unsure which approach I should use when designing a FE/BE. In the Access
BE I have 2 tables which are related by primary and foreign key with
referential integrity enforced. Should I create a one query in Access based
on those tables and link the VB.net FE to that query or should I create 2
queries and base the FE on them and enforce the referential intergity in the
VB FE. Or is the another approach?

Should I ask this question elsewhere?
TIA
johnb

 

[S.Clark]

I don't know if a direct connection to the table is the right thing to do...
maybe a SQL string or saved query to extract only the desired data. But, as
far as the R.I. is concerned, I'd probably prefer to have control over that
in the FE.

You could have the RI setup in the database, but that's only for cases where
you're actually using Access to clean up some data.

--

Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

Cheers Steve
So if understand this correctly. Its ok connect directly to the Access BE
tables but do all the R.I together with data validation in the VB.net FE.
That the right approach?

johnb

If records are only added to the child side of the R.I. relationship, then
there's no way for the R.I. to get wacked from the FE, given that your FE
properly allows inserts to only existing parent records. So, there shouldn't
be a concern there.

If R.I. is setup to prevent parent records from being deleted if they
contain child records, then I would enforce that in the VS data layer, rather
than count on an error message coming back from Access.

There may be other R.I. use cases I haven't explored, but I guess in any
case, I wouldn't rely on the Access error message coming back, but try it and
see what you get for free, then focus on rolling your own in the VS data
layer.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

Hi Steve

The usual culprits. I'm Selecting, Inserting, Deleting, Updating and
printing data. Inserting only ocurres on the many side (subform).

cheers
johnb

:

The answer is... [drumroll]... It depends.

What are you going to do to the data in the FE?

View, Add, Edit, Delete or all of the above?

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



:

Hi All

I'm unsure which approach I should use when designing a FE/BE. In the Access
BE I have 2 tables which are related by primary and foreign key with
referential integrity enforced. Should I create a one query in Access based
on those tables and link the VB.net FE to that query or should I create 2
queries and base the FE on them and enforce the referential intergity in the
VB FE. Or is the another approach?

Should I ask this question elsewhere?
TIA
johnb

 

[johnb]

Hi Steve

Thank you for your assistance its been most helpful.

Regards
johnb

I don't know if a direct connection to the table is the right thing to do...
maybe a SQL string or saved query to extract only the desired data. But, as
far as the R.I. is concerned, I'd probably prefer to have control over that
in the FE.

You could have the RI setup in the database, but that's only for cases where
you're actually using Access to clean up some data.

--

Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

Cheers Steve
So if understand this correctly. Its ok connect directly to the Access BE
tables but do all the R.I together with data validation in the VB.net FE.
That the right approach?

johnb

If records are only added to the child side of the R.I. relationship, then
there's no way for the R.I. to get wacked from the FE, given that your FE
properly allows inserts to only existing parent records. So, there shouldn't
be a concern there.

If R.I. is setup to prevent parent records from being deleted if they
contain child records, then I would enforce that in the VS data layer, rather
than count on an error message coming back from Access.

There may be other R.I. use cases I haven't explored, but I guess in any
case, I wouldn't rely on the Access error message coming back, but try it and
see what you get for free, then focus on rolling your own in the VS data
layer.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

:

Hi Steve

The usual culprits. I'm Selecting, Inserting, Deleting, Updating and
printing data. Inserting only ocurres on the many side (subform).

cheers
johnb

:

The answer is... [drumroll]... It depends.

What are you going to do to the data in the FE?

View, Add, Edit, Delete or all of the above?

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



:

Hi All

I'm unsure which approach I should use when designing a FE/BE. In the Access
BE I have 2 tables which are related by primary and foreign key with
referential integrity enforced. Should I create a one query in Access based
on those tables and link the VB.net FE to that query or should I create 2
queries and base the FE on them and enforce the referential intergity in the
VB FE. Or is the another approach?

Should I ask this question elsewhere?
TIA
johnb