Executing Access Query

[Guest]

Hi,

Can anyone point me to an example of how I would execute a query I've created in an Access DB. I've copied the SQL down to my VB.net application and that works fine, but it's ugly. I'd like to direcly execute the query that I created in Access - if possible.

Also, I would like to be able to add a parameter to that query. For example, if I'm in Access and put an unknown field in the query, I get prompted to enter that info when the query runs. I'm hoping that can also be done through VB.net.

Art

 

[Cor Ligthert]

Hi Art,

It can that you mean something completly else with your query, however when
this link is not the answer and you mean something totally else, please do
than reply and tell what you mean with an Access Query, this is not an
access newsgroup you know.

Access Automate
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317113

However, I hope this helps a little bit?

Cor

Can anyone point me to an example of how I would execute a query I've

created in an Access DB. I've copied the SQL down to my VB.net application
and that works fine, but it's ugly. I'd like to direcly execute the query
that I created in Access - if possible.

Also, I would like to be able to add a parameter to that query. For

example, if I'm in Access and put an unknown field in the query, I get
prompted to enter that info when the query runs. I'm hoping that can also
be done through VB.net.

 

[Guest]

Cor,

Thanks for the link. I'm trying to work with an Access Database in VB.net. I've seen how to read through records, submit SQL code, and write information back. I've also seen some examples about how to execute stored procedures in SQL Server. It starts with importing System.Data.SqlClient. I had been assuming that this is specific to SQL Server and would not work with MS Access -- but I don't really know (I confess that I should have tried).

I will read through the material on the link that you sent to me.

Art

 

[Cor Ligthert]

Hallo Art,

You can use those samples and everywhere where you see this

SqlClient.SQL you change it for OleDb.OleDb

the only difference it that you have to make an access connection which is
as easy as

Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" &
_
" Data Source=C:\db1.mdb;User Id=admin;Password=;")

I hope this helps to get a even a quicker start.

Cor

 

[Cor Ligthert]

Hi Art,

I was mixing you up with another message however with your new message I
would take this as reference (that link I had send also some hours ago and
send it as well to that new message).

VB.net Resource kit
http://msdn.microsoft.com/vbasic/vbrkit/default.aspx

And if you have problems installing the resource kit
http://msdn.microsoft.com/vbasic/vbrkit/faq/#installvdir

Another resource for learning is the Quick Starts
http://samples.gotdotnet.com/quickstart/

My message about SQL and Access stays with this the same.


I hope this helps a little bit?

Cor

Cor,

Thanks for the link. I'm trying to work with an Access Database in

VB.net. I've seen how to read through records, submit SQL code, and write
information back. I've also seen some examples about how to execute stored
procedures in SQL Server. It starts with importing System.Data.SqlClient.
I had been assuming that this is specific to SQL Server and would not work
with MS Access -- but I don't really know (I confess that I should have
tried).

 

[Paul Clement]

¤ Hi,
¤
¤ Can anyone point me to an example of how I would execute a query I've created in an Access DB. I've copied the SQL down to my VB.net application and that works fine, but it's ugly. I'd like to direcly execute the query that I created in Access - if possible.
¤
¤ Also, I would like to be able to add a parameter to that query. For example, if I'm in Access and put an unknown field in the query, I get prompted to enter that info when the query runs. I'm hoping that can also be done through VB.net.
¤
¤ Art

Here is an example of how to execute an Access QueryDef and place the contents of the Select query
into a DataSet:

Dim AccessConn As System.Data.OleDb.OleDbConnection

AccessConn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("qryTable1", AccessConn)
AccessCommand.CommandType = CommandType.StoredProcedure
AccessCommand.Parameters.Add("@ParamName", System.Data.OleDb.OleDbType.VarWChar).Value =
"SomeValue"

Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter

With da
.SelectCommand = AccessCommand
End With

Dim ds As New DataSet("QueryTables")
da.Fill(ds, "Table1")


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)