Solved

In VB6 and ACCESS how do I run ACCESS Macros and queries using VB code (I use recordset, etc)

Posted on 2007-11-27
2
910 Views
Last Modified: 2013-11-27
I program in VB 6 using ACCESS by using recordset commands (movefirst, movelast, Addnew, etc.).  I now need to be able to execute macros and queries withing the database using VB code.  With Currendb set to the table I need to be able to run the Macros and queries.  I know how to convert a macro into a vb function and insert it into a VB program, how ever it dos not work.  What references and components must I have installed.  I have Microsoft Access 11.0 Object Library on. as well as DAO 3.6.
0
Comment
Question by:Greinerjr
2 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 20364751
I think you should decide which products you are using for each part of your application.
You can use Access/Jet for the data store.
Then you decide what you are building your app with -  Access or VB.
If you are using VB then you don't want any part of the app in Access otherwise you have to install a copy of Access on each machine that runs your app as well as having to deal with the automation of Access objects.
VB apps are a lot easier to distribute than Access apps and there are is lots more functionality but you don't have the rapid development for database related features.
If you are installing Access anyway then you can develop the whole app in Access and it is likely to be a lot quicker to develop because of the easier forms and report creation.

At the moment you seem to have the worst of both worlds instead of the best of them.

0
 
LVL 2

Accepted Solution

by:
tonym001 earned 250 total points
ID: 20390153
To run a macro you do not need a references to the Microsoft Access 11.0 Object Library or the DAO 3.6. However, you might need the DAO 3.6 for your other operations. To run a macro in Access, Access has to be installed on the local PC and there is no way round that.

'Create a Access object
Dim objAccess As Object
Dim strMacro as String
Dim str

strMacro = "Test1"
Set objAccess = CreateObject("Access.Application")
'run behind the scenes
objAccess.Visible = False
'Open the database and run the macro
objAccess.OpenCurrentDatabase (strDbName)
'to run a macro
objAccess .DoCmd.RunMacro strMacro
'to run a
DoCmd.RunMacro
objAccess.CloseCurrentDatabase
Set objAccess = Nothing

To run a stored query you need a reference to the Microsost ActiveX Data Objects 2.xx the go to
http://forums.devx.com/archive/index.php/t-12498.html
Although this is in ASP it is easily changed

Hope this helps

0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

820 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