Re: Opening a query in MS Access with parameters using VB.net

Hi Cor,

Thank you for the input. The code in this link appears to set the
datasource to a SQL statement.

I know that I could have him build up a SQL string with a "WHERE"
statement in it, but it would be nice if he could just specify the
existing query that's already in the Access database, and pass the
parameters to it, using the stored query as the datasource. If that's
doable, what would be the code set the data source to the query and
pass a parameter to it, or is there no way to do that other than using
a SQL statement?

Partly the reason I want to show him this way, is that to get the
aggregate data he needs, there has to be two queries, with the second
one using the first query as its datasource. The first query is the one
that has the parameters. With DAO, all I have to do is declare the
second query as the recordsource, and pass it the parameters to get the
records I need.

Thanks,
Craig

Cor Ligthert [MVP] wrote:
Craig,

Sorry the first was SQL here one for OLEDB Access (is the same for 32 bits)

http://www.vb-tips.com/default.aspx?ID=c8303e25-496d-4288-bc1d-58e5850daf5e

Cor

"Cor Ligthert [MVP]" <notmyfirstname@xxxxxxxxx> schreef in bericht
news:ONodIIlTGHA.5884@xxxxxxxxxxxxxxxxxxxxxxx
Craig,

Reading is a lot easier, than in DAO and ADO. Updating is more difficult
because of the disconnected way. However because of databinding is the
total needed code much less than with DAO.

Here a sample in real code (it is so simple that we have not a sample with
only what you ask. What you ask is until the Fill).

http://www.vb-tips.com/default.aspx?ID=bece831d-6742-4364-bd0d-203ca99d2825

To see how easy it can be done in VB2005

http://www.vb-tips.com/default.aspx?ID=1139f14a-c236-4ad7-8882-b1ed16424252

I hope this helps,

Cor


"eagleofjade" <cwilliamson@xxxxxxxxxxxxxxxxx> schreef in bericht
news:1143062017.185351.300390@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am trying to help a friend who is learning VB.net in school. I have
done VB programming for a number of years using VB 6. He needs to open
a query in an Access database that has parameters so he can work with
the returned records.

In VB 6, I use DAO and do it this way:

Dim db As Database
Dim rs As Recordset
Dim qd As QueryDef
Dim strPath as string


Set db = DBEngine.Workspaces(0).OpenDatabase(PATH TO DATABASE)
Set qd = dbDatabase.QueryDefs![QUERY NAME]
qd![PARAMETER NAME] = PARAMETER TO FEED QUERY
Set rs = qd.OpenRecordset()

... CODE TO USE THE RECORDSET

rs.close
db.close

For VB.net, it appears that you have to use OLE DB and it really seems
to be way more complicated than it should be. Does anyone have a code
snippet where the code simply sets the recordsource to the query and
passes the query the parameters so he can work with the recordset?

Thanks,
Craig




.

Relevant Pages

  • Re: Viewing the contents of two tables using ASP and MS Access
    ... Number of fields in a recordset 255 ... Number of characters in a cell in the query design grid 1,024 ... Number of characters in an SQL statement approximately 64,000 ...
    (microsoft.public.inetserver.asp.db)
  • Re: Bounded Columns vs Recordset
    ... This involves code to get and then create the Row Source List for the ... Recordset will be based on a query, so why not just use the query ... Combobox instead of having a SQL statement in the RowSource Property. ...
    (microsoft.public.access.externaldata)
  • Re: Do While Loop code
    ... in SQL statement! ... I would just create a stored query from that SQL. ... Then you can either open a recordset on that query and retrieve the values ... outstanding transactions there are. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Do While not Statement
    ... How about just doing a count query or queries? ... intBasePaper = odoCon.ExecuteFROM tblUsers WHERE ... > the same recordset. ... > 'Initialise the strSQL variable with an SQL statement to query the ...
    (microsoft.public.inetserver.asp.general)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... Tools - references - tick Microsoft Outlook object library. ... in the criteria cell in the graphical query builder. ... the same filtered recordset every time. ... normal form to allow the normal filtering process and viewing of the ...
    (microsoft.public.access.formscoding)
Loading