Megan
asked on
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?
PLEASE HELP :) :)
Thnks!
Megan.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello Megan.. <smile>
Open a Standard.exe project, add a Listbox (List1), and
Change the .Connectionstring = "c:\FilePath\MyDatabase.md
<----- 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.md
.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
Open a Standard.exe project, add a Listbox (List1), and
Change the .Connectionstring = "c:\FilePath\MyDatabase.md
<----- 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.md
.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
ASKER
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.
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.
ASKER
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("AssetR
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.
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("AssetR
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.
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
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
>ObjRec.Open sql, 2 , 1
should be
objRec.Open sql, objConn, 2, 1
danny