Solved

Running a macro to run a macro

Posted on 2011-09-05
26
234 Views
Last Modified: 2012-05-12
Hi,

Having the following problem:

I have separate databases (all very similar in structure but disimilar data and all used by a different user). However I have to extract data from all five databases.

So my idea which I 've worked on a bit is :

I've run a make table query in each of the separate databases thereby creating a specific table in each of the databases.

I have then gone and created a separate program in Access and written a macro which imports all the separate five tables into this one database. And then run a query to join up all five table into one. So far so good.

I now have a separate program that can "stitch" together five tables into one but I am reliant that each separate user run their query to create the initial table.

Now I was wondering whether I could run a macro of some sort or possibly write some VBA code where I could initiate the creation of the initial tables from the New database and therefore not rely on the individual user to do so?

0
Comment
Question by:PipMic
  • 13
  • 5
  • 5
  • +2
26 Comments
 
LVL 10

Expert Comment

by:plummet
ID: 36483774
What I'd do is to link the relevant tables to a central database, then run one macro to run a set of queries to pull the data together into one table. Then it's all under the control of one database and as long as the other 5 databases are available you don't need to worry about doing anything on them.

Hope that helps.
0
 

Author Comment

by:PipMic
ID: 36483852
Hi,

Yes, but the relevent table is one that has to be created by the user and that is what I want to avoid. This Table is created fro three other related tables.

Worse still, these three tables in all five databases are named the same, eg Table1, 2 and 3 in Databases A,
Table1, 2 and 3 in Databases B,
etc.

I hope that makes sense!

0
 
LVL 10

Expert Comment

by:plummet
ID: 36483878
When you attach the tables they will be renamed so that they're unique, and you can then rename them to be whatever you like. Then you don't need to create a temporary table in each database, you run the process in your central DB. Why do the job in 5 different locations when you can centralise and control it? The linked tables will remain and you can run the process/queries whenever you like.
0
 

Author Comment

by:PipMic
ID: 36484095
hi,

Dont quite understand what you mean.

THE PROCESS:
A table is created in each Database based on three different tables. This new table has a unique name.

This unique table is created or initiated by the user using the Database.

The above process is done for four other Databases resulting in Five separate tables.

_____________________________

I then have a newly created Database used by the administrator.
In it  I have a macro which imports all five separate tables which are then joined together.

So far so good!

However my problem lies in the fact that the user has to initiate the make table in his/hers database in order for the data that is to be imported to be up to date

I was there fore wondering whether it was possible to initiate the make table query (which is now done by the user) within the new Database.

Thanks


0
 
LVL 10

Expert Comment

by:plummet
ID: 36484148
Yes, as I have said:

For each of your 5 databases:
1) Attach the 3 tables to the central database
2) Call them something logical eg Customer_1, Contacts_1, Activities_1, Customer_2 (etc)
3) Create a query to append the information you want from these 3 tables to a central table

That's all you would need to do. You could write some code so you don't need 5 different queries to run, but that's more advanced. The entire process is then under your control.

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 36484441
If the tables have the same structure, you could make a union query to combine their data, instead of appending it to a new table.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36485826
<Now I was wondering whether I could run a macro of some sort or possibly write some VBA code where I could initiate the creation of the initial tables from the New database and therefore not rely on the individual user to do so?>
If this is your main question then you can do this any number of ways.
Create a function to run the code, and put it in a Public Module

Function RunImport()
    Call YourImportCode '  "YourImportCode" represents the name of your sub that does the Importing
end function

Then create a macro named "Autoexec"
Action: RunCode
(Then under the Action Arguments:)
Function Name: RunImport()

Then this macro will run automatically each time the DB opens

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36485830
...to be on the safe side do this instead:

Public Function RunImport()
    Call YourImportCode '  "YourImportCode" represents the name of your sub that does the Importing
end function
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36485860
If you have an identical query which operates correctly in each of the five databases, then use that query in your local database in which you have added an IN clause after the FROM clause in which you provide the path to each successive remote mdb.

From Access Help

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
    FROM tableexpression [, ...] [IN externaldatabase]  <--  this contains the double-quoted path and mdb name
    [WHERE... ]
    [GROUP BY... ]
    [HAVING... ]
    [ORDER BY... ]
    [WITH OWNERACCESS OPTION]

On my machine:

IN "f:\downloads\remote1.mdb"

If the remotes are on a server, use the \\server name in place of a drive letter.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36485873
When you get it working for one, it is straightforward to create a union query to import all the data.
0
 

Author Comment

by:PipMic
ID: 36486811
Hi all,

Thanks for your contributions:

Plummet: I had misunderstood your comments partly because I didnt know that you could link tables and then rename them...I will reconsider this option :) if all else fails.

GRayl: Sounds ok ...but not quite sure how to do this??

Boag2000:  This option is much more appealing.

Function RunImport()
    Call YourImportCode '  "YourImportCode" represents the name of your sub that does the Importing
end function


What would an example of this code be?

Then create a macro named "Autoexec"
Action: RunCode
(Then under the Action Arguments:)
Function Name: RunImport()
Then this macro will run automatically each time the DB opens

This part I think I can manage. I have worked with the autoexec before.
0
 
LVL 10

Expert Comment

by:plummet
ID: 36486949
Hi PipMic

I hope you find a good solution. Whether you use VBA code to do it or queries, your best approach is to carry out all actions from the central database. I think using linked tables is the simplest and quickest way to do it, but I would write VBA code if it was my application.

Best regards
0
 

Author Comment

by:PipMic
ID: 36487143
Hi plummet,

