Question about wrappers for Office wizards OR about catching application level events.

Hi experts,

I have a need to catch and act on the import of a table into an Access database.  I'm sure there are dozens of better ways to do this than working inside of Access with VBA, but  to save on typing all the details, suffice it to say I'm working within a legacy application and my users preference is to stick with what they know - Access.

I have searched the web with all my might and can't find the solution I would really like to implement.

One solution I thought of is wrap the import wizard in a VBA object so that I can provide some information to it as it is launched, and gather some information as it terminates.  I can't find any example or documentation on how to do this.

The other option I thought of was to have an application level event that is triggered as a new file is imported to a local table.  Again, I didn't find any good example or documentation on that.

The basic question is: are either of these possible?  Or another way have any of you done anything similar where you could share how you solved the problem.

Any help or guidance is greatly appreciated.  

Best Regards,

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
As you may have guessed, there are no built in functions to catch those sort of "events"

What is the purpose of catching this "event"?
Can you modify this database?

For example, you could simply filp a Boolean public variable on the start of your import code.

In a public module do something like this:
Public blnpubImport as Boolean

Then in your code, do something like this:

Sub YourImportCode()
    blnpubImport =True
    'Your code to import the table, for example
    DoCmd.RunCommand acCmdImportAttachAccess
end sub

Then anywhere else in your app you could do this to see if the code ran:

If blnpubImport =True then
    msgbox "A table was imported."
end if

...obviously you would have to have a strategy for resetting this variable back to False
...and also figure out what to do (and how to do it) if you want the trigger to "do something".

Also note that "Import" is a vague term as Access VBA has different  runcommands for different types of Imports (Access, Excel, Text, ...etc)

This is why we need to know the reason for doing this...
In case there may be an alternate technique...

If I am not understanding, ...then please clarify...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jjenkelAuthor Commented:
Thank you Jeff for your prompt reply.  

Yeah, I've seen the variations on the runcommand arguments for the various file types ... I've handled this by making the user choose a filetype on the form that houses all of this code before they can begin.

Regarding your request for the purpose of the application ... and skipping much legacy crap ... I'll try to keep it concise. We've split the database to SQL server for data storage (at least) due to increased usage ... across more departments.  Probably to VB.Net too, but that is ... "being evaluated", ever heard that before?   But all the functionality (forms, code ... etc.) is in Access.

The application takes all kinds of datafiles from all kinds of sources which are not always the same and further as the file providers' systems change so do the file layouts - we have no control over this.  Once again, I'm sure there are better ways and we may get to those eventually with stuff like SSIS and other features of the BI tools suite.  Ultimately the files/tables do get into a standardized format, but I digress.

My user base is familiar with using the Import Wizard to pull data into Access from all kinds of file types and layouts.  My first path of solving this problem led me to let them import the table locally and use other VBA, along with a stored proc on SQL server to move the data into SQL (surprisingly that part was easy to write and works very well).  

I thought this would be the best option because I could get away with less re-inventing the wheel and they won't need to be trained on new functionality which granted with users this educated wouldn't be hard but ...

Does that give you the background or details you were looking for?

jjenkelAuthor Commented:
Hi again Jeff and others.  I forgot one of the most important elements that exists for such a question.  Our environment for now is: Windows 7, Office 2010 Pro, and SQL 2008 R2.

Also, one path led me to Data Macros in Access ... If I could watch the MsysObjects table for new records that would work, but alas, you can't add data macros on system tables ... that I could find/figure out.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jeffrey CoachmanMIS LiasonCommented:
Then (again if I am understanding correctly), ...there is no event to capture when a wizard is run.
Your only hope may be to trigger the wizards with code, and set variables as I mentioned.

I am still a bit hazy on the need for this.

Perhaps you could phrase your question like this:
Hello all, I would like to trap the event of the table import wizard in order to __________."
jjenkelAuthor Commented:
Hi all,

Jeff: taking your model ...

I would like to trap the event of the table import wizard successfully adding a new table to the current access db in order to execute some VBA code.  

But I might phrase it a bit wider to include the addition of a new table in the current access db, not just a case of the wizard.  

The code that needs to run is the code that exports (or pushes) the recently imported table to a SQL server, and replaces the table the user imported to Access with a link to the same table on the SQL server.

I agree with Jeff, your best course of action is to control the import yourself by having your form launch the wizard or if it is a standard import with a known format, use TransferText with a saved import spec.  That way you know when it runs and you can examine the MSysObjects table before and after to determine what got added so you can export it.
jjenkelAuthor Commented:
Thank you Pat & Jeff.

Yeah, with Jeff's replies I had reached a similar conclusion.  

So now I change a form level boolean when the wizard is launched (from inside one of *my* forms) that allows causes an On-timer event to go looking for a new local table.  The on-timer runs of course all the time, but doesn't start checking for a new table until the Wizard is launched.  When the new table is detected and the name acquired I execute the code that needs to run.

I appreciate your help with this and confirmation that what I was finding in other web searches was correct (meaning empty).

Jeffrey CoachmanMIS LiasonCommented:
Then the only way I see doing this is similar to what I posted...
Create a custom "import" user interface for the user.

...but now I see that you need to run your code "after" the table is imported.

So using the wizards will not be an option here, (because you need to provide all the variables before the code is run)
As far as I can tell, you must create your own "import" functionality based on the TransferXXX vba functions.
You will need to build the functions arguments from variables.  These variables will be loaded from user selections they make in your interface.

For example, the user will be asked:
What are you importing from?
Where is this source located?
What is the name of the source?
What is the source Type?
What is the destination?
What name would you like?
...remember that each potential source type will have its own unique import code syntax:
So your final code will be very complex, ...involving multiple programming "Branches"
...and wrought with potential pitfalls,
For example, you will need robust error handling in case any part of your code fails.
(You may also need "rollbacks" for the SQL stuff)
You will also need to consider validating for things like: Overwriting existing objects, counting the number of tables before during and after the import, ...etc
...all to complex for my tastes...
...but certainly do-able, if you have the tenacity...

Jeffrey CoachmanMIS LiasonCommented:
...sorry, you can see, took me quite a while to formulate my post above, I missed all of the posts that were mad in the interim...
jjenkelAuthor Commented:
would have been so much easier to call the wizard like you would ... let's say a fileopendialog box ...

but oh well,

thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.