Run An Access Query From VB

[ktwclark]

Hi

I'm a bit confused about connecting to an existing Access 97 database through RDO or ADO and being able to run queries, etc that have already been set up in the database.

Can anyone give me some pointers, please.

thx

kc

 

[nicsin]

sure. add a reference to Microsoft ActiveX Datra Objects Library and use the following code

Dim con as ADODB.Connection
Dim rs as ADODB.Recordset
'construct data objects
set con = New ADODB.Connection
set rs = New ADODB.Recordset
'open the connection to the database
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb"

with rs
    'populate the recordset
    .Open "select field1 from table1", con
    'check if empty
    if not (.bof and eof) then
        .MoveFirst
        'output the contents
        do until .eof
            msgBox rs(0)
            .MoveNext
        loop
    else
        msgBox "db returned nothing"
    end if
end with

rs.Close
set rs = Nothing
set con = Nothing

You could use the same connection object throughout your peoject to populate recordsets. You could even use the same recordset as long as you close it before you open it again with a new query. If you need to use more than one recordsets concurrently then create more.

 

[ktwclark]

thx for the reply. I've been using code similar to the above for producing recordsets but was wondering if I can run a query that is already set up in the database or is it easier/quicker to create a new recordset through VB. I have queries set up already that work within Access but when I set a bound control to the query at design time I get an error saying there is an error in the FROM clause, although the query executes fine in Access.

 

[nicsin]

I have used

select * from myQuery

and it works fine. If you want paste your query here (sql) because there could be a problem with it.

 

[jbradley]

You can open saved queries directly. Just as you could replace

rs.Open "SELECT * FROM TableName", YourConnection

with

rs.Open "TableName", YourConnection

you can also replace

rs.Open "SELECT Field1, Field2 FROM TableName WHERE Field1 = 'Something'", YourConnection

with

rs.Open "YourSavedQueryName", YourConnection

 

[ktwclark]

Thanks for all the replies.

But I'm having a problem now executing delete queires already set up in the database.

I get an error message "The connection cannot be used to perform this operation. The connection is either closed or invalid."

I've used the code above and called a delete query with the above error.

Any ideas. Locked recordset type?

 

[GAORR]

Is this what you are looking for?

You have queries set up for an access database that you want to use them via VB. Have a look at my example below. It is part of a DLL (data tier) that uses a query that has been set up in Access. The query 'GetAllCustomers' is set up in access (retrives all customers from the database 'videodvd.mdb').I'm hoping you will figure it out after reviewing the code. This is part of an app (from a VB6 project course I took which uses 3 tiers - the user interface, a business tier and the data tier. The example loads a diconnected recordset which is passed back to the business tier and then to the user interface to be displayed to the user.

If you have other questions concerning this, please ask, and I will try to help.

-----
code
-----
Public Function GetAllCustomers() As Recordset

' this procedure retreives all customer fields for all customers

Dim conVideoDvd As ADODB.Connection ' Database connection
Dim cmdGetCustomers As ADODB.Command ' Database command
Dim rstCustomers As ADODB.Recordset ' Customers

On Error GoTo GetAllCustomers_Error

