• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

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

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!
0
matthews_30
Asked:
matthews_30
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Ryan ChongCommented:
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:

=DLookUp("fieldtobedisplay","products","object_id=100")

hope this helps, regards
0
 
Ryan ChongCommented:
sorry, is you want to do it inside Access or it's a Visual Basic application?
0
 
matthews_30Author Commented:
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!
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
sk33v3Commented:
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")
     rs.movenext
loop
0
 
aryaomniCommented:
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= ""
   Else
      FindDesc = Trim(lrs.Fields(0).Value & "")
   End If
   p_strSql = ""
   lrs.Close
   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

0
 
matthews_30Author Commented:
can you please explain step by step, it is not working for me.

thanks a lot!

matt.
0
 
sk33v3Commented:
Matthews did you try the source code I submitted?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now