I have 2 questions about 2 related tables (1 to many, 1 "group" has many
"people"). One table is "people" (basic contact information exported from
another database, ACS, into an Access table) and the other table is "groups"
(basic group information of each group, that "people" belong to).

Unfortunately I can't update "people" information, I must use ACS to do
that, and then export a new Access table. I then import the new table
("people1") into Access and rename it "people" to get updated information for
the various reports that I use. Is there a way, via a macro or visual basic,
to automate the above update process?

Additionally, I would like to display on the main switchboard: 1. the date
created and/or date modified of each table (not individual records) and 2.
the total number of records in each tables . Thank you for your help.
--
Chris F

Chris Fillar wrote:
> I have 2 questions about 2 related tables (1 to many, 1 "group" has many
> "people"). One table is "people" (basic contact information exported from
> another database, ACS, into an Access table) and the other table is "groups"
> (basic group information of each group, that "people" belong to).
>
> Unfortunately I can't update "people" information, I must use ACS to do
> that, and then export a new Access table. I then import the new table
> ("people1") into Access and rename it "people" to get updated information for
> the various reports that I use. Is there a way, via a macro or visual basic,
> to automate the above update process?

If you delete the table "People", then rename another table "People", how are
you keeping the table "Groups" in sync? What is the primary key in "People"?
Will the PK for "John Smith" remain the same each time you import new
(updated)" People" information from ACS?

As to how to do it, one way might be to import the new data into a temp table,
delete the data in the "People" table, then append the data from the temp table
to the "People" table. Provided you can maintain the relationship with the
table "Groups"

>
> Additionally, I would like to display on the main switchboard: 1. the date
> created and/or date modified of each table (not individual records) and 2.
> the total number of records in each tables . Thank you for your help.

See this site:

http://www.mvps.org/access/queries/qry0002.htm


Modify the "Table" query Where clause to look for the tables PEOPLE and GROUPS.
Add the two fields Created and Modified fields. (I would use a recordset)
Push the dates into unbound text boxes on the switchboard form.

The number of records is just as easy. Open a recordset on each the tables,
..MoveLast, push the .Recordcount into a unbound text box on the form.

HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Steve,
Thank you so much for your quick reply. First, to answer your questions:
I’m using a field “IndSmallGroup” as the primary key between the 2 tables;
and the table “Groups” doesn’t have to go through the upgrade process as I
have to go through with “People”. I can update “Groups” in ACCESS so the
data stays in sync. I do have to reestablish the relationship with “People”,
after I have imported the new “People” table. Although I did a little
programming with earlier versions of DBASE, I’m new at programming ACCESS and
just now starting to learn visual basic, so basically, I’m lost. How would
I automate the process of importing the temp table, deleting the “People”
data, and append the temp table? Could you suggest code for a macro or VB?
Also, I’m not familiar with using a recordset. Where would I use it and how
would I push the dates. Thanks again, Chris

--
Chris F


"SteveS" <"sanfu at techie dot com" wrote:

>
> Chris Fillar wrote:
> > I have 2 questions about 2 related tables (1 to many, 1 "group" has many
> > "people"). One table is "people" (basic contact information exported from
> > another database, ACS, into an Access table) and the other table is "groups"
> > (basic group information of each group, that "people" belong to).
> >
> > Unfortunately I can't update "people" information, I must use ACS to do
> > that, and then export a new Access table. I then import the new table
> > ("people1") into Access and rename it "people" to get updated information for
> > the various reports that I use. Is there a way, via a macro or visual basic,
> > to automate the above update process?
>
> If you delete the table "People", then rename another table "People", how are
> you keeping the table "Groups" in sync? What is the primary key in "People"?
> Will the PK for "John Smith" remain the same each time you import new
> (updated)" People" information from ACS?
>
> As to how to do it, one way might be to import the new data into a temp table,
> delete the data in the "People" table, then append the data from the temp table
> to the "People" table. Provided you can maintain the relationship with the
> table "Groups"
>
> >
> > Additionally, I would like to display on the main switchboard: 1. the date
> > created and/or date modified of each table (not individual records) and 2.
> > the total number of records in each tables . Thank you for your help.
>
> See this site:
>
> http://www.mvps.org/access/queries/qry0002.htm
>
>
> Modify the "Table" query Where clause to look for the tables PEOPLE and GROUPS.
> Add the two fields Created and Modified fields. (I would use a recordset)
> Push the dates into unbound text boxes on the switchboard form.
>
> The number of records is just as easy. Open a recordset on each the tables,
> ..MoveLast, push the .Recordcount into a unbound text box on the form.
>
> HTH
> --
> Steve S.
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came, I saw, I stuck around.)
>

How do you tell one "John Smith" from another "John Smith"?

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Chris Fillar" wrote:

> Steve,
> Thank you so much for your quick reply. First, to answer your questions:
> I’m using a field “IndSmallGroup” as the primary key between the 2 tables;
> and the table “Groups” doesn’t have to go through the upgrade process as I
> have to go through with “People”. I can update “Groups” in ACCESS so the
> data stays in sync. I do have to reestablish the relationship with “People”,
> after I have imported the new “People” table. Although I did a little
> programming with earlier versions of DBASE, I’m new at programming ACCESS and
> just now starting to learn visual basic, so basically, I’m lost. How would
> I automate the process of importing the temp table, deleting the “People”
> data, and append the temp table? Could you suggest code for a macro or VB?
> Also, I’m not familiar with using a recordset. Where would I use it and how
> would I push the dates. Thanks again, Chris
>
> --
> Chris F
>
>
> "SteveS" <"sanfu at techie dot com" wrote:
>
> >
> > Chris Fillar wrote:
> > > I have 2 questions about 2 related tables (1 to many, 1 "group" has many
> > > "people"). One table is "people" (basic contact information exported from
> > > another database, ACS, into an Access table) and the other table is "groups"
> > > (basic group information of each group, that "people" belong to).
> > >
> > > Unfortunately I can't update "people" information, I must use ACS to do
> > > that, and then export a new Access table. I then import the new table
> > > ("people1") into Access and rename it "people" to get updated information for
> > > the various reports that I use. Is there a way, via a macro or visual basic,
> > > to automate the above update process?
> >
> > If you delete the table "People", then rename another table "People", how are
> > you keeping the table "Groups" in sync? What is the primary key in "People"?
> > Will the PK for "John Smith" remain the same each time you import new
> > (updated)" People" information from ACS?
> >
> > As to how to do it, one way might be to import the new data into a temp table,
> > delete the data in the "People" table, then append the data from the temp table
> > to the "People" table. Provided you can maintain the relationship with the
> > table "Groups"
> >
> > >
> > > Additionally, I would like to display on the main switchboard: 1. the date
> > > created and/or date modified of each table (not individual records) and 2.
> > > the total number of records in each tables . Thank you for your help.
> >
> > See this site:
> >
> > http://www.mvps.org/access/queries/qry0002.htm
> >
> >
> > Modify the "Table" query Where clause to look for the tables PEOPLE and GROUPS.
> > Add the two fields Created and Modified fields. (I would use a recordset)
> > Push the dates into unbound text boxes on the switchboard form.
> >
> > The number of records is just as easy. Open a recordset on each the tables,
> > ..MoveLast, push the .Recordcount into a unbound text box on the form.
> >
> > HTH
> > --
> > Steve S.
> > --------------------------------
> > "Veni, Vidi, Velcro"
> > (I came, I saw, I stuck around.)
> >

