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
911 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

738 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