Displaying File properties on a Access 2000 Form

C

Chris Fillar

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.
 
C

Chris Fillar

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
 
C

Chris Fillar

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?
 
S

SteveS

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".
 
C

Chris Fillar

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
 
S

SteveS

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 said:
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
'***** SNIP ********************
 
C

Chris Fillar

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 said:
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 said:
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
'***** SNIP ********************
 
S

SteveS

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 said:
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 said:
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 said:
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
 
C

Chris Fillar

Steve,
Thanks again, as you said everything works. I even added
"GetTableDatesRecs" code in the Form_Activate() event as you suggested!
Hopefully the ACS person will get back to me about a unique identifier, and
then I'll probably be asking for you help. But once again, thanks!
--
Chris F


SteveS said:
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 said:
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 said:
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.)


:

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
 
C

Chris Fillar

Steve,
I finally received the ACS information that there is no unique identifier.
The only field similar is the ID field, but that is based on the family (with
suffixes to identify individual family members) and if someone changes family
group via marriage, divorce, etc then the individuals ID number changes.

So I am back to trying to automate the manual system of updating the PEOPLE
table with a new PEOPLE table from the ACS (non-Access) database. Here's
what I'm doing now:

1. Open “Buffy’s Small Group Test†(this is the exported from ACS file) and
with the “People†Table open, Edit/Select All Records (or CTRL+A), then
Edit/Copy (or CTRL+C) to copy the data into the windows clipboard.

2. Open the “Small Groups†(this is the back end of my database), “Peopleâ€
table. Delete the data in the "People" table (Edit/Select All Records (or
CTRL+A), then Delete)

3. Copy the data from the clipboard to the “Small Groupsâ€, “People" table.
(With the “People†Table open, Edit/Paste (or CTRL+V).

Here are a few questions I have:
Is there a better way to do this?
Is there a way to automate this with a button on my "Main Switchboard" Form?
An additional concern is that by this method, my front end .mdb gets larger
every time I do this update. Is there a way to stop the file size growth?

Thank you very much for your assistance!

Chris




--
Chris F


SteveS said:
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 said:
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 said:
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.)


:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top