The Group data is small, less than 50 groups, so I was forced to control the
John Smith issue manually, because the person running the ACS "People" data
wouldn't let me use a numeric system for “IndSmallGroup". The don't think I
care how many John Smiths there are in "People" because they will have the
"IndSmallGroup" that will put them in the correct "Group". Am I missing
something?
--
Chris F


"SteveS" wrote:

> How do you tell one "John Smith" from another "John Smith"?
>
> --
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
>
>
> "Chris Fillar" wrote:
>
> > Steve,
> > Thank you so much for your quick reply. First, to answer your questions:
> > I’m using a field “IndSmallGroup” as the primary key between the 2 tables;
> > and the table “Groups” doesn’t have to go through the upgrade process as I
> > have to go through with “People”. I can update “Groups” in ACCESS so the
> > data stays in sync. I do have to reestablish the relationship with “People”,
> > after I have imported the new “People” table. Although I did a little
> > programming with earlier versions of DBASE, I’m new at programming ACCESS and
> > just now starting to learn visual basic, so basically, I’m lost. How would
> > I automate the process of importing the temp table, deleting the “People”
> > data, and append the temp table? Could you suggest code for a macro or VB?
> > Also, I’m not familiar with using a recordset. Where would I use it and how
> > would I push the dates. Thanks again, Chris
> >
> > --
> > Chris F
> >
> >
> > "SteveS" <"sanfu at techie dot com" wrote:
> >
> > >
> > > Chris Fillar wrote:
> > > > I have 2 questions about 2 related tables (1 to many, 1 "group" has many
> > > > "people"). One table is "people" (basic contact information exported from
> > > > another database, ACS, into an Access table) and the other table is "groups"
> > > > (basic group information of each group, that "people" belong to).
> > > >
> > > > Unfortunately I can't update "people" information, I must use ACS to do
> > > > that, and then export a new Access table. I then import the new table
> > > > ("people1") into Access and rename it "people" to get updated information for
> > > > the various reports that I use. Is there a way, via a macro or visual basic,
> > > > to automate the above update process?
> > >
> > > If you delete the table "People", then rename another table "People", how are
> > > you keeping the table "Groups" in sync? What is the primary key in "People"?
> > > Will the PK for "John Smith" remain the same each time you import new
> > > (updated)" People" information from ACS?
> > >
> > > As to how to do it, one way might be to import the new data into a temp table,
> > > delete the data in the "People" table, then append the data from the temp table
> > > to the "People" table. Provided you can maintain the relationship with the
> > > table "Groups"
> > >
> > > >
> > > > Additionally, I would like to display on the main switchboard: 1. the date
> > > > created and/or date modified of each table (not individual records) and 2.
> > > > the total number of records in each tables . Thank you for your help.
> > >
> > > See this site:
> > >
> > > http://www.mvps.org/access/queries/qry0002.htm
> > >
> > >
> > > Modify the "Table" query Where clause to look for the tables PEOPLE and GROUPS.
> > > Add the two fields Created and Modified fields. (I would use a recordset)
> > > Push the dates into unbound text boxes on the switchboard form.
> > >
> > > The number of records is just as easy. Open a recordset on each the tables,
> > > ..MoveLast, push the .Recordcount into a unbound text box on the form.
> > >
> > > HTH
> > > --
> > > Steve S.
> > > --------------------------------
> > > "Veni, Vidi, Velcro"
> > > (I came, I saw, I stuck around.)
> > >

I was hoping that there was a unique identifier (for each person) for the
"People" table. Then you could import "new" ACS people data to a temp table
and run code to append only new people (that are not already in the "People"
table) into the "People" table.

What info do you get (have) for the "People" table?


If you want to delete the "People"table each time there is an update to the
"People" table, you can't have a defined relationship (in the relationship
window) between the "People" and "Group" tables. Of course you can have them
set up in saved queries and VB code.

I still think you should create the table ("People") *once* and delete the
records/ import new records rather than recreate the "People" table each time.

If you want examples of Make Table code, search Google groups for "Make
table".


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Chris Fillar" wrote:

