473,320 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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

Mar 22 '06 #1
3 11526
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
"eagleofjade" <cw*********@youthhomesinc.org> schreef in bericht
news:11**********************@g10g2000cwb.googlegr oups.com...
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

Mar 23 '06 #2
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]" <no************@planet.nl> schreef in bericht
news:ON**************@TK2MSFTNGP14.phx.gbl...
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
"eagleofjade" <cw*********@youthhomesinc.org> schreef in bericht
news:11**********************@g10g2000cwb.googlegr oups.com...
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


Mar 23 '06 #3
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?...d-58e5850daf5e

Cor

"Cor Ligthert [MVP]" <no************@planet.nl> schreef in bericht
news:ON**************@TK2MSFTNGP14.phx.gbl...
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
"eagleofjade" <cw*********@youthhomesinc.org> schreef in bericht
news:11**********************@g10g2000cwb.googlegr oups.com...
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



Mar 23 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ryan Hubbard | last post by:
I would like to run a query from VBA. The query will be one with parameters. Is there a way to run the query and have Access prompt for the values like it does if I where to execute it through...
1
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an application under access 2003 but will target access...
19
by: bdt513 | last post by:
I am trying to extract the values from a query using VBA. Specifically, I want to concatenate all the values of the "rosEmail" field from query "qselRosterEmailList" into one string (strEmails). I...
1
by: Don Sealer | last post by:
I have a report that includes 5 different subreports. I'd like to be able to open this report using a date function (Start Date and End Date). I'd like all five subreports to show the data from...
3
by: Andy Davis | last post by:
I have set up a mail merge document in Word 2003 which gets its data from my Access 2000 database. I want to set up a button on a form that: 1. runs the query to provide the dat for the merge...
3
by: sara | last post by:
I've been reading all the posts on this topic. Most are years old, so I have 2 questions: 1. Is there any improvement on opening the same report multiple times (with different input parameters...
6
by: lesperancer | last post by:
SELECT distinct b.t_orno, b.t_pono FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS tblFilterDate_1 WHERE (((b.t_yearMonth) Between . And .)); tblMonthlyBooking is a sql server...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
16
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.