Create Access front end for Excel file? Or keep all in XL?

[Ed from AZ]

We have a very large and varied fleet of vehicles. In our maintenance
records, we list every part that is replaced on every vehicle, and the
date and vehicle parameters (odometer and various other meters) of
when the part was replaced. All these maintenance reports go into an
old and proprietary database that is largely inaccessible to me. We
can set up queries for certain types of vehicles and generate a report
that we can save out as an Excel file.

The fun begins when I have to create a report and calculate how long
certain parts are lasting. Or generate a report on all similar
parts. For instance, the right side third axle inboard tire was just
replaced. How many miles were on it? Or: generate a table of all
tire positions showing all the replacements at each position with date
and lifespan.

Right now the AutoFilter in XL is doing a lot of my work - that and my
calculator! Would it be worth the time and effort to explore crafting
an Access front end for these huge database outputs that would enter
something like "TIRE", "R3I", and a date, and it will find the last
time that tire was replaced and calculate the lifespan? And let me
replace the XL file with an updated file every few days?

Ed

 

[dhstein]

Ed,

IMHO you might try to import your excel file into an Access database
table. One of the (substantial) advantages of Access is the ability to run
queries against the database. By importing the file, you could see what
queries you might need and also learn what fields you might need to add. In
addition, you can export your Access data back to Excel very easily. None of
these procedures would require very extensive development - you could do it
in a few minutes and gain some understanding of the pros and cons of the
Access vs Excel question.

 

[Clif McIrvin]

Good question! I see that dhstein has beat me with a response ... I'm
going to suggest beginning with a *linked* table rather than an imported
table, as from your post it seems that the Excel worksheet is a
throw-away that gets recreated on demand.

There are data-type issues that can crop up when linking to Excel
data -- but until you try it you just don't know what you're going to
run into.

As dhstein said, setting up a (linked) Excel table is fairly quick and
simple (the import table wizard walks you right through it). If you run
into #NUM errors, etc. I have some links to information I'll be happy to
post.

Good Luck!

 

[Ed from AZ]

Thanks to both of you!!

Yes, the XL file is a "throw-away" that is replaced by an updated
report. I seem to remember long ago in a VB program making the code
look in a certain folder for the latest file. The VB just set an
object to the file for ranges and such - can I use something of the
same sort here for the linking? Have my main form On_Load search a
specific folder, find the latest XL file, and link to that file for
the data table?

The next challenge would be to build the query - or maybe I'd just
want some text boxes and build a SQL statement from that. I could
have a part name, a part number, maybe just a serial number or a
manufacturer code, and sometimes a date range. I'd want to be able to
put in TIRE and R1I and get just that location, or only TIRE and get
all tires, and constrain either of those with lower and / or upper
date bounds. Sounds like a lot of IFs and building strings and
assembling a SQL from non-zero-length strings.

The results would need to be shown as a data sheet - probably a
subform with a button to export the results in an Excel file. And the
end columns would have to show calculations to tell me the lifespan of
each item found. That's also going to mean the query will have to
search back, find the previous R1I tire replacement, get the
parameters at install, and use those to calculate how long this R1I
tire lasted.

Am I really asking too much?

Ed

 

[Clif McIrvin]

It all sounds do-able ... the first thing is to discover what the linked
table looks like in Access. Until you have actually linked to the
worksheet you don't actually know what the field datatypes are going to
be, since Access makes that decision for you based on what it sees in
the first rows of the sheet.

As to locating and linking to the latest file ... I don't know your work
requirements; but would it be feasable to always replace the older file
so the location doesn't move around? Then there is no hunting for the
newest file; the link is simply valid.

The rest of what you describe sounds like queries -- and / or SQL
generated "on the fly" ... and there are a lot of good examples out
there already -- both on Access related websites, and in previous
threads on these groups. Try searching Google Groups, also:

As you get into it, there are many here with more experinece than I that
are very helpful!

(Thanks to John W. Vinson [MVP] for this info):

Here are some tutorials and other resources that you should find
helpful:
A free tutorial written by Crystal (MS Access MVP):
http://www.accessmvp.com/Strive4Peace/Index.htm
also at http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:http://allenbrowne.com/links.html#Tutorials

Here's a primer with 23 well defined, well written, clearly named
chapters:
http://www.functionx.com/vbaccess/index.htm

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

HTH!
--
Clif

Thanks to both of you!!

Yes, the XL file is a "throw-away" that is replaced by an updated
report. I seem to remember long ago in a VB program making the code
look in a certain folder for the latest file. The VB just set an
object to the file for ranges and such - can I use something of the
same sort here for the linking? Have my main form On_Load search a
specific folder, find the latest XL file, and link to that file for
the data table?

The next challenge would be to build the query - or maybe I'd just
want some text boxes and build a SQL statement from that. I could
have a part name, a part number, maybe just a serial number or a
manufacturer code, and sometimes a date range. I'd want to be able to
put in TIRE and R1I and get just that location, or only TIRE and get
all tires, and constrain either of those with lower and / or upper
date bounds. Sounds like a lot of IFs and building strings and
assembling a SQL from non-zero-length strings.

The results would need to be shown as a data sheet - probably a
subform with a button to export the results in an Excel file. And the
end columns would have to show calculations to tell me the lifespan of
each item found. That's also going to mean the query will have to
search back, find the previous R1I tire replacement, get the
parameters at install, and use those to calculate how long this R1I
tire lasted.

Am I really asking too much?

Ed

 

[Ed from AZ]

Well, then - on we go!! I'll be sure and post back with smoke
reports! 8>)

Thanks for the links!!!!!!!!! They _will_ be used!!

Ed

 

[Clif McIrvin]

Well, then - on we go!! I'll be sure and post back with smoke
reports! 8>)

Thanks for the links!!!!!!!!! They _will_ be used!!

Ed



You're welcome!

I've been at this a bit over a year (only part time avaialble for Access
app. development) now; and best guess is that 2/3 of what I know about
Access / VBA I've learned from lurking in these forums. When you see a
topic that looks like it might be relevant --- follow it!

You'll also begin to recognize names you want to listen to <grin>

I'll 'keep my ears on' ....

 

[Tony Toews [MVP]]

We have a very large and varied fleet of vehicles. In our maintenance
records, we list every part that is replaced on every vehicle, and the
date and vehicle parameters (odometer and various other meters) of
when the part was replaced.

FWIW take a look at the Granite Fleet Manager
http://www.granitefleet.com/

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/