Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2014-10-02
Medium Priority
Last Modified: 2014-10-02
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,

Question by:jjenkel
  • 5
  • 4
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 40357731
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...


Author Comment

ID: 40357783
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?


Author Comment

ID: 40357791
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.

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40357807
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 __________."

Author Comment

ID: 40357833
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.

LVL 40

Expert Comment

ID: 40357858
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.

Author Comment

ID: 40357876
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).

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40358016
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...

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40358021
...sorry, ...as you can see, ...it took me quite a while to formulate my post above, ...so I missed all of the posts that were mad in the interim...

Author Comment

ID: 40358031
would have been so much easier to call the wizard like you would ... let's say a fileopendialog box ...

but oh well,

thanks again.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question