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

[eagleofjade]

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.Worksp aces(0).OpenDat abase(PATH TO DATABASE)
Set qd = dbDatabase.Quer yDefs![QUERY NAME]
qd![PARAMETER NAME] = PARAMETER TO FEED QUERY
Set rs = qd.OpenRecordse t()

... 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

[Cor Ligthert [MVP]]

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

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


"eagleofjad e" <cwilliamson@yo uthhomesinc.org > schreef in bericht
news:1143062017 .185351.300390@ g10g2000cwb.goo glegroups.com.. .[color=blue]
>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.Worksp aces(0).OpenDat abase(PATH TO DATABASE)
> Set qd = dbDatabase.Quer yDefs![QUERY NAME]
> qd![PARAMETER NAME] = PARAMETER TO FEED QUERY
> Set rs = qd.OpenRecordse t()
>
> ... 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
>[/color]

[Cor Ligthert [MVP]]

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

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 @planet.nl> schreef in bericht
news:ONodIIlTGH A.5884@TK2MSFTN GP14.phx.gbl...[color=blue]
> 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?...d-203ca99d2825
>
> To see how easy it can be done in VB2005
>
> http://www.vb-tips.com/default.aspx?...2-b1ed16424252
>
> I hope this helps,
>
> Cor
>
>
> "eagleofjad e" <cwilliamson@yo uthhomesinc.org > schreef in bericht
> news:1143062017 .185351.300390@ g10g2000cwb.goo glegroups.com.. .[color=green]
>>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.Worksp aces(0).OpenDat abase(PATH TO DATABASE)
>> Set qd = dbDatabase.Quer yDefs![QUERY NAME]
>> qd![PARAMETER NAME] = PARAMETER TO FEED QUERY
>> Set rs = qd.OpenRecordse t()
>>
>> ... 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
>>[/color]
>
>[/color]

[eagleofjade]

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:[color=blue]
> Craig,
>
> Sorry the first was SQL here one for OLEDB Access (is the same for 32 bits)
>
> http://www.vb-tips.com/default.aspx?...d-58e5850daf5e
>
> Cor
>
> "Cor Ligthert [MVP]" <notmyfirstname @planet.nl> schreef in bericht
> news:ONodIIlTGH A.5884@TK2MSFTN GP14.phx.gbl...[color=green]
> > 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?...d-203ca99d2825
> >
> > To see how easy it can be done in VB2005
> >
> > http://www.vb-tips.com/default.aspx?...2-b1ed16424252
> >
> > I hope this helps,
> >
> > Cor
> >
> >
> > "eagleofjad e" <cwilliamson@yo uthhomesinc.org > schreef in bericht
> > news:1143062017 .185351.300390@ g10g2000cwb.goo glegroups.com.. .[color=darkred]
> >>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.Worksp aces(0).OpenDat abase(PATH TO DATABASE)
> >> Set qd = dbDatabase.Quer yDefs![QUERY NAME]
> >> qd![PARAMETER NAME] = PARAMETER TO FEED QUERY
> >> Set rs = qd.OpenRecordse t()
> >>
> >> ... 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
> >>[/color]
> >
> >[/color][/color]