Solved

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

Posted on 2014-10-02
10
199 Views
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,

J.
0
Comment
Question by:jjenkel
  • 5
  • 4
10 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 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...


JeffCoacman
0
 

Author Comment

by:jjenkel
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?

J.
0
 

Author Comment

by:jjenkel
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.

J.
0
 
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 __________."
0
 

Author Comment

by:jjenkel
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.

J.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 34

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:jjenkel
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).

J.
0
 
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?
...etc
...remember that each potential source type will have its own unique import code syntax:
TransferDatabase
TransferSQLDatabase
TransferSpreadsheet
Transfertex
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...
;-)

JeffCoachman
0
 
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...
0
 

Author Comment

by:jjenkel
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now