Solved

ADO and SQL Query

Posted on 2000-03-30
6
211 Views
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?
PLEASE HELP :)   :)

Thnks!
Megan.
0
Comment
Question by:Megan
  • 3
  • 2
6 Comments
 
LVL 6

Accepted Solution

by:
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 :

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

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

objRec.close
objConn.close
set objRec = Nothing
set objConn = Nothing

hope this helps :)

  danny
0
 
LVL 6

Expert Comment

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

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

  danny


0
 
LVL 14

Expert Comment

by:wsh2
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
    .Open
  End With

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

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
      .MoveNext
    Loop
    .Close
  End With
  xLoadArchive = intCount

End Function



0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Megan
ID: 2674562
setiawan,
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.
0
 

Author Comment

by:Megan
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
 
rsConfirmed.MoveFirst

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

rsConfirmed.Close
cnDbase.Close
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>
Megan.
0
 
LVL 6

Expert Comment

by:setiawan
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

   danny
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Run code from text file in vb 1 56
using web browser with BING 40 110
VB6 ListBox Question 4 30
VB 6 error 5 in windows 10 but not in XP 7 39
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

932 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

11 Experts available now in Live!

Get 1:1 Help Now