Using a Query instead fo a table in ADO.

[ShawnR]

I want to be able to directly access a data source without having to add a control like a combo box to a form. I have been sucessful in accessing tables with the following code.

Dim rst as ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select field1 from TableName"

If rst!field1 = 25 then
perform some action
End If

The above works great.

However I have not been successful in linking /using a query. I have not been able to get the code to work.

Any ideas what I am doing wrong? I have a saved query in Access "on the front end of a split database" that I want to call to return certain records. I tried copying the actualt SQl code fomr the query into the above "open" statement and that didn't produce any results.

Thanks for the Help!

 

[cmmrfrds]

I assume you are using Access 2000 or above.

Here is one way to execute a query - they are under VIEWS in the catalog. You will need a reference to the ADOX library. Create an instance of view and set a reference to your view name.

''- Microsoft ADO Ext. 2.6 for DDL and Security
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection

Dim v As View
Dim vn As View
For Each v In cg.Views
Debug.Print "views = "; v.Name
If v.Name = "query1" Then
Set vn = v
End If
Next

rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly

 

[BlackKnight]

Hi,

You can try this. Modify as needbe.

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim DataDBPath As String, strConn As String

'Where ever your data database is located.

DataDBPath = "C:\dbdata.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[Src];Persist Security Info=False"

strConn = Replace(strConn, "[Src]", DataDBPath)
conn.Open (strConn)
rst.Open "SELECT * FROM tblCustomer IN 'dbdata.mdb';", conn, adOpenKeyset, adLockPessimistic

If rst.EOF And rst.BOF Then
MsgBox "No records"
Else
With rst
MsgBox .Fields("Name&quot
.Close
End With
End If
Set rst = Nothing: conn.Close: Set conn = Nothing

Have a good one!
BK

 

[ShawnR]

Thank guys. I'm still having a few problems. Here is my code. I know it has to be something simple. I do appreciate your help.

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim DataDBPath As String, strConn As String

DataDBPath = "\\Chi-fg-op-fp-1\Dept\Helpdesk\Contact.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[Src];Persist Security Info=False"

strConn = Replace(strConn, "[Src]", DataDBPath)
conn.Open (strConn)
rst.Open "SELECT WhatWeDidID FROM qWWD25and152 IN 'contact.mdb';", conn, adOpenKeyset, adLockPessimistic

If IsNull(rst!WhatWeDidID.Column(0, 1)) And rst!WhatWeDidID.Column(0, 0) = 25 Then
MsgBox "'Recieved Production Support' Task not _ entered.", , "Opps! Please Check"
Status = 1
Text89 = Null
Status.SetFocus
Else
Perform other stuff
End If

qWWD25and152 is a query not a table. It brings in 2 different values that have potentially 2 rows. One with 152 and 25. Do I need to dicate the row someplace?

Here is the data in the query:

WhatWeDidID CallID
25 8812
152 8812

Or it could be this that would trigger the first part of the If statement.

WhatWeDidID CallID
25 8811

 

[ShawnR]

Thank guys. I'm still having a few problems. Here is my code. I know it has to be something simple. I do appreciate your help.

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim DataDBPath As String, strConn As String

DataDBPath = "\\Chi-fg-op-fp-1\Dept\Helpdesk\Contact.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[Src];Persist Security Info=False"

strConn = Replace(strConn, "[Src]", DataDBPath)
conn.Open (strConn)
rst.Open "SELECT WhatWeDidID FROM qWWD25and152 IN 'contact.mdb';", conn, adOpenKeyset, adLockPessimistic

If IsNull(rst!WhatWeDidID.Column(0, 1)) And rst!WhatWeDidID.Column(0, 0) = 25 Then
MsgBox "'Recieved Production Support' Task not _ entered.", , "Opps! Please Check"
Status = 1
Text89 = Null
Status.SetFocus
Else
Perform other stuff
End If

qWWD25and152 is a query not a table. It brings in 2 different values that have potentially 2 rows. One with 152 and 25. Do I need to dicate the row someplace?

Here is the data in the query:

WhatWeDidID CallID
25 8812
152 8812

Or it could be this that would trigger the first part of the If statement.

WhatWeDidID CallID
25 8811

I've tried other variations of the If statement. If I change the open statement to a table it works. I have even attempted to build a where clause into the open statement and it works in a query but on in the open statement.

 

[cmmrfrds]

You don't say what the problem is so it will be difficult to come up with the answer other than a quess.

 

[ShawnR]

I figured it out. I appears the same value cannot be queried twice. So i added a second open statement witha diff query set and it then worked.