Run macro in vba

[wk6pack]

Hi,

I have an access database 1 opened. I'm trying to run a macro or query in
another access database 2.

I wrote some code in the modules to open the connection. When I try to call
the macro or query in database 2, I get an error. Also, the SetWarnings
gives me an error. Compile error: Argument not optional. I dont want the
user to see any warnings.
Why cant I run those macros or queries?
Error for runMacro: Run-time error '2485' Microsoft Office Access cant find
the macro 'zautoexec'

If I do a SQL statement it works.

Instead of opening a connection, I think it would be better if I could just
open the database and let it run the autoexec. How can this be done?

here is the code.

Set MyDB = New ADODB.Connection
MyDB.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='p:\budgetdb\prelim\Budget" & Right(Year(Now) + 1, 2) &
Right(Year(Now) + 2, 2) & "_data.mdb';")
DoCmd.SetWarnings = False
' DoCmd.OpenQuery "Deletez_OldBudgetTable"
DoCmd.RunMacro "zautoexec"
' DoCmd.RunSQL "delete * from [z_old budget]"
' DoCmd.RunSQL "INSERT INTO [z_OLD Budget] SELECT Budget.* FROM
Budget;"
' MySet.Close
MyDB.Close
' Set MySet = Nothing
Set MyDB = Nothing

thanks,
Will

 

[Alex Dybenko]

Hi,
you have to run execute method instead docmd.runsql:
Set MyDB = New ADODB.Connection
MyDB.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='p:\budgetdb\prelim\Budget" & Right(Year(Now) + 1, 2) &
Right(Year(Now) + 2, 2) & "_data.mdb';")
MyDB.execute "Deletez_OldBudgetTable"
MyDB.execute "delete * from [z_old budget]"
MyDB.execute "INSERT INTO [z_OLD Budget] SELECT Budget.*
FROMBudget;"
MyDB.Close
Set MyDB = Nothing


HTH

 

[wk6pack]

Thanks Alex.

Will

Hi,
you have to run execute method instead docmd.runsql:
Set MyDB = New ADODB.Connection
MyDB.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='p:\budgetdb\prelim\Budget" & Right(Year(Now) + 1, 2) &
Right(Year(Now) + 2, 2) & "_data.mdb';")
MyDB.execute "Deletez_OldBudgetTable"
MyDB.execute "delete * from [z_old budget]"
MyDB.execute "INSERT INTO [z_OLD Budget] SELECT Budget.*
FROMBudget;"
MyDB.Close
Set MyDB = Nothing


HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Hi,

I have an access database 1 opened. I'm trying to run a macro or query in
another access database 2.

I wrote some code in the modules to open the connection. When I try to
call
the macro or query in database 2, I get an error. Also, the SetWarnings
gives me an error. Compile error: Argument not optional. I dont want
the
user to see any warnings.
Why cant I run those macros or queries?
Error for runMacro: Run-time error '2485' Microsoft Office Access cant
find
the macro 'zautoexec'

If I do a SQL statement it works.

Instead of opening a connection, I think it would be better if I could
just
open the database and let it run the autoexec. How can this be done?

here is the code.

Set MyDB = New ADODB.Connection
MyDB.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='p:\budgetdb\prelim\Budget" & Right(Year(Now) + 1, 2) &
Right(Year(Now) + 2, 2) & "_data.mdb';")
DoCmd.SetWarnings = False
' DoCmd.OpenQuery "Deletez_OldBudgetTable"
DoCmd.RunMacro "zautoexec"
' DoCmd.RunSQL "delete * from [z_old budget]"
' DoCmd.RunSQL "INSERT INTO [z_OLD Budget] SELECT Budget.* FROM
Budget;"
' MySet.Close
MyDB.Close
' Set MySet = Nothing
Set MyDB = Nothing

thanks,
Will