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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…

816 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