Solved

run access query in vb

Posted on 2004-11-01
180 Views
Last Modified: 2008-03-06
I have an Access Database (Suite.mdb) with a VB 6.0 front end.  I would like to run a query in the database (tblContactsQuery) from the VB front end.  Any ideas?  Also, the database is attached with an ADODC to the VB front end.

thanks!
0
Question by:drewderocco
    6 Comments
     
    LVL 34

    Expert Comment

    by:flavo
    Hi drewderocco,

    example (not the best with ado, so im not sure about the connection string

    Dim db As ADODB.Connection
    Set db = New ADODB.Connection

    'set connection

    db.Execute sMyUpdateQuery

    Idea???

    Dave
    0
     
    LVL 12

    Accepted Solution

    by:
    Here is the syntax

    Private Sub Form_Load()
    Dim ProductsQuery As String
    Dim cn As ADODB.Connection
    Dim cmd As String
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<c:\aaa testing>\Suite.mdb;User Id=admin;Password="

    cmd = "tblContactsQuery"

    cn.Execute cmd

    End Sub


    Bob
    0
     
    LVL 12

    Expert Comment

    by:BobLamberson
    add your path to the mdb where I have    <c:\aaa testing>

    Bob
    0
     

    Author Comment

    by:drewderocco
    sorry, I should have been more specific.  When I ask this query to run, the command in VB is in the menu of the form.  I have listed "Query Contacts" as the verbage in the menu.  When I choose this, I would like the query to run in access.

    thanks!
    0
     
    LVL 12

    Expert Comment

    by:BobLamberson
    go to the code behind the form and in the left hand drop down box, select the menu option name "Query Contacts". Then in the right hand dropdown list select the Query Contacts_Click event and enter the code so it will end up looking like this>

    Private Sub Private Sub QueryContacts_Click()    ' **NOTE HERE that your event name may be different from the display name.
    Dim ProductsQuery As String
    Dim cn As ADODB.Connection
    Dim cmd As String
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<c:\aaa testing>\Suite.mdb;User Id=admin;Password="

    cmd = "tblContactsQuery"

    cn.Execute cmd
    End Sub

    **You can find the event name of the menu option by going to the form, right clicking, left click Menu Editor, select the menu option with the caption "Query Contacts" and see what the Name is.

    -Bob
    0
     

    Author Comment

    by:drewderocco
    Bob:
    Works like a charm.  Thanks!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Product Review - Android Remix

    Come along for the ride with our Senior Product Manager, Brian Matis, as he reviews the Android Remix.

    Suggested Solutions

    Title # Comments Views Activity
    Access-SQL 6 42
    Hide Subreport if no records 7 15
    Linked Excel Spreadsheet 13 43
    Syntax error in Alter Table statement 8 0
    In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
    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…
    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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    934 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