> The Group data is small, less than 50 groups, so I was forced to control the
> John Smith issue manually, because the person running the ACS "People" data
> wouldn't let me use a numeric system for “IndSmallGroup". The don't think I
> care how many John Smiths there are in "People" because they will have the
> "IndSmallGroup" that will put them in the correct "Group". Am I missing
> something?
> --
> Chris F
>
>
> "SteveS" wrote:
>
> > How do you tell one "John Smith" from another "John Smith"?
> >
> > --
> > Steve S
> > --------------------------------
> > "Veni, Vidi, Velcro"
> > (I came; I saw; I stuck around.)
> >
> >
> > "Chris Fillar" wrote:
> >
> > > Steve,
> > > Thank you so much for your quick reply. First, to answer your questions:
> > > I’m using a field “IndSmallGroup” as the primary key between the 2 tables;
> > > and the table “Groups” doesn’t have to go through the upgrade process as I
> > > have to go through with “People”. I can update “Groups” in ACCESS so the
> > > data stays in sync. I do have to reestablish the relationship with “People”,
> > > after I have imported the new “People” table. Although I did a little
> > > programming with earlier versions of DBASE, I’m new at programming ACCESS and
> > > just now starting to learn visual basic, so basically, I’m lost. How would
> > > I automate the process of importing the temp table, deleting the “People”
> > > data, and append the temp table? Could you suggest code for a macro or VB?
> > > Also, I’m not familiar with using a recordset. Where would I use it and how
> > > would I push the dates. Thanks again, Chris
> > >
> > > --
> > > Chris F
> > >
> > >
> > > "SteveS" <"sanfu at techie dot com" wrote:
> > >
> > > >
> > > > Chris Fillar wrote:
> > > > > I have 2 questions about 2 related tables (1 to many, 1 "group" has many
> > > > > "people"). One table is "people" (basic contact information exported from
> > > > > another database, ACS, into an Access table) and the other table is "groups"
> > > > > (basic group information of each group, that "people" belong to).
> > > > >
> > > > > Unfortunately I can't update "people" information, I must use ACS to do
> > > > > that, and then export a new Access table. I then import the new table
> > > > > ("people1") into Access and rename it "people" to get updated information for
> > > > > the various reports that I use. Is there a way, via a macro or visual basic,
> > > > > to automate the above update process?
> > > >
> > > > If you delete the table "People", then rename another table "People", how are
> > > > you keeping the table "Groups" in sync? What is the primary key in "People"?
> > > > Will the PK for "John Smith" remain the same each time you import new
> > > > (updated)" People" information from ACS?
> > > >
> > > > As to how to do it, one way might be to import the new data into a temp table,
> > > > delete the data in the "People" table, then append the data from the temp table
> > > > to the "People" table. Provided you can maintain the relationship with the
> > > > table "Groups"
> > > >
> > > > >
> > > > > Additionally, I would like to display on the main switchboard: 1. the date
> > > > > created and/or date modified of each table (not individual records) and 2.
> > > > > the total number of records in each tables . Thank you for your help.
> > > >
> > > > See this site:
> > > >
> > > > http://www.mvps.org/access/queries/qry0002.htm
> > > >
> > > >
> > > > Modify the "Table" query Where clause to look for the tables PEOPLE and GROUPS.
> > > > Add the two fields Created and Modified fields. (I would use a recordset)
> > > > Push the dates into unbound text boxes on the switchboard form.
> > > >
> > > > The number of records is just as easy. Open a recordset on each the tables,
> > > > ..MoveLast, push the .Recordcount into a unbound text box on the form.
> > > >
> > > > HTH
> > > > --
> > > > Steve S.
> > > > --------------------------------
> > > > "Veni, Vidi, Velcro"
> > > > (I came, I saw, I stuck around.)
> > > >

First, the “People” table has 46 fields of personal data such as emails,
phone numbers, address, children, etc. There are only 4 of those 46 that
contain group membership information that relate to the groups in the
“Groups” table, but I use the other personal individual information in
various reports. Updating any information in People must be done outside of
ACCESS, in the ACS database, and that’s why I didn’t see an advantage to keep
the “People” table intact. I thought since the ACS database is essentially
the “master”, why not just remove and replace. Is there an advantage to
updating, rather than replacing the ACCESS table? Although most individuals
remain somewhat stable, there are constant deletions, additions and changes.

I’ve used your earlier suggestion of ”import the new data into a temp
table, delete the data in the "People" table, then append the data from the
temp table to the "People" table. Provided you can maintain the relationship
with the table "Groups"” successfully and it has maintained the relationship
between the tables. I even automated it somewhat with a append query, but
that has stopped working correctly, since I followed another suggestion to
have a front end and back end with linked tables.
Is there another way to automate the new data to a temp file, delete the
data, and then add the data via a micro or VB?

Would you explain “Modify the "Table" query Where clause to look for the
tables PEOPLE and GROUPS. Add the two fields Created and Modified fields. (I
would use a recordset) Push the dates into unbound text boxes on the
switchboard form.”. Are the 2 fields you mentioned for every record. I was
looking for the last date that “People” table was last modified for example,
rather than an individual record, or will what you suggested return 1 value?


Also, would you please explain further the process for: “
The number of records is just as easy. Open a recordset on each the tables,
...MoveLast, push the .Recordcount into a unbound text box on the form. “?

Again, thank you for your help.
Chris

--
Chris F


"SteveS" wrote:

> I was hoping that there was a unique identifier (for each person) for the
> "People" table. Then you could import "new" ACS people data to a temp table
> and run code to append only new people (that are not already in the "People"
> table) into the "People" table.
>
> What info do you get (have) for the "People" table?
>
>
> If you want to delete the "People"table each time there is an update to the
> "People" table, you can't have a defined relationship (in the relationship
> window) between the "People" and "Group" tables. Of course you can have them
> set up in saved queries and VB code.
>
> I still think you should create the table ("People") *once* and delete the
> records/ import new records rather than recreate the "People" table each time.
>
> If you want examples of Make Table code, search Google groups for "Make
> table".
>
>
> --
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
>
>
> "Chris Fillar" wrote:
>
> > The Group data is small, less than 50 groups, so I was forced to control the
> > John Smith issue manually, because the person running the ACS "People" data
> > wouldn't let me use a numeric system for “IndSmallGroup". The don't think I
> > care how many John Smiths there are in "People" because they will have the
> > "IndSmallGroup" that will put them in the correct "Group". Am I missing
> > something?
> > --
> > Chris F
> >
> >
> > "SteveS" wrote:
> >
> > > How do you tell one "John Smith" from another "John Smith"?
> > >
> > > --
> > > Steve S
> > > --------------------------------
> > > "Veni, Vidi, Velcro"
> > > (I came; I saw; I stuck around.)
> > >
> > >
> > > "Chris Fillar" wrote:
> > >
> > > > Steve,
> > > > Thank you so much for your quick reply. First, to answer your questions:
> > > > I’m using a field “IndSmallGroup” as the primary key between the 2 tables;
> > > > and the table “Groups” doesn’t have to go through the upgrade process as I
> > > > have to go through with “People”. I can update “Groups” in ACCESS so the
> > > > data stays in sync. I do have to reestablish the relationship with “People”,
> > > > after I have imported the new “People” table. Although I did a little
> > > > programming with earlier versions of DBASE, I’m new at programming ACCESS and
> > > > just now starting to learn visual basic, so basically, I’m lost. How would
> > > > I automate the process of importing the temp table, deleting the “People”
> > > > data, and append the temp table? Could you suggest code for a macro or VB?
> > > > Also, I’m not familiar with using a recordset. Where would I use it and how
> > > > would I push the dates. Thanks again, Chris
> > > >
> > > > --
> > > > Chris F
> > > >
> > > >
> > > > "SteveS" <"sanfu at techie dot com" wrote:
> > > >
> > > > >
> > > > > Chris Fillar wrote:
> > > > > > I have 2 questions about 2 related tables (1 to many, 1 "group" has many
> > > > > > "people"). One table is "people" (basic contact information exported from
> > > > > > another database, ACS, into an Access table) and the other table is "groups"
> > > > > > (basic group information of each group, that "people" belong to).
> > > > > >
> > > > > > Unfortunately I can't update "people" information, I must use ACS to do
> > > > > > that, and then export a new Access table. I then import the new table
> > > > > > ("people1") into Access and rename it "people" to get updated information for
> > > > > > the various reports that I use. Is there a way, via a macro or visual basic,
> > > > > > to automate the above update process?
> > > > >
> > > > > If you delete the table "People", then rename another table "People", how are
> > > > > you keeping the table "Groups" in sync? What is the primary key in "People"?
> > > > > Will the PK for "John Smith" remain the same each time you import new
> > > > > (updated)" People" information from ACS?
> > > > >
> > > > > As to how to do it, one way might be to import the new data into a temp table,
> > > > > delete the data in the "People" table, then append the data from the temp table
> > > > > to the "People" table. Provided you can maintain the relationship with the
> > > > > table "Groups"
> > > > >
> > > > > >
> > > > > > Additionally, I would like to display on the main switchboard: 1. the date
> > > > > > created and/or date modified of each table (not individual records) and 2.
> > > > > > the total number of records in each tables . Thank you for your help.
> > > > >
> > > > > See this site:
> > > > >
> > > > > http://www.mvps.org/access/queries/qry0002.htm
> > > > >
> > > > >
> > > > > Modify the "Table" query Where clause to look for the tables PEOPLE and GROUPS.
> > > > > Add the two fields Created and Modified fields. (I would use a recordset)
> > > > > Push the dates into unbound text boxes on the switchboard form.
> > > > >
> > > > > The number of records is just as easy. Open a recordset on each the tables,
> > > > > ..MoveLast, push the .Recordcount into a unbound text box on the form.
> > > > >
> > > > > HTH
> > > > > --
> > > > > Steve S.
> > > > > --------------------------------
> > > > > "Veni, Vidi, Velcro"
> > > > > (I came, I saw, I stuck around.)
> > > > >

