ADO and SQL Query

Posted on 2000-03-30
Last Modified: 2013-11-23
I'm using VB6, Access 97, and the ADO data environment.
I want to run a query on a table titled Confirmed. I want this query to return all the records in that table where the field 'Archived' = No. I then want to access the results returned from the query and display certain fields (fields such as RoomID) from the results in a listbox using ADO (and the data environment). I don't mind if it's hard coded or done in the data environment using a command, but it must be using ADO.
How do I do this?

Question by:Megan
  • 3
  • 2

Accepted Solution

setiawan earned 120 total points
ID: 2672246
Dim objConn as New ADODB.Connection
Dim objRec as New ADODB.Recordset
Dim sql as string

ObjConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\db1.mdb"

sql = "Select * from confirmed where Archived=" & No
ObjRec.Open sql, 2 , 1

That's it

Now, you can do some operation with recordset object

objRec.MoveFirst ' goto first rec
objRec.MoveNext  ' goto next rec
objRec.MoveLast  ' goto last rec

if you want to dispaly the result in the list box, you can do like this :

do while objRec.EOF
   list1.addItem objRec.Fields("RoomID")

'dont forget to close the connection and recorset object when you finished

set objRec = Nothing
set objConn = Nothing

hope this helps :)


Expert Comment

ID: 2672249
sorry there is some mistake
>ObjRec.Open sql, 2 , 1

should be
objRec.Open sql, objConn, 2, 1


LVL 14

Expert Comment

ID: 2672916
Hello Megan.. <smile>

Open a Standard.exe project, add a Listbox (List1), and
Change the .Connectionstring = "c:\FilePath\MyDatabase.mdb" and the strArchive = "2" to use your MDB settings.

<----- Code Begin ----->

Option Explicit

Private Sub Form_Load()
  Dim cnAccess As New adodb.Connection
  With cnAccess
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "c:\FilePath\MyDatabase.mdb" ' <---- Change
  End With

  Dim strArchive as String
  strArchive = "2" ' <---- Change
  MsgBox ("Record Count: " & xLoadArchive(strArchive, cnAccess))

End Sub

Private Function xLoadArchive _
(ByVal strArchiveKey As String, ByRef cnConnection As adodb.Connection) _
As Long

  Dim intCount As Long
  Dim rsArchive As New adodb.Recordset
  With rsArchive
    .ActiveConnection = cnConnection
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .Source = "Select RoomID From Confirmed " _
      & "Where Archived = " _
      & strArchiveKey
    .Open , , , , adCmdText
    Do Until .EOF
      List1.AddItem .Fields(0) ' or whatever field you want
      intCount = intCount + 1
  End With
  xLoadArchive = intCount

End Function

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why


Author Comment

ID: 2674562
I've implemented all the code you suggested, but I'm getting an error: "Syntax error (missing operator) in query expression 'Archived='."
This is part of what i've written.

SQLQuery = "Select * from Confirmed where Archived= " & No
rsConfirmed.Open SQLQuery, cnDbase, 2, 1

Also what does the 2, 1 mean? Do you know what's wrong with the query?
I've tried to change the query to... SQLQuery = "Select * from Confirmed where (Archived = ('" & No & "'))"
but it doesn't work either...

Any ideas??
tnks. Meg.

Author Comment

ID: 2674674
Disregard my last comment i've figured it out! :)

this is the way that works...
SQLQuery = "SELECT * FROM Confirmed WHERE Archived='No'"
rsConfirmed.Open SQLQuery, cnDbase, 2, 1

Do While rsConfirmed.EOF = False
   cmbRoom.AddItem rsConfirmed.Fields("AssetRoomID")

Set rsConfirmed = Nothing
Set cnDbase = Nothing
I still don't understand what the 2, 1 is doing. Could you explain that for me please?..
Thanks sooo much for the help! <Grin>

Expert Comment

ID: 2678580
Hi Megan,

CursorType   Optional.
A CursorTypeEnum value that determines the type of cursor that the provider should use when opening the Recordset. Can be one of the following constants (see the CursorType property for definitions of these settings).

Constant Description
adOpenForwardOnly (Default) Opens a forward-only–type cursor.
adOpenKeyset Opens a keyset-type cursor.
adOpenDynamic Opens a dynamic-type cursor.
adOpenStatic Opens a static-type cursor.

LockType   Optional. A LockTypeEnum value that determines what type of locking (concurrency) the provider should use when opening the Recordset. Can be one of the following constants (see the LockType property for more information).

Constant Description
adLockReadOnly (Default) Read-only—you cannot alter the data.
adLockPessimistic Pessimistic locking, record by record—the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.
adLockOptimistic Optimistic locking, record by record—the provider uses optimistic locking, locking records only when you call the Update method.
adLockBatchOptimistic Optimistic batch updates—required for batch update mode as opposed to immediate update mode.

ps : 2 -> adOpenDynamic (cursor type)
     1 -> adLockReadOnly (lock type)

hope this helps


Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
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.
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…
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…

777 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