Data collection away from office.

[GA]

Hello all,

I'm in the planning stage of an extension to an existing database, an
Access front end to SQL Server. A number of colleagues will be going
out on inspection and assessment trips and the results will be
collated and reported upon in the database.

I've never tried it before but my first thought was replication but
that is apparently out of the question as it's a transactional
database.

So I was wondering what is the best strategy to follow and what are
the pitfalls.

My second thought was Excel using a form for data entry and then
importing the sheet when back at base. Is that sensible ?

Thanks for any input.
GA

 

[Wayne-I-M]

Hi

Sorry but I don't know what a transactional database is.
If I undertand what you are looking for ??.
You have a database - some people will use a copy of this on a stand alone
machine. Yo then need to import that updateed data from these machines and
incorperate it into your main DB and run some reports. ?

If this is the case then you could make it very simple by just updateing the
table in your main DB with the details that have been updated. You can use a
simple update query forth is. You could add something the the Update to show
who, what, when, where etc the new data was collected.

 

[CraigH]

Please take or leave my comment (I will not respond as I think it is self
evident):
Wayne - I see that you may have helped alot of people but why would not do a
simple google search to see what a transactional database is, and see how it
would affect your answer.

GA- There are problems with the "very simple" aproach that Wayne has
suggested. And I will admit up front I don't know enough about this to
really help you and would normaly not respond and let someone who is more
knowledgeable to answer - but I do know a few things

One problem is knowing what data to update - If a field is different in
both versions who made the change. If the Office did the change then you
don't want to do a mass update on the fields. That means that each field
change has to be recorded and possibly checked against the same change in
your office version, so you can't just do a mass query update.
As with anything there are exceptions - you may be lucky enough that the
only things that the people going on the trips will be changing/Adding (not
an update query but an append) will not be touched by someone in the office -
Probably a bad way to think about it but for a quick job you can think this
way.

The next problem you stated was how to gather that information:
I am biased for just keeping it in Access since you already have the front
end. The problem will be copying what you need to the person who is going to
do the assessment. Do you copy all the information over or just what they
need? Can you use the same DB format? (if it an Oracle Server and you will
probably have to develope a copy routine into Jet, or MS SQL)

Well I will let the experts give you the real lowdown but hopefully I have
given you some idea of mre of the questions you will need to address

Craig

 

[GA]

On Sun, 1 Mar 2009 11:12:02 -0800, Wayne-I-M
Well not exactly. They can't use a copy because the Access front end
connects to SQL Server. It's simple data collection for which I'm
going to use an Excel Spreadsheet.

"Transactional" in the way that SQL Transactions only work if
completed, a change either works completely and is committed to the
database or if there is any problem it fails completely so there is
never any databse corruption, is the way I understand it in my limited
knowledge.

However the idea of an update query pointing at a worksheet sounds
good. I shall give that a try.

GA

 

[GA]

On Mon, 2 Mar 2009 07:17:01 -0800, CraigH

Thanks for that Craig.

It's one of those situations where just talking about it forces you to
think it through and see things more clearly.

The idea is that the data collected will form a complete record in
it's own right and will not be further added to or amended.

I'm asked to create the equivalent of a tick sheet really which cannot
be recorded in the db until the colleague gets back to the office.
GA