Access 2003-How to update records in 2 separate Access databases

[emajka21]

I have been working on creating a HR database. Everything is working fine there but now they want a training database. I basically need a few fields from the employee table and I need the full department table. I also need the position titles table along with 1 additional field. I was able to export everything fine to the new database.

Is there a way to update the training database when data is updated in the HR database. I figured out how to do an append when a new employee is added but I'm not sure how to delete and update records between multiple databases. The name of the other database is DRR.MDB.

I have found how to link tables between databases but I don't want to give the DRR database the entire employee record, only part of it. I could break the table apart but then I would have to do ALOT of reprogramming of forms and reports and I will only do that as a last resort.

Is there a way to open the DRR database through code from the HR database and run a SQL update/delete query to update records in the DRR?

Thanks for any insight to this problem you might have!

[missinglinq]

I expect someone will be along that can guide you in this, but in every organization I've worked in that used a database for this kind of thing, the training data was part and parcel with the HR database. In the health care field, HR has to know who's been trained in what procedures and when they come up for renewal of certifications. If persons outside of the HR department need access to enter data, you just give them limited access. Much easier than trying to maintain two almost identical databases.

Linq ;0)>

[ADezii]

Is there a way to open the DRR database through code from the HR database and run a SQL update/delete query to update records in the DRR?

Here is one approach that you can look at.

Here is some code that I put together for you that will Update 3 Fields ([Address], [City], and [PostalCode]) in an Employees Table in an External Training Database named Training.mdb (C:\Test\Traini ng.mdb). This process is done completely from within the context of the Current Database, and maintains no Linkage or Connectivity whatsoever with the Training Database. The code does, however, assumed that the 2 Employees Table have identical Field Names and Data Types. If they were not the same, it would not pose a problem, I did it this way simply for demo purposes. I'll stop rambling and simply post the code, any questions, please feel free to ask:

Dim wrkJet As Workspace
Dim dbsTraining As DAO.Database
Dim dbsCurrent As DAO.Database
Dim rstTraining As DAO.Recordset
Dim rstCurrent As DAO.Recordset
Dim strPathToTrngDB As String
 
strPathToTrngDB = "C:\Test\Training.mdb"
 
'Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
 
'Open Training Database exclusively
Set dbsTraining = wrkJet.OpenDatabase(strPathToTrngDB, True)
 
Set dbsCurrent = CurrentDb()    'Current Database
 
'Open Recordset based on the Employees Table of the Training Database
Set rstTraining = dbsTraining.OpenRecordset("Employees", dbOpenDynaset)
 
'Open Recordset based on the Employees Table of the Current Database
Set rstCurrent = dbsCurrent.OpenRecordset("Employees", dbOpenSnapshot)
 
Do While Not rstTraining.EOF
  Do While Not rstCurrent.EOF
    'Match specific Employee in Training to Current DBs
    If rstTraining![EmployeeID] = rstCurrent![EmployeeID] Then
      rstTraining.Edit
        rstTraining![Address] = rstCurrent![Address]
        rstTraining![City] = rstCurrent![City]
        rstTraining![PostalCode] = rstCurrent![PostalCode]
      rstTraining.Update
    End If
    rstCurrent.MoveNext         'Next Employee/Current DB
  Loop
  rstCurrent.MoveFirst          '1st Employee/Current DB
  rstTraining.MoveNext          'Next Employee/Training DB
Loop
 
rstTraining.Close
rstCurrent.Close
Set rstTraining = Nothing
Set rstCurrent = Nothing

P.S. - Linq makes some excellent points about you logic.

[DonRayner]

Break your table apart and rename each part something else like "Mytable1", "Mytable2"..... Then create a query to gather everything back togeather and give it the name of the origional table "Mytable", your forms and reports will treat the query just like it was the origional unsplit table. You can then keep all of the records in the HR db, create your Training DB as a frontend application only and link the required tables into it.

You might run into a few problems if you are running SQL or recordsets in your VBA code but it should be quick and easy to fix up.

[emajka21]

First, thank you all for responding to me.

