How do I link AND APPEND the linked data

[Guest]

How do I LINK AND APPEND the linked data of a named range of columns from an
Excel spreadsheet to an existing Access table?

Short background: I'm a physician with minimal programing experience, but so
far have been able to develope an Access database of obstetrical patients for
a rural community health center. The last missing step is to add a scanned
picture of each patient to a tab'ed page. I know how to do it when the data
is drawn from an Access table (using Bound Object Frame), but unfortunately
the data entry person is trained to work with Excel and not with Access.
Consequently I'm getting the data by linking to Excel spreadsheets. Since the
Access table is linked to an Excel table, I cann't add to it a column with an
OLE object.

Without getting into complicated coding, can I create an Access table which
mirrors the Excel table, add to it an OLE object field for the picture and
then link/append the Excel spreadsheet to this Access table, so that only the
identical fields will be linked but the picture column will remain un-linked
and will be available to bind to the patient's pictures?

Any advise will be greately appreciated!!

Thanks, Dr. Lidor

 

[Danok]

Dr. Lidor;
There are two solutions I can offer:
First, I suggest you link your excel table as a "temp" table and then use an
append query to append the data to your "real" table.

Second, and more importantly, is DON'T add the scanned picture directly to
your database. For a variety of reasons, but the most important is the
performance hit you'll receive. The scanned graphic file will be larger -
much larger - than simple text data referencing the file path for the
appropriate file. And after a number of records are added you'll find your
database growing exponentially.

A better method is to have a text field in your table that stores the file
path for the appropriate graphic file. In the databases I've done requiring
graphic images I've created a sub-folder in the folder the database is stored
to contain the graphic files. Then in your access db form place an unbound
object and programmatically revise the source of the object with each record.
Following is some code I use to do this: I call this routine from the
OnCurrent event for the applicable form.

Public Sub RefreshPhoto()
On Error Resume Next
If IsNull(Me.PhotosLocation) Then
Me.imgPhoto.Picture = "(none)"
Else
Me.imgPhoto.Picture = Me.PhotosLocation
End If
End Sub

"PhotosLocation" refers to the field name of the text field containing the
filepath
"imgPhoto" is the name for the image object.

Hope this helps.

Dan Knight

 

[Guest]

Hi Dan,

Thanks for the very useful suggestions.

In regards to the “temp†table and the “append†query, I’m playing with this
option for quite awhile, but could not yet solve two major problems with this
approach:

1. The patient’s records are updated throughout their pregnancy (with new
findings or new lab results, etc). The Append query does not update existing
records but only add new ones. One possible solution is to delete all the
records from the current table with a Delete Query and use an Append query to
copy again all the records (after updating the excel table and the linked
access “temp†table). I just don’t know yet how to write a code to do it
automatically. I could not figure out if the Update query could also do such
job.

2. The Append query adds the new records before (on top) of the old ones,
rather that after them. Is there a way to control where the new records are
added?

In regards to the suggestion about the pictures location – it is an
excellent one. The only problem – I don’t know much about VBA programming. I
just bought the book Access VBA programming for Dummies to comprehend in
details your code, though in general terms I understand it. If you don’t mind
I’ll ask for your advice in case of unexpected difficulty.

Thanks again for your very valuable suggestions. I feel now much closer to
the end of this project than ever before.

Dr. Lidor

 

[Danok via AccessMonster.com]

Dr. Lidor;
Glad to be of help. VBA Programming for Dummies is an excellant 'starter'
book: If it is written by Rob Krum (sp?), then his writing style is perfect
for the position you're in; he takes you step by step and builds on his
previous information.

With respect to the appending vs updating of "changing patient data; I can
speak only as a patient...whenever my doctor brings my file into the
examination room, I notice that ALL the data is there. He simply adds today's
record onto of the last document. I suspect the same is true for your
patient's hard-copy file.

Likewise, develop your database to replicate this technique. When you need to
"add" or append new data include a "date-stamp" field. All this is is a
date/time field added to the structure of your data table and populated with
the current date when the append is executed. To do this you can either
include a date column in your excel file or simply add an additional field in
your append query.

Adding the date & or time column to your excel data means you can add varying
dates and times depending on the accuracy needed. In other words, if you need
to know that data X for patient Y was performed 20/03/06 at 8:32 AM and data
Y for Patient Y was performed 20/03/06 at 10:45 AM, then the data should be
in your excel table. However, if all you need is to know which record was the
most recent record added to your access table AND there will only be one
record per patient per append, then use the append query to auto-populate a
date stamp.

