vba To Open 2 mdb and run some macro

[Minkowski]

Hello everyone i want to open 2 mdb's and run some macro's
i get the job done
but i get an error saying something about using OLE?!?

is the structure ok?open the first mdb execute the 3 macro
then open second mdb and run 1 macro
close

Dim A As Object
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase ("H:\Data\Sales.mdb")
A.DoCmd.RunMacro "Delete Data"
A.DoCmd.RunMacro "Import Sales"
A.DoCmd.RunMacro "Append to Master Table"
A.CloseCurrentDatabase
'A.Quit
'A.OpenCurrentDatabase ("H:\Data\Sales2.mdb")
'A.DoCmd.RunMacro "Append to ALL Sales"
'A.CloseCurrentDatabase
A.Quit
Set A = Nothing

thanks in advance

 

[Minkowski]

would this work?

Dim A As Object
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase ("H:\Data\Sales.mdb")
A.DoCmd.RunMacro "Delete Data"
A.DoCmd.RunMacro "Import Sales"
A.DoCmd.RunMacro "Append to Master Table"
A.OpenCurrentDatabase ("H:\Data\Sales2.mdb")
A.DoCmd.RunMacro "Append to ALL Sales"
A.CloseCurrentDatabase
A.Quit
Set A = Nothing

 

[xenou]

Works for me. But DoCmd.RunMacro in Access runs a Macro, not just a vba subroutine. If its a subroutine in a standard module use Application.Run.

It's better to makes applications visible when you are testing.

ξ

 

[Norie]

Why not add a form to the database(s) that opens automatically on startup, runs the macros then closes the database?

By the way are the 2 databases linked? Could they not be combined?

Or could you not use linked tables in one of them for the tables in the other one.

Then, depending on what your macros do exactly, you wouldn't need to open both databases.

Actually to be honest, I really don't think it's a good idea to do something like this - it just seems like there could cause an increased chance of running into problems.

Perhaps not immediately but if this is done on a regular basis you could end up with 2 corrupt databases.

Just my thoughts.

 

[Minkowski]

The problem is capacity
i could make 1 database out of them but the limitation of 2 gb
won't let me (yes office 2003)

so untill i am ready to move them to SQL server
i have to make the best out of this.
they are linked and i don't have any other choice unfortunatelly

you scared me a little bit with the problem part
what exactly could go wrong?

 

[xenou]

If the tables in the other (second or third) databases are linked you can run the code in the first database - a linked table will work like a local table as far as running queries, updates, deletes, etc. This would avoid the need to literally open other databases. But I can't see how opening a database and running a macro is in and of itself risking corruption.

ξ

 

[Minkowski]

thank you both for your time!

 

[Norie]

Sorry for scare-mongering a bit, it just doesn't seem like a particularly reliable set-up to me.

That's my opinion of course, so can be taken with a large pinch of salt.

I can't think of anything specific since I don't know enough about your setup.

Anyway, if what you have works that's the most important thing just make sure you make regular back-ups.

By the way, if you really do have that amount of data it really is time to consider using some other database.

Since it's Access you are using perhaps SQL Server could be a good choice, it might make transition easier.

I know in Access 2010 there's an option to basicaly create an SQL Server database from your Access database.

I've only tried it a couple of times just to see how it works, and it seemed OK.

Of course, I had no way near as much data as you have.

By the way, before moving from Access it might be worth looking at how you currently have things setup.

You should probably do that even if you are going to upgrade.

PS This is probably a stupid question - are you regularly running Compact and Repair on the databases?

 

[Minkowski]

at least once a week (and i delete older data and have the raw data as backup)
the IT is going at some point to install office 2010

and allready they set up for me an account for MS SQL 2008
but i started from zero on this subject
so i'm taking baby steps
i tested migrating 2 major tables into SQL (worked well)
(i am at a 10% of re-writing all my queries in T-SQL)

by the way it is amazing how fast everything runs there
and how a total size of 100gb isn't such a big deal...lol
good stuff

as always thanks for your time Norie!

 

[Norie]

Are you compacting and repairing the databases?

Even if you delete data/tables/other objects, especially with code, they can still sort of be there.

Or at least Access 'thinks' they are.

That doesn't quite make sense I know and I'm not good with technical explanation but it's just the case.