[AccessD] Custom Excel Import.

Hey folks.  I've got a 'form' in a VB project, it's used a LOT.
Especially by me.  It's our company phone list (with all sorts of bells
and whistles).  When I built the system it's in, I included an import
routine so that HR could update the data in the table behind that form
(the employee data at least, there's other info like computer
information that HR can't touch, in other tables).  The import was an
excel dump (using Crystal Reports) from a Foxpro based system (Abra).
They've since moved to a new system, and the reporting/excel dumping
capability is different.  I've been planning for a month or two to
change the import process. It's still working right now, but it's kind
of a pain for the HR person to crunch the Excel file into the right
format.

 

So, I've got a little 'free' time to do some development.  I'm planning
on building a 'custom Excel import' process.  It'll let the user
'select' an excel file, and then it will go check the file (using ADO),
to get the sheet names, let the user select the sheet, to get the field
names, then it will allow the user to match up fields for the import.
It'll then 'save' that import process (like an import/export spec), and
when the user goes to run it, it will actually do a comparison BEFORE
anything gets done.  Allowing the user to tweak any changes before they
are actually applied.

 

For example:  Import 'Main'.  Most of the fields would be matched, along
with a key field.  Delete 'missing data' would be set (ie, if an
employee isn't listed in the 'import', it's removed from the table
(because the employee doesn't exist anymore...would be an option in the
import/update setup).  When run, the user would get a display of what
fields in what records need to be updated.  They would get a list of new
records to be created, and a list of records 'marked' for deletion.  Any
one of these items would be available to 'change/remove' before
implementing the import/update.

 

Import 'Minor'  Just a few fields to check.  Delete 'missing data'
wouldn't be set.  Same thing.  New Records would be displayed, changes
would be displayed, and the user would be able to change/remove any of
these items before the changes are recorded.

 

So, here's my question.  I am planning on doing this in VB 6.  The
initial project is in VB 6, but it's huge, so I'm going to build it on
the side, then just 'merge' it into the current system.  I would REALLY
like to keep it in VB 6, for two reasons.  One, the open file dialog box
is much easier to do in VB, it's just an object to add to the form.
More importantly, this is going to use treeviews...with custom right
click menus, which just don't work the same in Access (you can't get a
popup window on a treeview in Access the way you do in VB, unless you
got the API route to make the popup menu....COMPLETE pain, and I've
never tried it).  Would anyone be interested in this?  And if so, would
VB 6 be ok, or should I try (can't promise) to make it Access
importable.  (I can make this a .exe that can be used on any
system....).  

 

Another 'additional' feature is that I'm going to be putting some user
security in it, so I can designate who can 'update' what tables, based
on NT name, but that would be pretty simple to change to a custom
username/password system.

 

Drew


The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.