By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,302 Members | 1,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,302 IT Pros & Developers. It's quick & easy.

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

P: 38
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!
Oct 17 '08 #1
Share this Question
Share on Google+
7 Replies


missinglinq
Expert 2.5K+
P: 3,532
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)>
Oct 18 '08 #2

ADezii
Expert 5K+
P: 8,669
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\Training.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:
Expand|Select|Wrap|Line Numbers
  1. Dim wrkJet As Workspace
  2. Dim dbsTraining As DAO.Database
  3. Dim dbsCurrent As DAO.Database
  4. Dim rstTraining As DAO.Recordset
  5. Dim rstCurrent As DAO.Recordset
  6. Dim strPathToTrngDB As String
  7.  
  8. strPathToTrngDB = "C:\Test\Training.mdb"
  9.  
  10. 'Create Microsoft Jet Workspace object.
  11. Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
  12.  
  13. 'Open Training Database exclusively
  14. Set dbsTraining = wrkJet.OpenDatabase(strPathToTrngDB, True)
  15.  
  16. Set dbsCurrent = CurrentDb()    'Current Database
  17.  
  18. 'Open Recordset based on the Employees Table of the Training Database
  19. Set rstTraining = dbsTraining.OpenRecordset("Employees", dbOpenDynaset)
  20.  
  21. 'Open Recordset based on the Employees Table of the Current Database
  22. Set rstCurrent = dbsCurrent.OpenRecordset("Employees", dbOpenSnapshot)
  23.  
  24. Do While Not rstTraining.EOF
  25.   Do While Not rstCurrent.EOF
  26.     'Match specific Employee in Training to Current DBs
  27.     If rstTraining![EmployeeID] = rstCurrent![EmployeeID] Then
  28.       rstTraining.Edit
  29.         rstTraining![Address] = rstCurrent![Address]
  30.         rstTraining![City] = rstCurrent![City]
  31.         rstTraining![PostalCode] = rstCurrent![PostalCode]
  32.       rstTraining.Update
  33.     End If
  34.     rstCurrent.MoveNext         'Next Employee/Current DB
  35.   Loop
  36.   rstCurrent.MoveFirst          '1st Employee/Current DB
  37.   rstTraining.MoveNext          'Next Employee/Training DB
  38. Loop
  39.  
  40. rstTraining.Close
  41. rstCurrent.Close
  42. Set rstTraining = Nothing
  43. Set rstCurrent = Nothing
P.S. - Linq makes some excellent points about you logic.
Oct 18 '08 #3

DonRayner
Expert 100+
P: 489
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.
Oct 18 '08 #4

P: 38
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!
Oct 20 '08 #5

ADezii
Expert 5K+
P: 8,669
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.
Oct 20 '08 #6

P: 38
Ok I got it to work. I modified it some:
-----------------------------------------------------------------------
Private Sub cboPositionTitle_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.OpenDatabase(strPathToTrngDB, True)

'Open Recordset based on the Employees Table of the Training Database
Set rstTraining = dbsTraining.OpenRecordset("Employee", 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.Edit
rstTraining![PositionTitleID] = Me.PositionTitleID
rstTraining.Update
test = 1
End If

rstTraining.MoveNext '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.Close

Set rstTraining = Nothing

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


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

Thanks for your help!
Oct 20 '08 #7

ADezii
Expert 5K+
P: 8,669
Ok I got it to work. I modified it some:
-----------------------------------------------------------------------
Private Sub cboPositionTitle_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.OpenDatabase(strPathToTrngDB, True)

'Open Recordset based on the Employees Table of the Training Database
Set rstTraining = dbsTraining.OpenRecordset("Employee", 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.Edit
rstTraining![PositionTitleID] = Me.PositionTitleID
rstTraining.Update
test = 1
End If

rstTraining.MoveNext '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.Close

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
Oct 20 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.