For example: in your append query design view, once all the regular fields
from the excel data are in the grid add an additional field Updated: date()
or
Updated: now()
What this does is create a new field in your query named Updated and
populates it with either the date or now data. Date() returns the current
system date, Now() returns the current system date and time. I'd only use
Date() if there will only ever be one record per patient added per day,
otherwise I'd use Now().

This technigue retains all the history of the patient and can easily be
filtered or sorted to show only the most recent data by filtering for certain
date ranges using Between And filters or using the Max() or Min() functions.

I hope this helps.

Dan Knight
Knight Information Services

Hi Dan,

Thanks for the very useful suggestions.

In regards to the “temp” table and the “append” query, I’m playing with this
option for quite awhile, but could not yet solve two major problems with this
approach:

1. The patient’s records are updated throughout their pregnancy (with new
findings or new lab results, etc). The Append query does not update existing
records but only add new ones. One possible solution is to delete all the
records from the current table with a Delete Query and use an Append query to
copy again all the records (after updating the excel table and the linked
access “temp” table). I just don’t know yet how to write a code to do it
automatically. I could not figure out if the Update query could also do such
job.

2. The Append query adds the new records before (on top) of the old ones,
rather that after them. Is there a way to control where the new records are
added?

In regards to the suggestion about the pictures location – it is an
excellent one. The only problem – I don’t know much about VBA programming. I
just bought the book Access VBA programming for Dummies to comprehend in
details your code, though in general terms I understand it. If you don’t mind
I’ll ask for your advice in case of unexpected difficulty.

Thanks again for your very valuable suggestions. I feel now much closer to
the end of this project than ever before.

Dr. Lidor

Dr. Lidor;
There are two solutions I can offer:

[quoted text clipped - 55 lines]

 

[Guest]

Hi Dan,

Once again - great advice! I probably could have used it if the purpose of
the database would have been to fully replace the hard copy data. But in all
actuality, we still maintain the old-fashioned patient charts and want to use
the database for two purposes only:

1. To analyze on the fly our patient population (i.e. how many and who are
the teen pregnancies we have at any given moments in our program or how many
diabetes in pregnancy patients we have at 37 weeks, who they are, and after
deliver - what was their outcome, etc.).

2. To allow L&D nurses to tap into this electronic prenatal record database
to save on hard copies updates that are periodically faxed to them, and to
allow them to find available information on a patient for whom hard copy was
not yet been faxed.

The bottom line here is that there is really no importance to WHEN the data
have changed. To give you a practical example: suppose a patient enrolls at
13 wks. She gets full history and physical and certain routine labs are being
taken (CBC, Type and screen, STD screen, RPR, Hepatitis B screen, Immunity to
rubella etc.). Once all the labs are back, our database entry person would
enter them into the patient’s record. However, we get other prenatal labs
later on, such as diabetes screen (which we routinely do between 24-30 wks),
GBS culture (which we routinely do between 34-37 wks), etc. Every test has a
field/column in excel. The fields for which data are still unavailable at the
time of the initial data entry are marked as “maybe†(since they could become
“Positiveâ€, or “Negativeâ€, or carry a certain numeric value later on, when
the results are available). This allows the data entry person to pull out (by
filtering) for example all the patients that are at 31 weeks and their
diabetes screen is sill “maybeâ€. She will then pull out their hard copied
charts and update from them the proper values in the electronic records.

Thus this is a static sequential database, and not an incremental one. At
any given moment in time, certain patient records are already fully
completed, ready to be analyzed or to be opened by the L&D nurses when the
patients show up for labor, while the records of other patients are still
being built up.

Taken all together, I thought there might be one simple way to update the
final Access database: Since the linked “temp†Access chart is always
reflecting the changes made in Excel by the data entry person, a code could
be written that upon opening the “final/target†database table, would run a
Delete query to delete all the current records from this table, and then it
will run an Update query to fill up the table again with the “new“ records,
by copying them all from the “temp†table into the “final/target†one (of
course, only some of theses records would be new; others would be updated and
still others would be unchanged). In my mind this is the simplest way to
deal with it without worrying about were appended records are going to end
up, since the original order/structure is always going to be maintained
exactly as the data entry person entered it in excel, i.e. - new patient
records are going to be added always underneath the old ones.

Hope it is clearer now. What do you think about this approach?

Thank you so much for your help. It’s really great stuff.

Dr. Lidor