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 ********************
