Solved

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

Posted on 2014-10-02
10
201 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

23 Experts available now in Live!

Get 1:1 Help Now