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
907 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

Backup Your Microsoft Windows Server®

Backup 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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

930 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

8 Experts available now in Live!

Get 1:1 Help Now