Append current record only

[tmtompkins]

I need to send one record ( a work order ) from one databse to another, for approval, and then on to a master database. What would be the easiest way to set up an append query or macro to only send the current record up the line? Thanks, Terry

 

[willir]

tmtompkins

So you use three databases for work orders??

Subsystems and subsystems...

Are the three databases all Access or do you have to update another type of database such as MS*SQL or Oracle?

Are the database in the same location, or at different sites.

There are several ways of doing this; some more challenging than others....
- work with shared / linked tables
- email
- export / import
- ADO coding to connect to other database

The simplest would be to share linked tables.

Assuming you have Access for all three, and assuming the database schema will support it, you can actually link tables in Access. The assumptions are...
- Using Access
- Specifications for the fields are the same, or similar enough not to run into data type errors.
- Updates from the subsystem will not result in data integerity issues.

SubSystem1 (starting point)

tblWorkOrder
WorkOrderID
WorkOrderNum
etc

tblWorkOrderDetail
WorkOrderDetailID
WorkOrderID
+ details

SubSystem2 (approval)
Will need to be able to use WorkOrderNum
Need to include enough info to preserve integrity

tblWorkOrderApproval
WorkOrderApproval
WorkOrderNum
etc

Will you need to include the details at this point?? If so, the tables need to be linked for this too.

This table(s) is(are) accessed by both subsystem 1 and 2.

To link a table, click on the "New" in the table database object view, and select "Link Table"

With Access 2000 and beyond, you can also use the "Linked Table Manager" under the menu select Tools -> Database Utilities.

Point to the desired database and select the appropriate table or tables.

Updates to the linked table will become visible to both "systems".

Repeat when linking subsystem 2 to the master database.

The advantage of using linked tables is that...
- the updates are immediate
- you are working with similar tools

The disadvantage, at this will be for most solutions is that you have to make sure you maintain data integrity between the susbsystems.

If the databases are at different sites / locations, you may be able to keep the tables updated / in synch via replication.

If you are working with non Access database, then the solution will be trickier.

Hopefully, I have provided enough info to help, or atleast give you a better idea in direction.

Richard

 

[tmtompkins]

Thanks for the info, I'll check it out and let you know how it's going or get back with you with any other questions. Thanks again, Terry

 

[tmtompkins]

I’m actually just setting this system up and am looking for the best way of doing it. What I have are 7 different buildings in the district that use a system of inter-office mailing hard copies of work orders from teachers and staff to a building computer coordinator for approval and initial action and then on to the technology department for follow-up.
What I was looking into is having the first step available to staff from an icon on the staffs computer to a database on the building server. This record would be sent to the building coordinator where a sub form, to keep track of actions taken, would be added. The request would be acted on or approved to be sent to technology on a networked server in another building. The final database at technology would be receiving records from all 7 buildings.
The reasons I was looking at separate databases where, I didn’t need the base staff to access the sub form and I didn’t want the work order to make technology without first being looked at by the building coordinator.
Let me know what ideas you may have, Thanks, terry

 

[willir]

tmtompkins

In other words, you have a database that you want to modularize by task.

Linking only the requreid tables will work.

I use this approach for HR management, Shift Supervisors and Lead hands. The guy who created the database lacked quite a bit in skills, so setting up security would be problematic.

So I split the database into two based on tables...
- Lead hands access a subset of the database using linked tables.
- Supervisor accesses same information plus more
- HR accesses more info plus the confidedntial stuff.

Say F: is a network drive...

[blue]LeandHands - minimal access[/blue]
Access
F:\DB\LeadHands\TrackHrs.mdb

tblEmployee - with name, phone number, address
tblTrackDelays tables - delays, production, employee work areas

[blue]Supervisors[/blue]
Access
F:\DB\LeadHands\TrackProd.mdb
F:\DB\Supervisors\TrackHrs.mdb

tblTrackHrs tables - record OT, absences, etc

[blue]HR[/blue]
Access
F:\DB\LeadHands\TrackProd.mdb
F:\DB\Supervisors\TrackHrs.mdb
F:\DB\HRManagement\HRInfo.mdb

tblConfidential - linked 1:1 to tblEmployee with salaries, etc
tblGrievences- track grievences
etc

By breaking the one database into three parts, I addressed a security problem without having to implement Access security. I use network security to control access.

Having one main database with Access security may have been better, but I had to work with what I had. ALSO, some of the boys on the floor are pretty savy with Access, and I would have a fear of a user breaking into the confidential tables by linking tables from another database.


As per your issue intergrating different sites. How often? Access over a WAN or modem is not known for performance -- Access copies entire recordsets or tables when opening up a simple form.

Replication may be one solution. Play first with a backup copy of your database first -- there are some interesting side effects.

A better solution, if you can afford it, is to use a terminal server offered by Microsoft.

Richard