I was wondering/hoping there was a unique identifier, like Employee number,
that could be used to identify each person. You said "Delete the "People"
table, import the new data and rename the table". This destroys the link
between the "Group" and "People" tables. You manually establish the link
between the tables each time. That might be acceptable if the "People" table
is under 50 people, but if there are more than 500, it is going to tabke a
lot of time - not to mention the possibility of errors.

If there was a unique identifier, the code could loop thru the new data in
the Temp table, update the"People" table, compare the two tables to add the
new people to
"People" table and delete the people from the "People" table that were not
in the temp table.

Deleting a table then recreating it will probably corrupt your database at
the worst time you could think of.

--
Having a FE/BE is the right way to go - it is easy to make changes to the
code/forms/queries/reports in a test mdb, then reattach the production data
(BE).
Splitting the database was not the cause of the code not working.

--
Your last two paragraphs in the first post is about the table
Creation/Modification dates/times. I pasted a link a "The Access Web" page
that has the start of some code to get the creation/modification dates/times.
Note that the modify date/time is when the *structure* was modified, not when
the last time the table data was updated.

There is a hidden system table that stores this info (MsysObjects). The
following is how you get the dates/time and the record count.....

Asumptions: there is a form named "Switcboard" and two tables "Group" and
"People".

On the form "Switchboard", add 6 unbound text boxes. Name them:
(ub stands for unbound)
ubGroupCreateDte
ubGroupModifyDte
ubGroupRecCount

ubPeopleCreateDte
ubPeopleModifyDte
ubPeopleRecCount

Add a button to the form.

Modify the caption property of the 6 unbound text boxes to show the Create
date, modify date and num of records for each table.

For the button, create an On Click event. It should look something like this:

Private Sub Command0_Click()
GetTableDatesRecs
End Sub

Paste this code after the button click code:
(watch for line wrap)

'********beg code *********
Sub GetTableDatesRecs()
Dim rst As dao.Recordset, rst1 As dao.Recordset
Dim strSQL As String
Dim bDebug As Boolean

'set this to True to see the strSQL string
bDebug = False

'create the query string
strSQL = "SELECT MSysObjects.Name as tblName, DateCreate, DateUpdate"
strSQL = strSQL & " FROM MsysObjects"
strSQL = strSQL & " WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4) <>
'Msys')"
strSQL = strSQL & " AND ((MSysObjects.Type)=1 or (MSysObjects.Type)=6)"
'could also limit the recordset to the two tables
' by adding
' strSQL = strSQL & " AND ((MSysObjects.Name )= 'Group' or
(MSysObjects.Name)= 'People')"
strSQL = strSQL & " ORDER BY MSysObjects.Name;"

'for debugging
If bDebug Then
MsgBox strSQL
Exit Sub
End If

'open the recordset
Set rst = CurrentDb.OpenRecordset(strSQL)

' check of there are records
If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
Do While Not rst.EOF
Select Case rst!tblName
Case "Group" 'table name
Forms!Switchboard.ubGroupCreateDte = rst!DateCreate
Forms!Switchboard.ubGroupModifyDte = rst!DateUpdate
'now open the table to get the record count
Set rst1 = CurrentDb.OpenRecordset("Group")
If Not rst1.BOF And Not rst1.EOF Then
rst1.MoveLast
Forms!Switchboard.ubGroupRecCount = rst1.RecordCount
rst1.Close
End If

Case "People" 'table name
Forms!Switchboard.ubPeopleCreateDte = rst!DateCreate
Forms!Switchboard.ubPeopleModifyDte = rst!DateUpdate
' open the table to get the record count
Set rst1 = CurrentDb.OpenRecordset("People")
If Not rst1.BOF And Not rst1.EOF Then
rst1.MoveLast
Forms!Switchboard.ubPeopleRecCount = rst1.RecordCount
rst1.Close
End If

End Select
' move to the next record in recordset 'RST'
rst.MoveNext
Loop
End If

'cleanup
rst.Close
Set rst = Nothing
Set rst1 = Nothing

End Sub
'********end code *********

Save the code and save the form. Click the button. You could also call the
"GetTableDatesRecs" code in the Form_Activate() event.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Chris Fillar" wrote:

> First, the “People” table has 46 fields of personal data such as emails,
> phone numbers, address, children, etc. There are only 4 of those 46 that
> contain group membership information that relate to the groups in the
> “Groups” table, but I use the other personal individual information in
> various reports. Updating any information in People must be done outside of
> ACCESS, in the ACS database, and that’s why I didn’t see an advantage to keep
> the “People” table intact. I thought since the ACS database is essentially
> the “master”, why not just remove and replace. Is there an advantage to
> updating, rather than replacing the ACCESS table? Although most individuals
> remain somewhat stable, there are constant deletions, additions and changes.
>
> I’ve used your earlier suggestion of ”import the new data into a temp
> table, delete the data in the "People" table, then append the data from the
> temp table to the "People" table. Provided you can maintain the relationship
> with the table "Groups"” successfully and it has maintained the relationship
> between the tables. I even automated it somewhat with a append query, but
> that has stopped working correctly, since I followed another suggestion to
> have a front end and back end with linked tables.
> Is there another way to automate the new data to a temp file, delete the
> data, and then add the data via a micro or VB?
>
> Would you explain “Modify the "Table" query Where clause to look for the
> tables PEOPLE and GROUPS. Add the two fields Created and Modified fields. (I
> would use a recordset) Push the dates into unbound text boxes on the
> switchboard form.”. Are the 2 fields you mentioned for every record. I was
> looking for the last date that “People” table was last modified for example,
> rather than an individual record, or will what you suggested return 1 value?
>
>
> Also, would you please explain further the process for: “
> The number of records is just as easy. Open a recordset on each the tables,
> ..MoveLast, push the .Recordcount into a unbound text box on the form. “?
>
> Again, thank you for your help.
> Chris
>
> --
> Chris F
>
>
> "SteveS" wrote:
>
> > I was hoping that there was a unique identifier (for each person) for the
> > "People" table. Then you could import "new" ACS people data to a temp table
> > and run code to append only new people (that are not already in the "People"
> > table) into the "People" table.
> >
> > What info do you get (have) for the "People" table?
> >
> >
> > If you want to delete the "People"table each time there is an update to the
> > "People" table, you can't have a defined relationship (in the relationship
> > window) between the "People" and "Group" tables. Of course you can have them
> > set up in saved queries and VB code.
> >
> > I still think you should create the table ("People") *once* and delete the
> > records/ import new records rather than recreate the "People" table each time.
> >
> > If you want examples of Make Table code, search Google groups for "Make
> > table".
> >
> >
> > --
> > Steve S
> > --------------------------------
> > "Veni, Vidi, Velcro"
> > (I came; I saw; I stuck around.)
> >
'***** SNIP ********************

Thank you so much for all your efforts! I’m trying to get an unique
identifier from the ACS people, to do the updates as you suggested. For the
time being, I’m not deleting the tables, I’m deleting the data and then
pasting in the new information.

I tried to apply your code with the following exceptions:
1. Switchboard is really Main Switchboard so in 6 locations, I changed
Forms!Switchboard. to Forms![Main Switchboard].
2. Groups is really St. Stephen’s Small Groups so I changed:

Case "Group" 'table name
To
Case "[St. Stephen’s Small Group]" 'table name

And
Set rst1 = CurrentDb.OpenRecordset("Group")
to
Set rst1 = CurrentDb.OpenRecordset("[St. Stephen’s Small Group]")

3. I got the following error “Compile error: User-defined type not
defined” at
Dim rst As dao.Recordset So I changed it to adodb.Recordset (since I
didn’t have any idea of what I’m doing.)
4. I now get a Type Mismatch error and I’m out of ideas, except I’m running
ACCESS 9.0 from the Office 2000 suite, and I think Jet version 4.0, but all I
could find is a Jet Core Component of Jet 2x.

Chris

Here’s how it looks:

Private Sub Command59_Click()
On Error GoTo Err_Command59_Click

GetTableDatesRecs

Exit_Command59_Click:
Exit Sub

Err_Command59_Click:
MsgBox Err.Description
Resume Exit_Command59_Click

End Sub

Sub GetTableDatesRecs()
Dim rst As adodb.Recordset, rst1 As adodb.Recordset
Dim strSQL As String
Dim bDebug As Boolean

'set this to True to see the strSQL string
bDebug = False

'create the query string
strSQL = "SELECT MSysObjects.Name as tblName, DateCreate, DateUpdate"
strSQL = strSQL & " FROM MsysObjects"
strSQL = strSQL & " WHERE (Left$([Name],1)<>'~') AND
(Left$([Name],4)<>'Msys')"
strSQL = strSQL & " AND ((MSysObjects.Type)=1 or (MSysObjects.Type)=6)"
'could also limit the recordset to the two tables
' by adding
' strSQL = strSQL & " AND ((MSysObjects.Name )= '[St. Stephen's Small
Group]' or MSysObjects.Name)= 'People')"
strSQL = strSQL & " ORDER BY MSysObjects.Name;"

'for debugging
If bDebug Then
MsgBox strSQL
Exit Sub
End If

'open the recordset
Set rst = CurrentDb.OpenRecordset(strSQL)

' check of there are records
If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
Do While Not rst.EOF
Select Case rst!tblName
Case "[St. Stephen's Small Group]" 'table name
Forms![Main Switchboard].ubGroupCreateDte = rst!DateCreate
Forms![Main Switchboard].ubGroupModifyDte = rst!DateUpdate
'now open the table to get the record count
Set rst1 = CurrentDb.OpenRecordset("[St. Stephen's Small Group]")
If Not rst1.BOF And Not rst1.EOF Then
rst1.MoveLast
Forms![Main Switchboard].ubGroupRecCount = rst1.RecordCount
rst1.Close
End If

Case "People" 'table name
Forms![Main Switchboard].ubPeopleCreateDte = rst!DateCreate
Forms![Main Switchboard].ubPeopleModifyDte = rst!DateUpdate
' open the table to get the record count
Set rst1 = CurrentDb.OpenRecordset("People")
If Not rst1.BOF And Not rst1.EOF Then
rst1.MoveLast
Forms![Main Switchboard].ubPeopleRecCount = rst1.RecordCount
rst1.Close
End If

End Select
' move to the next record in recordset 'RST'
rst.MoveNext
Loop
End If

'cleanup
rst.Close
Set rst = Nothing
Set rst1 = Nothing

End Sub

--
Chris F


"SteveS" wrote:

