local or sql tables?

I have Access XP and SQL 2000. I'm doing tests for my
upsizing to SQL. I've read that I should have tables in
my Access FE for data that don't change much. Like
tblCity, tblEmployee and such. However, I do have a
question on tables that don't get changed much, but are
linked to other tables.
For instance, tblEmployee only change if there is someone
new to the company (not often do I change that).
tblProvider is similar to tblEmployee where I only add if
we get a new Provider (which can happen once a yr or two).
These two tables do have one-many relationships to other
tables. tblProvider has a one-many relationship to two
tables. tblEmployee has a link (not a one-many) to the
tblIncident that house incidents for an employee.
Should these two tables be local because they dont get
changed much or be SQL linked tables because they have a
relationship?
Being a SQL Server guy and wanting to make sure that ALL the data is
properly maintained, primary/foreign keys are in place so that all the data
that we expect to be there exists, the appropriate security exists, and
proper database backups are taken....
I would put everything in SQL Server.
Keith
"ngan" <anonymous@.discussions.microsoft.com> wrote in message
news:494f01c4a180$6b0804f0$a501280a@.phx.gbl...
> I have Access XP and SQL 2000. I'm doing tests for my
> upsizing to SQL. I've read that I should have tables in
> my Access FE for data that don't change much. Like
> tblCity, tblEmployee and such. However, I do have a
> question on tables that don't get changed much, but are
> linked to other tables.
> For instance, tblEmployee only change if there is someone
> new to the company (not often do I change that).
> tblProvider is similar to tblEmployee where I only add if
> we get a new Provider (which can happen once a yr or two).
> These two tables do have one-many relationships to other
> tables. tblProvider has a one-many relationship to two
> tables. tblEmployee has a link (not a one-many) to the
> tblIncident that house incidents for an employee.
> Should these two tables be local because they dont get
> changed much or be SQL linked tables because they have a
> relationship?
|||is there any time you want to have local tables? like
temp tables?
Thanks for your input.
One more question:
When I did the upsizing, it made triggers for the
relationships, instead of DRI. Is it just best to
recreate the SQL tables and re-do the upsizing to use
DRI? Or is there a way to change the trigger to DRI on
the existing tables?
Ngan
>--Original Message--
>Being a SQL Server guy and wanting to make sure that ALL
the data is
>properly maintained, primary/foreign keys are in place so
that all the data
>that we expect to be there exists, the appropriate
security exists, and
>proper database backups are taken....
>I would put everything in SQL Server.
>--
>Keith
>
>"ngan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:494f01c4a180$6b0804f0$a501280a@.phx.gbl...
someone[vbcol=seagreen]
if[vbcol=seagreen]
two).
>.
>
|||What do you mean by "temp" tables? Temp tables are a concept within SQL
Server...these tables go away when the command that created them goes away.
I suspect that you mean temp working table...but I am not sure
how/why/where they would be used so I do not feel that I should say one way
or the other.
Yes, I would go for the PK/FK approach using DRI instead of the trigger
approach.
Keith
"Ngan" <anonymous@.discussions.microsoft.com> wrote in message
news:278701c4a196$ffbded60$a601280a@.phx.gbl...[vbcol=seagreen]
> is there any time you want to have local tables? like
> temp tables?
> Thanks for your input.
> One more question:
> When I did the upsizing, it made triggers for the
> relationships, instead of DRI. Is it just best to
> recreate the SQL tables and re-do the upsizing to use
> DRI? Or is there a way to change the trigger to DRI on
> the existing tables?
> Ngan
> the data is
> that all the data
> security exists, and
> message
> someone
> if
> two).
|||I was just trying to figure out when you would store a
table within the FE file. In any case, i have moved all
the tables to SQL like you suggested because of the
security and integrity issue...also because if I create a
website for this, I will need those other tables. so in
the end SQL will have to have all the tables.
Thanks for your help.
Ngan

>--Original Message--
>What do you mean by "temp" tables? Temp tables are a
concept within SQL
>Server...these tables go away when the command that
created them goes away.
>I suspect that you mean temp working table...but I am
not sure
>how/why/where they would be used so I do not feel that I
should say one way
>or the other.
>Yes, I would go for the PK/FK approach using DRI instead
of the trigger
>approach.
>--
>Keith
>
>"Ngan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:278701c4a196$ffbded60$a601280a@.phx.gbl...
ALL[vbcol=seagreen]
so[vbcol=seagreen]
my[vbcol=seagreen]
tables in[vbcol=seagreen]
are[vbcol=seagreen]
add[vbcol=seagreen]
other[vbcol=seagreen]
two[vbcol=seagreen]
the[vbcol=seagreen]
get[vbcol=seagreen]
have a
>.
>
|||Having done some Access/SQL Server development, I'll throw in my 2 cents.
If you have split your Access application, so all forms, reports and code
live on the client workstation. Then use either code and/or linked tables
pointing back to the data on SQL Server. It is perfectly acceptable to use
local tables in Access to store more static data. Typically you need to code
routines, that on application startup determine if the local table data
needs to be refreshed.
Steve
"Ngan" <anonymous@.discussions.microsoft.com> wrote in message
news:297001c4a1b1$8cc6d220$a601280a@.phx.gbl...[vbcol=seagreen]
> I was just trying to figure out when you would store a
> table within the FE file. In any case, i have moved all
> the tables to SQL like you suggested because of the
> security and integrity issue...also because if I create a
> website for this, I will need those other tables. so in
> the end SQL will have to have all the tables.
> Thanks for your help.
> Ngan
> concept within SQL
> created them goes away.
> not sure
> should say one way
> of the trigger
> message
> ALL
> so
> my
> tables in
> are
> add
> other
> two
> the
> get
> have a