Linq,
1. We're not the normal organization (or else things might be easier! :)
2. The HR database is located in the HR network drive. There are only about 3-4 people who have access to any portion of this drive and they are not willing to give it to this other person. (Heck they truthfully don't want me to look at the info.) Management is highly paranoid.
3. This DRR database is also "hopefully" going to be temporary.
4. DRR's are only a tiny portion of the training that goes on here and they are also currently looking at redoing how they handle training (they are looking for a full blown training system for next year.) Another reason this database is probably only temporary.
5. DRR's stand for Document Review Request. Bascially employees need to look at a list of documents once as year as part of their training. They also need to be notified when a document changes. We are currently inplementing a new Document Management system in which we are hoping to configure the system to send out these review notifications but it will take several months for all the kinks to be worked out. Which is again, another reason this database is hopefully temporary. HR does not handle these requests, the document management coordinator does.


I could keep going on with the list of reasons but I won't. I was hoping this was just going to be an easy thing, but of course, nothing in life is easy. :) I was just trying to make life a little easier for the person until all of the other things were finished because now all of this info is kept on paper and several spreadsheets. VERY INEFFICIENT!


ADezii,
Looking at the code it looks like this will compare all the records in both tables. Is there are way to make it update the 1 record only at the time of the change (through a form)? For example, if a person moves departments and HR updates their database, can it then at that point (maybe at an onchange event) update the DRR database? The key in both databases is their EmployeeID. Also how would a delete work? Currently I have the HR system move the employees record to a terminated table if the employee's record gets marked for termination. I would like add another procedure during this that would delete it from the DRR table.


DonRayner,
This would probably be the easiest thing to do but it would also give the other person access to the raw data and splitting it apart is still a pain in the butt. But at the same time it makes alot of sense, especially if the DRR database is only going to be temporary...

I will consider this further before I make any drastic changes. But for curiousity's sake I would still like to know how to update/delete the way ADezii is describing. :) Having choices is good. :)

Thanks again!

[ADezii]

First, thank you all for responding to me.

Linq,
1. We're not the normal organization (or else things might be easier! :)
2. The HR database is located in the HR network drive. There are only about 3-4 people who have access to any portion of this drive and they are not willing to give it to this other person. (Heck they truthfully don't want me to look at the info.) Management is highly paranoid.
3. This DRR database is also "hopefully" going to be temporary.
4. DRR's are only a tiny portion of the training that goes on here and they are also currently looking at redoing how they handle training (they are looking for a full blown training system for next year.) Another reason this database is probably only temporary.
5. DRR's stand for Document Review Request. Bascially employees need to look at a list of documents once as year as part of their training. They also need to be notified when a document changes. We are currently inplementing a new Document Management system in which we are hoping to configure the system to send out these review notifications but it will take several months for all the kinks to be worked out. Which is again, another reason this database is hopefully temporary. HR does not handle these requests, the document management coordinator does.


I could keep going on with the list of reasons but I won't. I was hoping this was just going to be an easy thing, but of course, nothing in life is easy. :) I was just trying to make life a little easier for the person until all of the other things were finished because now all of this info is kept on paper and several spreadsheets. VERY INEFFICIENT!


ADezii,
Looking at the code it looks like this will compare all the records in both tables. Is there are way to make it update the 1 record only at the time of the change (through a form)? For example, if a person moves departments and HR updates their database, can it then at that point (maybe at an onchange event) update the DRR database? The key in both databases is their EmployeeID. Also how would a delete work? Currently I have the HR system move the employees record to a terminated table if the employee's record gets marked for termination. I would like add another procedure during this that would delete it from the DRR table.


DonRayner,
This would probably be the easiest thing to do but it would also give the other person access to the raw data and splitting it apart is still a pain in the butt. But at the same time it makes alot of sense, especially if the DRR database is only going to be temporary...

I will consider this further before I make any drastic changes. But for curiousity's sake I would still like to know how to update/delete the way ADezii is describing. :) Having choices is good. :)

Thanks again!

Looking at the code it looks like this will compare all the records in both tables.

One Table is actually a Lookup Table that enables you to retrieve the Start and Stop Ranges for a given State.

Is there are way to make it update the 1 record only at the time of the change (through a form)?

That's exactly what the demo does, or parallels.

[emajka21]

Ok I got it to work. I modified it some:
-----------------------------------------------------------------------
Private Sub cboPositionTitl e_Change()
Dim wrkJet As Workspace
Dim dbsTraining As DAO.Database
Dim rstTraining As DAO.Recordset
Dim strPathToTrngDB As String

Dim test As Integer

strPathToTrngDB = "h:\DRR.mdb "

'Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace ("", "admin", "", dbUseJet)

'Open Training Database exclusively
Set dbsTraining = wrkJet.OpenData base(strPathToT rngDB, True)

'Open Recordset based on the Employees Table of the Training Database
Set rstTraining = dbsTraining.Ope nRecordset("Emp loyee", dbOpenDynaset)

test = 0

Do While test = 0 And Not rstTraining.EOF 'Ends once there is a match or Training EOF is reached
'Match specific Employee in Training to Current DBs
If rstTraining![EmployeeID] = Me.EmployeeID Then
rstTraining.Edi t
rstTraining![PositionTitleID] = Me.PositionTitl eID
rstTraining.Upd ate
test = 1
End If

rstTraining.Mov eNext 'Next Employee/Training DB
Loop

If test = 0 Then
MsgBox "Employee was not found in DRR database. Please notify Doc Coordinator of change."
End If
rstTraining.Clo se

Set rstTraining = Nothing

End Sub
------------------------------------------------------------


I will add this to all the fields that are needed in the DRR database.

Thanks for your help!

[ADezii]

Ok I got it to work. I modified it some:
-----------------------------------------------------------------------
Private Sub cboPositionTitl e_Change()
Dim wrkJet As Workspace
Dim dbsTraining As DAO.Database
Dim rstTraining As DAO.Recordset
Dim strPathToTrngDB As String

Dim test As Integer

strPathToTrngDB = "h:\DRR.mdb "

'Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace ("", "admin", "", dbUseJet)

'Open Training Database exclusively
Set dbsTraining = wrkJet.OpenData base(strPathToT rngDB, True)

'Open Recordset based on the Employees Table of the Training Database
Set rstTraining = dbsTraining.Ope nRecordset("Emp loyee", dbOpenDynaset)

test = 0

Do While test = 0 And Not rstTraining.EOF 'Ends once there is a match or Training EOF is reached
'Match specific Employee in Training to Current DBs
If rstTraining![EmployeeID] = Me.EmployeeID Then
rstTraining.Edi t
rstTraining![PositionTitleID] = Me.PositionTitl eID
rstTraining.Upd ate
test = 1
End If

rstTraining.Mov eNext 'Next Employee/Training DB
Loop

If test = 0 Then
MsgBox "Employee was not found in DRR database. Please notify Doc Coordinator of change."
End If
rstTraining.Clo se

Set rstTraining = Nothing

End Sub
------------------------------------------------------------


I will add this to all the fields that are needed in the DRR database.

Thanks for your help!

You are quite welcome