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

Posted on 2005-04-11
Medium Priority
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
  • 2
  • 2
  • 2
  • +1
LVL 54

Expert Comment

by:Ryan Chong
ID: 13758966
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 54

Expert Comment

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

Author Comment

ID: 13759100
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!
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Accepted Solution

sk33v3 earned 600 total points
ID: 13759278
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")

Expert Comment

ID: 13759549
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
G_Connection.open "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

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

thanks a lot!


Expert Comment

ID: 14365678
Matthews did you try the source code I submitted?

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month13 days, 9 hours left to enroll

750 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