how to execute an SQL statement in MS Access 2000 - 2003?

Posted on 2005-04-11
Last Modified: 2010-05-02
i have a form
i have a textbox1, and
i have a textbox2 (page result).

i need to write an SQL like 'select * from products where object_id=100'

and the result must be written in the textbox2 (result page).

thanks a lot!
Question by:matthews_30
    LVL 48

    Expert Comment

    by:Ryan Chong
    try have a simple solution by using DLookup function, like:

    Open the form in design view, then open the Control Property, then go to the Data tab, at the Control Source Property, enter:


    hope this helps, regards
    LVL 48

    Expert Comment

    by:Ryan Chong
    sorry, is you want to do it inside Access or it's a Visual Basic application?

    Author Comment

    i will explain better...

    ok. in visual basic i have an application that opens a msaccess database. this application is for controlling a cyber-cafe. it update the information properly, but i can not create a report because i dont know how to capture the result of an SQL:

    table is called 'main', it contains the table 'machine_no', 'paid_value', 'date', 'cashier'

    the SQL statment would be the following:

    select date, machine_no, paid_vale from main where date=04/11/2005 and cashier=matt'

    thanks a lot!
    p.s. remember it is for a Visual basic 6 program!
    LVL 9

    Accepted Solution

    ok first thing is first you need to be connecting to the database via ADO, well you probably don't have to but I would suggest it. Second create your ADODB.connection object. Then once you have your database connection setup you just need to do this

    dim RS as recordset
    set rs=connection.execute("This is where your SQL goes.")
    do until rs.eof
         me.textbox2.text=me.textbox2.text & vbcrlf & rs("Field1")
    LVL 1

    Expert Comment

    you can use some generalised function to get a single value  

    Public Function FindDesc(p_strSql As String) As Variant
       Dim tmp
       tmp = Screen.MousePointer
       Screen.MousePointer = vbHourglass
       Dim lrs As ADODB.Recordset
       Set lrs = New ADODB.Recordset
       lrs.Open p_strSql, G_Connection, adOpenForwardOnly, adLockReadOnly
       If lrs.EOF = True And lrs.BOF = True Then
          ' Nothing
          FindDesc= ""
          FindDesc = Trim(lrs.Fields(0).Value & "")
       End If
       p_strSql = ""
       Set lrs = Nothing
       Screen.MousePointer = tmp
    End Function

    pass theSelect Query You want to select as p_strSql
    here Connection is Connection objects that is already opened with Database
    you can open connection like this way on application startup

    Public  G_Connection as New ADODB.Connection "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;Persist Security Info=False "

    I hope this will help you


    Author Comment

    can you please explain step by step, it is not working for me.

    thanks a lot!

    LVL 9

    Expert Comment

    Matthews did you try the source code I submitted?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    761 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

    6 Experts available now in Live!

    Get 1:1 Help Now