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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.


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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

746 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

12 Experts available now in Live!

Get 1:1 Help Now