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

 

[Cor Ligthert [MVP]]

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

 

[Cor Ligthert [MVP]]

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

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

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

 

[eagleofjade]

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

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

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

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