>
> I was wondering/hoping there was a unique identifier, like Employee number,
> that could be used to identify each person. You said "Delete the "People"
> table, import the new data and rename the table". This destroys the link
> between the "Group" and "People" tables. You manually establish the link
> between the tables each time. That might be acceptable if the "People" table
> is under 50 people, but if there are more than 500, it is going to tabke a
> lot of time - not to mention the possibility of errors.
>
> If there was a unique identifier, the code could loop thru the new data in
> the Temp table, update the"People" table, compare the two tables to add the
> new people to
> "People" table and delete the people from the "People" table that were not
> in the temp table.
>
> Deleting a table then recreating it will probably corrupt your database at
> the worst time you could think of.
>
> --
> Having a FE/BE is the right way to go - it is easy to make changes to the
> code/forms/queries/reports in a test mdb, then reattach the production data
> (BE).
> Splitting the database was not the cause of the code not working.
>
> --
> Your last two paragraphs in the first post is about the table
> Creation/Modification dates/times. I pasted a link a "The Access Web" page
> that has the start of some code to get the creation/modification dates/times.
> Note that the modify date/time is when the *structure* was modified, not when
> the last time the table data was updated.
>
> There is a hidden system table that stores this info (MsysObjects). The
> following is how you get the dates/time and the record count.....
>
> Asumptions: there is a form named "Switcboard" and two tables "Group" and
> "People".
>
> On the form "Switchboard", add 6 unbound text boxes. Name them:
> (ub stands for unbound)
> ubGroupCreateDte
> ubGroupModifyDte
> ubGroupRecCount
>
> ubPeopleCreateDte
> ubPeopleModifyDte
> ubPeopleRecCount
>
> Add a button to the form.
>
> Modify the caption property of the 6 unbound text boxes to show the Create
> date, modify date and num of records for each table.
>
> For the button, create an On Click event. It should look something like this:
>
> Private Sub Command0_Click()
> GetTableDatesRecs
> End Sub
>
> Paste this code after the button click code:
> (watch for line wrap)
>
> '********beg code *********
> Sub GetTableDatesRecs()
> Dim rst As dao.Recordset, rst1 As dao.Recordset
> Dim strSQL As String
> Dim bDebug As Boolean
>
> 'set this to True to see the strSQL string
> bDebug = False
>
> 'create the query string
> strSQL = "SELECT MSysObjects.Name as tblName, DateCreate, DateUpdate"
> strSQL = strSQL & " FROM MsysObjects"
> strSQL = strSQL & " WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4) <>
> 'Msys')"
> strSQL = strSQL & " AND ((MSysObjects.Type)=1 or (MSysObjects.Type)=6)"
> 'could also limit the recordset to the two tables
> ' by adding
> ' strSQL = strSQL & " AND ((MSysObjects.Name )= 'Group' or
> (MSysObjects.Name)= 'People')"
> strSQL = strSQL & " ORDER BY MSysObjects.Name;"
>
> 'for debugging
> If bDebug Then
> MsgBox strSQL
> Exit Sub
> End If
>
> 'open the recordset
> Set rst = CurrentDb.OpenRecordset(strSQL)
>
> ' check of there are records
> If Not rst.BOF And Not rst.EOF Then
> rst.MoveFirst
> Do While Not rst.EOF
> Select Case rst!tblName
> Case "Group" 'table name
> Forms!Switchboard.ubGroupCreateDte = rst!DateCreate
> Forms!Switchboard.ubGroupModifyDte = rst!DateUpdate
> 'now open the table to get the record count
> Set rst1 = CurrentDb.OpenRecordset("Group")
> If Not rst1.BOF And Not rst1.EOF Then
> rst1.MoveLast
> Forms!Switchboard.ubGroupRecCount = rst1.RecordCount
> rst1.Close
> End If
>
> Case "People" 'table name
> Forms!Switchboard.ubPeopleCreateDte = rst!DateCreate
> Forms!Switchboard.ubPeopleModifyDte = rst!DateUpdate
> ' open the table to get the record count
> Set rst1 = CurrentDb.OpenRecordset("People")
> If Not rst1.BOF And Not rst1.EOF Then
> rst1.MoveLast
> Forms!Switchboard.ubPeopleRecCount = rst1.RecordCount
> rst1.Close
> End If
>
> End Select
> ' move to the next record in recordset 'RST'
> rst.MoveNext
> Loop
> End If
>
> 'cleanup
> rst.Close
> Set rst = Nothing
> Set rst1 = Nothing
>
> End Sub
> '********end code *********
>
> Save the code and save the form. Click the button. You could also call the
> "GetTableDatesRecs" code in the Form_Activate() event.
>
> HTH
> --
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
>
>
> "Chris Fillar" wrote:
>
> > First, the “People” table has 46 fields of personal data such as emails,
> > phone numbers, address, children, etc. There are only 4 of those 46 that
> > contain group membership information that relate to the groups in the
> > “Groups” table, but I use the other personal individual information in
> > various reports. Updating any information in People must be done outside of
> > ACCESS, in the ACS database, and that’s why I didn’t see an advantage to keep
> > the “People” table intact. I thought since the ACS database is essentially
> > the “master”, why not just remove and replace. Is there an advantage to
> > updating, rather than replacing the ACCESS table? Although most individuals
> > remain somewhat stable, there are constant deletions, additions and changes.
> >
> > I’ve used your earlier suggestion of ”import the new data into a temp
> > table, delete the data in the "People" table, then append the data from the
> > temp table to the "People" table. Provided you can maintain the relationship
> > with the table "Groups"” successfully and it has maintained the relationship
> > between the tables. I even automated it somewhat with a append query, but
> > that has stopped working correctly, since I followed another suggestion to
> > have a front end and back end with linked tables.
> > Is there another way to automate the new data to a temp file, delete the
> > data, and then add the data via a micro or VB?
> >
> > Would you explain “Modify the "Table" query Where clause to look for the
> > tables PEOPLE and GROUPS. Add the two fields Created and Modified fields. (I
> > would use a recordset) Push the dates into unbound text boxes on the
> > switchboard form.”. Are the 2 fields you mentioned for every record. I was
> > looking for the last date that “People” table was last modified for example,
> > rather than an individual record, or will what you suggested return 1 value?
> >
> >
> > Also, would you please explain further the process for: “
> > The number of records is just as easy. Open a recordset on each the tables,
> > ..MoveLast, push the .Recordcount into a unbound text box on the form. “?
> >
> > Again, thank you for your help.
> > Chris
> >
> > --
> > Chris F
> >
> >
> > "SteveS" wrote:
> >
> > > I was hoping that there was a unique identifier (for each person) for the
> > > "People" table. Then you could import "new" ACS people data to a temp table
> > > and run code to append only new people (that are not already in the "People"
> > > table) into the "People" table.
> > >
> > > What info do you get (have) for the "People" table?
> > >
> > >
> > > If you want to delete the "People"table each time there is an update to the
> > > "People" table, you can't have a defined relationship (in the relationship
> > > window) between the "People" and "Group" tables. Of course you can have them
> > > set up in saved queries and VB code.
> > >
> > > I still think you should create the table ("People") *once* and delete the
> > > records/ import new records rather than recreate the "People" table each time.
> > >
> > > If you want examples of Make Table code, search Google groups for "Make
> > > table".
> > >
> > >
> > > --
> > > Steve S
> > > --------------------------------
> > > "Veni, Vidi, Velcro"
> > > (I came; I saw; I stuck around.)
> > >
> '***** SNIP ********************

