Solved

Looking for advice on how to approach a certain scenario

Posted on 2014-10-03
5
98 Views
Last Modified: 2014-10-04
I have a database that is used by people in the office.  Then we have people that work outside the office and they are called field personnel.  Using the "office" database we want to be able to export a record that is being viewed on a form.  In addition to this we want to export another record that is related to that record being viewed on the form.

Then, I believe that the field people need a separate copy of the database with everything removed that they don't need other than the form so they can view the same record when they receive it.  They will edit the record and send it back to the office.

What is the best way to handle the transfer of the record back an forth.  Has anyone done something like this before?  

--Steve
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 40359538
Do the field people enter or update data?  Or are they just viewing it?
0
 
LVL 28

Expert Comment

by:omgang
ID: 40359614
I have done something similar years ago with an Access application (Access 2000).  We had four regional locations all using the same inventory control system (Access app).  Every Friday night, three of the offices would FTP their back-end Access db to our home location.   We had another Access app whose sole purpose was to merge the data from the four back-end databases and produce a new 'master' copy that was FtPd back to the other offices.  On Monday, we'd all start with the exact same set of data.
The process was not simple but was 'cheap' and it served its purpose.   We eventually went to a Windows Terminal Services solution where the regional offices all worked on the same app and back-end db via Citrix remote connections to the home office.
All that being said, since you have to get a copy of the 'thin' app to the Field Folks via internet (I'm assuming this is how you plan to distribute), it seems to be you'd be much better off developing a simple web app to allow them to view/update necessary records.  It could be something as simple as a classic ASP web app with an Access back-end or a .NET app with a SQL Server db back-end.  This is the direction I'd pursue.
OM Gang
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40359623
Have you considered using SharePoint?
0
 

Author Comment

by:SteveL13
ID: 40359719
Pat,

The field people will edit the data and return it somehow.  I don't think SharePoint is an option at this time.
0
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40359765
I would use TransferText or TransferSpreadsheet to export a query that contains the selected data.  The user can send that file via email and then the Access app can import the transferred file.

If the data is from a single table, the process is pretty simple but if the data is hierarchical in nature then the export must export using a query rather than a table and the import process needs to apply adds/updates to multiple tables in the master database so it must be done with VBA and can't use a single update/append query.

If you want to automate the process, you can use s special email address or code in the subject line to get the email into a separate folder.  The Access app can read the folder periodically or on demand, process the emails and extract and save the attachments.  Then process the saved files.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question