Thanks. Just hoping someone can provide me with the simple code as suggested by boag2000 !

:)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Expert Comment

by:plummet
ID: 36487427
I might write it today if I get some time!
0
 

Author Comment

by:PipMic
ID: 36487547
Thanks  will be appreciated.
0
 

Author Comment

by:PipMic
ID: 36487570
Hi agian,

I would assume that it only be a two lines of code repeated five times for the 5 different databases, i.e.

1. run the query to create the table
2. to import the table to the Central database

I could also initiate it directly on an "as and when" basis using a button in the form of the central databases . The button would then have an event procedure with the same coding I've suggested previously.

Thanks
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36488742
My presumption was that you already had the code...

As you stated:
<I've run a make table query>
<I have then gone and created a separate program in Access and written a macro which imports all the separate five tables into this one database. And then run a query to join up all five table into one. So far so good.>

So if you want to run a macro form a macro the Action is: RunMacro
...and the Argument will be the name of the macro.

So basically you would create an autoexec macro that does exactly what you are doing already.

So if you have code that does everything, you can call it from a function in the Autoexec macro.

If you have three queries and code, then  run the three queries and code in the autoexec macro...

Jeff
0
 

Author Comment

by:PipMic
ID: 36491879
Hi,

Well not exactly...

I wrote:
<I've run a make table query>
<I have then gone and created a separate program in Access and written a macro which imports all the separate five tables into this one database. And then run a query to join up all five table into one. So far so good.>

The part : <I've run a make table query> is a query initiated by the user. This is what I am trying to avoid. I would like to run the entire procedure form the centralised database.

i.e.
1. I've run a make table query>  ------this is carried out by the user in the separate database
2.I have then gone and created a separate program in Access and written a macro which imports all the separate five tables into this one database. And then run a query to join up all five table into one. So far so good.----this I run from the central database

I would like to run both procedures in one go from the central Database!

My initial main problem was running either a macro or an event procedure from the Central Database. Your (boag2000) idea of creating a event procedure was ideal, which I presumed was what you meant when you wrote:

Public Function RunImport()
    Call YourImportCode '  "YourImportCode" represents the name of your sub that does the Importing
end function


Since I dont know how or never have run a macro to run a macro in a different database, I kind of liked your idea of writing some code which would do the execution of the make table and then the importation to the Central database and therefore do away with the centralized macro.

I hope I have made myself clearer...  :(
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 36493040
Then in the big picture you can always open another database form the "central DB" and do whatever you like:

You can do something like this in the "Central" DB:

Public Function TargetDBCommands()
Dim acc As Access.Application
    Set acc = New Access.Application
    acc.OpenCurrentDatabase "c:\YourFolder\TargetDB.mdb"
    acc.DoCmd.OpenQuery "qrymtCustomersx" 'This command runs the Make table query in the target/remote DB
End Function


So you see here, ...from the central DB you can do pretty much anything you like in any other DB
(even run commands in more than one target DB.)

The kicker here is that doing things "in another DB" always creates potential problems.
If the DB is already open
If the DB is being edited.
If the Location cannot be found
If the DB is of a different version
If the user runs the code at an inappropriate time
If you try to run a command that relies on settings in the "wrong" database
...and God help you if the network hiccups while you are doing any of this, (now will need iron-clad error handling and "Rollback" code...
:-O
...etc.

This is why most developers try to stay away from these "multiple DB" situations.
So consider combining these database if at all possible.

JeffCoachman

0
 

Author Comment

by:PipMic
ID: 36494015
Hi Boag2000,

Great advice!

having second thoughts now...may try it out for fun to see if it works but will have to reconsider given your concerns.

I should therefore ensure that none of the independent databases are being used or worked on when I carry out the centralised procedure.

As regards rollback code, this is not something I'm familiar with even though I have read about it before. I presume that keeping daily backups would help.


Grateful for your thoughts
Thanks
0
 

Author Comment

by:PipMic
ID: 36494496
Hi again,

Now this is going to seem ridiculous, but how do I

1. create a Public Function?

2. I have so far created a button on a form in the centralised database and was hoping to simulate  the above code (with my own folders etc) to the subroutine of the event procedure linked to the button I have created.

Hoping that by clicking the button I would initiate the routine. But as always, no luck.

Boag2000, I would be most grateful if you could assist me on this. I know this goes beyond the scope of my original question but it is nonetheless related.

:)

Thanks
0
 

Author Comment

by:PipMic
ID: 36494556
Hi again,

It would help too to know how to call up a Public Function!

:)
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 125 total points
ID: 36496214
<having second thoughts now...may try it out for fun to see if it works but will have to reconsider given your concerns.>

...also add to this any conflicts between any of the front ends and Backends of any of the databases involved...
;-)


<Now this is going to seem ridiculous, but how do I create a Public Function?>
Create a New module
In this Module, do something like this:
Public Function DoStuff()
   'Your Code here
End Function


Then from anywhere else in your app you can Call/Run this function by using something like this:

Sub YourSub()
    Call DoStuff
End sub
0
 

Author Comment

by:PipMic
ID: 36501106
Hi Boag2000,

Thanks for the info...will try it later today...will let you know how I get on  

Cheers!
0
 

Author Comment

by:PipMic
ID: 36710095
Hi boag2000,

Did not work but the idea sounds promising . Will not continue with this prob. other work beckons. Thanks again.
0
 

Author Closing Comment

by:PipMic
ID: 36710127
Good advice all round
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

706 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

20 Experts available now in Live!

Get 1:1 Help Now