1. Good

2. No "[ ]" around "St. Stephen’s Small Groups"

3. You need to have a reference to Microsoft DAO 3.6 Library.

Press Control-G to open the IDE
Goto TOOLS/REFERENCES
Scroll down and find Microsoft DAO 3.6 Library
Select it
Close the dialog box

Change the DIM statement back to

Dim rst As dao.Recordset, rst1 As dao.Recordset

4. Shouldn't have any more compile errors.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Chris Fillar" wrote:

> Thank you so much for all your efforts! I’m trying to get an unique
> identifier from the ACS people, to do the updates as you suggested. For the
> time being, I’m not deleting the tables, I’m deleting the data and then
> pasting in the new information.
>
> I tried to apply your code with the following exceptions:
> 1. Switchboard is really Main Switchboard so in 6 locations, I changed
> Forms!Switchboard. to Forms![Main Switchboard].
> 2. Groups is really St. Stephen’s Small Groups so I changed:
>
> Case "Group" 'table name
> To
> Case "[St. Stephen’s Small Group]" 'table name
>
> And
> Set rst1 = CurrentDb.OpenRecordset("Group")
> to
> Set rst1 = CurrentDb.OpenRecordset("[St. Stephen’s Small Group]")
>
> 3. I got the following error “Compile error: User-defined type not
> defined” at
> Dim rst As dao.Recordset So I changed it to adodb.Recordset (since I
> didn’t have any idea of what I’m doing.)
> 4. I now get a Type Mismatch error and I’m out of ideas, except I’m running
> ACCESS 9.0 from the Office 2000 suite, and I think Jet version 4.0, but all I
> could find is a Jet Core Component of Jet 2x.
>
> Chris
>
> Here’s how it looks:
>
> Private Sub Command59_Click()
> On Error GoTo Err_Command59_Click
>
> GetTableDatesRecs
>
> Exit_Command59_Click:
> Exit Sub
>
> Err_Command59_Click:
> MsgBox Err.Description
> Resume Exit_Command59_Click
>
> End Sub
>
> Sub GetTableDatesRecs()
> Dim rst As adodb.Recordset, rst1 As adodb.Recordset
> Dim strSQL As String
> Dim bDebug As Boolean
>
> 'set this to True to see the strSQL string
> bDebug = False
>
> 'create the query string
> strSQL = "SELECT MSysObjects.Name as tblName, DateCreate, DateUpdate"
> strSQL = strSQL & " FROM MsysObjects"
> strSQL = strSQL & " WHERE (Left$([Name],1)<>'~') AND
> (Left$([Name],4)<>'Msys')"
> strSQL = strSQL & " AND ((MSysObjects.Type)=1 or (MSysObjects.Type)=6)"
> 'could also limit the recordset to the two tables
> ' by adding
> ' strSQL = strSQL & " AND ((MSysObjects.Name )= '[St. Stephen's Small
> Group]' or MSysObjects.Name)= 'People')"
> strSQL = strSQL & " ORDER BY MSysObjects.Name;"
>
> 'for debugging
> If bDebug Then
> MsgBox strSQL
> Exit Sub
> End If
>
> 'open the recordset
> Set rst = CurrentDb.OpenRecordset(strSQL)
>
> ' check of there are records
> If Not rst.BOF And Not rst.EOF Then
> rst.MoveFirst
> Do While Not rst.EOF
> Select Case rst!tblName
> Case "[St. Stephen's Small Group]" 'table name
> Forms![Main Switchboard].ubGroupCreateDte = rst!DateCreate
> Forms![Main Switchboard].ubGroupModifyDte = rst!DateUpdate
> 'now open the table to get the record count
> Set rst1 = CurrentDb.OpenRecordset("[St. Stephen's Small Group]")
> If Not rst1.BOF And Not rst1.EOF Then
> rst1.MoveLast
> Forms![Main Switchboard].ubGroupRecCount = rst1.RecordCount
> rst1.Close
> End If
>
> Case "People" 'table name
> Forms![Main Switchboard].ubPeopleCreateDte = rst!DateCreate
> Forms![Main Switchboard].ubPeopleModifyDte = rst!DateUpdate
> ' open the table to get the record count
> Set rst1 = CurrentDb.OpenRecordset("People")
> If Not rst1.BOF And Not rst1.EOF Then
> rst1.MoveLast
> Forms![Main Switchboard].ubPeopleRecCount = rst1.RecordCount
> rst1.Close
> End If
>
> End Select
> ' move to the next record in recordset 'RST'
> rst.MoveNext
> Loop
> End If
>
> 'cleanup
> rst.Close
> Set rst = Nothing
> Set rst1 = Nothing
>
> End Sub
>
> --
> Chris F
>
>
> "SteveS" wrote:
>
> >
> > I was wondering/hoping there was a unique identifier, like Employee number,
> > that could be used to identify each person. You said "Delete the "People"
> > table, import the new data and rename the table". This destroys the link
> > between the "Group" and "People" tables. You manually establish the link
> > between the tables each time. That might be acceptable if the "People" table
> > is under 50 people, but if there are more than 500, it is going to tabke a
> > lot of time - not to mention the possibility of errors.
> >
> > If there was a unique identifier, the code could loop thru the new data in
> > the Temp table, update the"People" table, compare the two tables to add the
> > new people to
> > "People" table and delete the people from the "People" table that were not
> > in the temp table.
> >
> > Deleting a table then recreating it will probably corrupt your database at
> > the worst time you could think of.
> >
> > --
> > Having a FE/BE is the right way to go - it is easy to make changes to the
> > code/forms/queries/reports in a test mdb, then reattach the production data
> > (BE).
> > Splitting the database was not the cause of the code not working.
> >
> > --
> > Your last two paragraphs in the first post is about the table
> > Creation/Modification dates/times. I pasted a link a "The Access Web" page
> > that has the start of some code to get the creation/modification dates/times.
> > Note that the modify date/time is when the *structure* was modified, not when
> > the last time the table data was updated.
> >
> > There is a hidden system table that stores this info (MsysObjects). The
> > following is how you get the dates/time and the record count.....
> >
> > Asumptions: there is a form named "Switcboard" and two tables "Group" and
> > "People".
> >
> > On the form "Switchboard", add 6 unbound text boxes. Name them:
> > (ub stands for unbound)
> > ubGroupCreateDte
> > ubGroupModifyDte
> > ubGroupRecCount
> >
> > ubPeopleCreateDte
> > ubPeopleModifyDte
> > ubPeopleRecCount
> >
> > Add a button to the form.
> >
> > Modify the caption property of the 6 unbound text boxes to show the Create
> > date, modify date and num of records for each table.
> >
> > For the button, create an On Click event. It should look something like this:
> >
> > Private Sub Command0_Click()
> > GetTableDatesRecs
> > End Sub
> >
> > Paste this code after the button click code:
> > (watch for line wrap)
> >
> > '********beg code *********
> > Sub GetTableDatesRecs()
> > Dim rst As dao.Recordset, rst1 As dao.Recordset
> > Dim strSQL As String
> > Dim bDebug As Boolean
> >
> > 'set this to True to see the strSQL string
> > bDebug = False
> >
> > 'create the query string
> > strSQL = "SELECT MSysObjects.Name as tblName, DateCreate, DateUpdate"
> > strSQL = strSQL & " FROM MsysObjects"
> > strSQL = strSQL & " WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4) <>
> > 'Msys')"
> > strSQL = strSQL & " AND ((MSysObjects.Type)=1 or (MSysObjects.Type)=6)"
> > 'could also limit the recordset to the two tables
> > ' by adding
> > ' strSQL = strSQL & " AND ((MSysObjects.Name )= 'Group' or
> > (MSysObjects.Name)= 'People')"
> > strSQL = strSQL & " ORDER BY MSysObjects.Name;"
> >
> > 'for debugging
> > If bDebug Then
> > MsgBox strSQL
> > Exit Sub
> > End If
> >
> > 'open the recordset
> > Set rst = CurrentDb.OpenRecordset(strSQL)
> >
> > ' check of there are records
> > If Not rst.BOF And Not rst.EOF Then
> > rst.MoveFirst
> > Do While Not rst.EOF
> > Select Case rst!tblName
> > Case "Group" 'table name
> > Forms!Switchboard.ubGroupCreateDte = rst!DateCreate
> > Forms!Switchboard.ubGroupModifyDte = rst!DateUpdate
> > 'now open the table to get the record count
> > Set rst1 = CurrentDb.OpenRecordset("Group")
> > If Not rst1.BOF And Not rst1.EOF Then
> > rst1.MoveLast
> > Forms!Switchboard.ubGroupRecCount = rst1.RecordCount
> > rst1.Close
> > End If
> >
> > Case "People" 'table name
> > Forms!Switchboard.ubPeopleCreateDte = rst!DateCreate
> > Forms!Switchboard.ubPeopleModifyDte = rst!DateUpdate
> > ' open the table to get the record count
> > Set rst1 = CurrentDb.OpenRecordset("People")
> > If Not rst1.BOF And Not rst1.EOF Then
> > rst1.MoveLast
> > Forms!Switchboard.ubPeopleRecCount = rst1.RecordCount
> > rst1.Close
> > End If
> >
> > End Select
> > ' move to the next record in recordset 'RST'
> > rst.MoveNext
> > Loop
> > End If
> >
> > 'cleanup
> > rst.Close
> > Set rst = Nothing
> > Set rst1 = Nothing
> >
> > End Sub
> > '********end code *********
> >
> > Save the code and save the form. Click the button. You could also call the
> > "GetTableDatesRecs" code in the Form_Activate() event.
> >
> > HTH
> > --
> > Steve S
> > --------------------------------
> > "Veni, Vidi, Velcro"
> > (I came; I saw; I stuck around.)
> >
> >
> > "Chris Fillar" wrote:
> >
> > > First, the “People” table has 46 fields of personal data such as emails,
> > > phone numbers, address, children, etc. There are only 4 of those 46 that
> > > contain group membership information that relate to the groups in the
> > > “Groups” table, but I use the other personal individual information in
> > > various reports. Updating any information in People must be done outside of
> > > ACCESS, in the ACS database, and that’s why I didn’t see an advantage to keep
> > > the “People” table intact. I thought since the ACS database is essentially
> > > the “master”, why not just remove and replace. Is there an advantage to
> > > updating, rather than replacing the ACCESS table? Although most individuals
> > > remain somewhat stable, there are constant deletions, additions and changes.
> > >
> > > I’ve used your earlier suggestion of ”import the new data into a temp
> > > table, delete the data in the "People" table, then append the data from the
> > > temp table to the "People" table. Provided you can maintain the relationship
> > > with the table "Groups"” successfully and it has maintained the relationship
> > > between the tables. I even automated it somewhat with a append query, but
> > > that has stopped working correctly, since I followed another suggestion to
> > > have a front end and back end with linked tables.
> > > Is there another way to automate the new data to a temp file, delete the
> > > data, and then add the data via a micro or VB?
> > >
> > > Would you explain “Modify the "Table" query Where clause to look for the
> > > tables PEOPLE and GROUPS. Add the two fields Created and Modified fields. (I
> > > would use a recordset) Push the dates into unbound text boxes on the
> > > switchboard form.”. Are the 2 fields you mentioned for every record. I was
> > > looking for the last date that “People” table was last modified for example,
> > > rather than an individual record, or will what you suggested return 1 value?
> > >
> > >
> > > Also, would you please explain further the process for: “
> > > The number of records is just as easy. Open a recordset on each the tables,
> > > ..MoveLast, push the .Recordcount into a unbound text box on the form. “?
> > >
> > > Again, thank you for your help.
> > > Chris