' determine the database location
mDBLocation = GetSetting("UI", "DBLocation", "Location", "&quot

If mDBLocation = "" Then
mDBLocation = App.Path & _
IIf(Not (Right$(App.Path, 1) = "\&quot, "\", "&quot & _
"videodvd.mdb"
End If

' establish the database connection
Set conVideoDvd = New ADODB.Connection
With conVideoDvd
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & mDBLocation
.Open
End With

' establish the database command
Set cmdGetCustomers = New ADODB.Command
With cmdGetCustomers
Set .ActiveConnection = conVideoDvd
.CommandText = "GetAllCustomers"
.CommandType = adCmdStoredProc
End With

' retrieve the customers as a disconnected recordset
Set rstCustomers = New ADODB.Recordset
With rstCustomers
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Open cmdGetCustomers
Set .ActiveConnection = Nothing
End With

' release the resources
conVideoDvd.Close
Set cmdGetCustomers = Nothing
Set conVideoDvd = Nothing

Set GetAllCustomers = rstCustomers

' release the resources
Set rstCustomers = Nothing

Exit Function

GetAllCustomers_Error:

Err.Raise vbObjectError + 1200, Err.Source, Err.Description

End Function

 

[gwar2k1]

Hey saw the thread and Im having troubles with searching too. I have a list box that should, in theory, display the barcode and title of every magazine held in the table. There are currently 5 saved, viewable in Access, but when the code is run it produces this error:

Either BOF or EOF is true, or the current record has been deleted.

Um? This is my code:

ProdRec.Open "SELECT * FROM Product", Cnct, adOpenKeyset, adLockReadOnly
While Not (ProdRec.BOF And ProdRec.EOF)
form1.lstResults.AddItem ProdRec("EAN 8/13&quot & vbTab & ProdRec("Title&quot
ProdRec.MoveNext
Wend

Im still fresh to VB so im very appreciative of the help ive been getting from the VB board members =D

~*Gwar3k1*~
"To the pressure, everything's just like: an illusion. I'll be losing you before long..."

 

[nicsin]

Gwar3k1,

change it to this and it should work:

    ProdRec.Open "SELECT * FROM Product", Cnct, adOpenKeyset, adLockReadOnly
    if Not (ProdRec.BOF And ProdRec.EOF) then
        ProdRec.MoveFirst
        While Not ProdRec.EOF
            form1.lstResults.AddItem ProdRec("EAN 8/13") & vbTab & ProdRec("Title")
            ProdRec.MoveNext
        Wend
    else
        msgBox "Empty recordset"
    end if
    ProdRec.Close

 

[gwar2k1]

thanks for your reply, i made the change and it works... well it runs anywho. its not adding anything to the list box =S however i added in a msgbox and that displays the info correctly. any ideas?

ProdRec.CursorType = adOpenDynamic
ProdRec.LockType = adLockOptimistic

ProdRec.Open "SELECT * FROM Product", Cnct, adOpenKeyset, adLockReadOnly
If Not (ProdRec.BOF And ProdRec.EOF) Then
ProdRec.MoveFirst
While Not ProdRec.EOF
OutStr = ProdRec("EAN 8/13&quot & vbTab & ProdRec("Title&quot
form1.lstResults.AddItem (OutStr)
MsgBox (OutStr)
ProdRec.MoveNext
Wend
Else
MsgBox ("not found&quot
End If

ProdRec.Close

~*Gwar3k1*~
"To the pressure, everything's just like: an illusion. I'll be losing you before long..."

 

[martinb7]

nicsin: "add a reference to Microsoft ActiveX Datra Objects Library"

Where do i add that and where from?? im running VB 6

thanx


Martin

Computing help and info:

http://www.webrevolt.biz

 

[gwar2k1]

In VB6 Alt+P or click Project at the top, go to references. Check these:

Microsoft Active X Data Objects 2.1 Library (or higher)
Microsoft ADO Ext. 2.5 for DLL and Security
Microsoft DAO 2.5/3.51 Compatibility Library

=D

~*Gwar3k1*~
"To the pressure, everything's just like: an illusion. I'll be losing you before long..."

 

[martinb7]

thanx

ps how do you make it return more than one result from the table?

and how would you go about listing them in a list box??

any ideas??

thanx


Martin

Computing help and info:

http://www.webrevolt.biz

 

[gwar2k1]

yeh. by more than one result i guess you mean multiple records? I have a view all procedure that has this snippet of code. this was constructed with the help of members from tek-tips

ProdRec.Open "SELECT * FROM Product", Cnct, adOpenKeyset, adLockReadOnly
If Not (ProdRec.BOF And ProdRec.EOF) Then
ProdRec.MoveFirst
While Not ProdRec.EOF
OutStr = ProdRec("EAN 8/13&quot & vbTab & ProdRec("Title&quot
form1.lstViewAll.AddItem (OutStr)
ProdRec.MoveNext
Wend
Else
MsgBox ("not found&quot
End If

~*Gwar3k1*~
"To the pressure, everything's just like: an illusion. I'll be losing you before long..."