Improve company productivity with a Business Account.Sign Up

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

ADO and SQL Query

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?

  • 3
  • 2
1 Solution
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 :)

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

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


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

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.

MeganAuthor Commented:
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.
MeganAuthor Commented:
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>
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

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

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.

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