Updating a Query Def from .Net

[Guest]

I am generating a Crystal Report based on an Access Query that uses queries
that uses other queries ... One query needs to be filtered on the fly and it
is not the main query called by the report. I cannot filter past this query
because its data is later grouped in order to be useful by the report. What
I thought I could do is modify the 'sub' query that needs a WHERE clause
added. However, I do not know how to alter Access queries held in my db. I
tried to delete the query and recreate it with the same name by using Drop,
but that doesn't work - Drop is for tables. I am having trouble seeing
outside the box here. What I need basicly is to filter data for my Crystal
Report that is hard-coded to use a main query.

 

[Brendan Reynolds]

You were on the right track with DROP, here's an example ...

Module Module1

Sub Main()

Dim strCnn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\usenet\db1.mdb;" & _
"Persist Security Info=False;" & _
"Jet OLEDBatabase Password=password"
Dim cnn As New System.Data.OleDb.OleDbConnection(strCnn)
Dim cmd As System.Data.OleDb.OleDbCommand
Try
cnn.Open()
cmd = New System.Data.OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "DROP VIEW vTest"
cmd.ExecuteNonQuery()
cmd.CommandText = _
"CREATE VIEW vTest AS SELECT * FROM Employees"
cmd.ExecuteNonQuery()
Console.ReadLine()
Catch ex As Exception
Console.WriteLine(ex.Message)
Console.ReadLine()
Finally
If Not cnn Is Nothing Then
If cnn.State <> ConnectionState.Closed Then
cnn.Close()
End If
End If
End Try
End Sub

End Module

 

[Guest]

Excellent! That did it! I ended up with a significant discovery - If you
have joins in your query Access generates an error telling you that it can
only create simple Views. I then tried creating a query to sub the place of
my original that selected all the data from the original query. This allowed
me to run the following:

Create <new query> View As Select * from <original query name>

In my other queries, I select off the new query rather than the original and
all is now functional - woo hoo! Thank you - you get 10 for 10! Robert

 

[Brendan Reynolds]

Joins aren't the problem, Robert. There are other restrictions, though. You
don't seem to be able to use an ORDER BY clause in a view. I tried using
SELECT TOP 100 PERCENT, which I understand is what you have to do to use
ORDER BY in a SQL Server view, but that doesn't seem to work in JET. You can
still use an ORDER BY clause, though, you just have to create a procedure
rather than a view ...

Module Module1

Sub Main()

Dim strCnn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\usenet\db1.mdb;" & _
"Persist Security Info=False;" & _
"Jet OLEDBatabase Password=password"
Dim cnn As New System.Data.OleDb.OleDbConnection(strCnn)
Dim cmd As System.Data.OleDb.OleDbCommand
Try
cnn.Open()
cmd = New System.Data.OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "DROP VIEW vTest"
cmd.ExecuteNonQuery()
cmd.CommandText = "DROP PROCEDURE pTest"
cmd.ExecuteNonQuery()
cmd.CommandText = _
"CREATE VIEW vTest AS SELECT " & _
"Customers.CustomerID, " & _
"Orders.OrderID FROM Customers INNER JOIN Orders ON " & _
"Customers.CustomerID = Orders.CustomerID"
cmd.ExecuteNonQuery()
Console.WriteLine("View created")
Console.ReadLine()
cmd.CommandText = _
"CREATE PROCEDURE pTest AS SELECT " & _
"Customers.CustomerID, " & _
"Orders.OrderID FROM Customers INNER JOIN Orders ON " & _
"Customers.CustomerID = Orders.CustomerID " & _
"ORDER BY Customers.CustomerID DESC"
cmd.ExecuteNonQuery()
Console.WriteLine("Procedure created")
Console.ReadLine()
Catch ex As Exception
Console.WriteLine(ex.Message)
Console.ReadLine()
Finally
If Not cnn Is Nothing Then
If cnn.State <> ConnectionState.Closed Then
cnn.Close()
End If
End If
End Try
End Sub

End Module

 

[Guest]

Brendan, That was one of the most eloquent answers I have had in a long time!
Thank you! While I am aware that I could create a stored proc I did not
choose that way. In Access a stored proc is not easily implemented/used by
Crystal Reports. I wanted to keep my report, my code, and my database simple
and I tried the following instead:

1.) My original query would not recreate exactly as it was (it did have an
Order By clause by the way). It contained multiple joins, some inner some
right.

2.) I knew that something specific about my query may be the cause besides
the joins, but I wanted to back off from testing any tweaks to the query - I
decided to blame it on the joins ... excuses are more fun than the truth
sometimes ...

3.) I decided to "simplify" the query as the error message suggested.
Instead, I inserted a new query that would all the fields from original and
add my filter to it instead.

4.) This worked well and I implemented the idea farther up the tree of
queries that my main query called. I didn't need to change anything besides
add two new queries. My report runs and my filters are working as desired.

Now I need to figure out how to create an installation app that includes all
my files and the crystal report engine as well ... that's for another forum
....

Thank you Brendan, I would love to work along side someone like you someday!
Good luck to you!

 

[Brendan Reynolds]

You're very welcome, Robert, and I'm glad it's working for you. I was just
trying to be accurate - you never know how many other people with similar
problems may be following a thread, or may read it later in the archives.