Access Query from VB.NET

[mscertified]

Has anyone successfully used an Access quety from VB.NET? I am trying to do
this and am getting a weird error. VB.NET calles queries 'stored procedures'.
The error I am getting says "Schema could not be retrieved for this stored
procedure' ... "The underlying enumerator did not support enumerating objects
of type 'ProcedureParamater'.

 

[Maurice]

You have to set the ADO.net reference in vb.net. You can then call the
parameters collection via command object. If you call your query via Code you
can use the regular select statements you use in Access because that's a form
of SQL. Stored Procedures are TSQL-statements you can use in vb.net. Regular
queries (select statements) are often referred to as Views.
So don't be scared, using the right libraries you can get your data from an
Access backend as well.

hth

 

[mscertified]

Thanks for the response.
I'm not actually doing the .Net part, that is another guy but I've been
looking over his shoulder. He is binding a data grid to the data and is using
a 'wizard' (for want of a better term) in Visual Studio. He can specify
either 'tables and views' (which drop-down does NOT list the query) or
'custom stored procedures' which does list the query. As soon as he selects
the query from the drop-down, he gets the error I mentioned. When we cut and
paste the entire text of the query into .NET to run, it runs fine, but I'd
prefer him to be executing the query.
Here is the query:
SELECT FirstName, LastName, Address1, Address2, City, State, ZipCode,
RecordType, HowMany, RecordDate FROM tblAddressOnline
UNION SELECT FirstName, LastName, Address1, Address2, City, State, ZipCode,
RecordType, HowMany, RecordDate FROM tblAddress
ORDER BY LastName, FirstName;

-Dorian

 

[RoyVidar]

Thanks for the response.
I'm not actually doing the .Net part, that is another guy but I've
been looking over his shoulder. He is binding a data grid to the
data and is using a 'wizard' (for want of a better term) in Visual
Studio. He can specify either 'tables and views' (which drop-down
does NOT list the query) or 'custom stored procedures' which does
list the query. As soon as he selects the query from the drop-down,
he gets the error I mentioned. When we cut and paste the entire text
of the query into .NET to run, it runs fine, but I'd prefer him to
be executing the query. Here is the query:
SELECT FirstName, LastName, Address1, Address2, City, State, ZipCode,
RecordType, HowMany, RecordDate FROM tblAddressOnline
UNION SELECT FirstName, LastName, Address1, Address2, City, State,
ZipCode, RecordType, HowMany, RecordDate FROM tblAddress
ORDER BY LastName, FirstName;

-Dorian

This should "successfully" return records from a stored Jet query
(Stored Procedure) from VB, and display the result in a message box.

Sub MyButtonClick(ByVal sender As Object, ByVal e As EventArgs)

Dim con As OleDbConnection
Dim cmd As OleDbCommand = New OleDbCommand()
Dim dr As OleDbDataReader

Dim sConStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyPath\Northwind.mdb"

con = New OleDbConnection(sConStr)

cmd.Connection = con
con.Open()

cmd.CommandText = "[Ten Most Expensive Products]"
cmd.CommandType = CommandType.StoredProcedure

dr = cmd.ExecuteReader()
Dim strString As New StringBuilder
Do While dr.Read
strString.Append(dr.GetValue(0).ToString()).Append(" - ")
strString.Append(dr.GetValue(1).ToString())
strString.Append(environment.NewLine)
Loop
dr.Close()
con.Close()

Messagebox.Show(strString.ToString())

End Sub

You'll need System.Text, System.Data and System.Data.OleDb

There are some articles around, with info on stuff like this. Here's
one working with "Jet Stored Procedures"
http://www.devcity.net/Articles/18/msaccess_sp.aspx
http://www.devcity.net/Articles/34/msaccess_sp2.aspx

Note that when using this approach (DDL), the queries won't be neither
visible nor available in the Access interface in the 2000 version,
only through code/OLE DB provider.

I think this error is more likely to stem from the programming
environment than Jet, so I think it is more likely you'll get the
answers you need in an NG dedicated to the technology you're using.

BTW - a bit semantic, but this isn't really an Access query, but a
Jet query. When connecting to a mdb file from VB, C#... you aren't
using Access at all, but connection to a Jet database trough the
OLE DB provider.