running a query in access through code

[CoffeeMug]

Hi all,

I'm trying to execute a query in VB.Net that is stored in Access.

I already have a connection to the database ("C:\Tmp\Testdatabase.mdb") and now I'm looking to execute the query ("qryTest") that creates a table in Access. Can anyone give me the code that will do such a thing?

Thanks in advance,
c(_)

[Ken Tucker [MVP]]

Hi,

Dim conn As OleDbConnection

Dim strConn As String

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn &= "Data Source = C:\Tmp\Testdatabase.mdb;"

conn = New OleDbConnection(strConn)

Dim cmd As New OleDbCommand("qryTest", conn)

conn.Open()

cmd.ExecuteNonQuery()

conn.Close()

Ken

---------------------------

"CoffeeMug" <an*******@discussions.microsoft.com> wrote in message
news:EC**********************************@microsof t.com...

Hi all,

I'm trying to execute a query in VB.Net that is stored in Access.

I already have a connection to the database ("C:\Tmp\Testdatabase.mdb")
and now I'm looking to execute the query ("qryTest") that creates a table
in Access. Can anyone give me the code that will do such a thing?

Thanks in advance,
c(_)

[CoffeeMug]

Thanks for the quick response

c(_)

[CoffeeMug]

Hi Ken

Thanks again for the reply..
I'm getting the following error

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE
This error occurs when VB tries to execute the code cmd.ExecuteNonQuer

The database is not in use, and the query is pretty straightforward
It copies data from a linked (through ODBC) table (pwd and UID are stored) and puts it in a new table..

Can you help me out please

Thanks in advance
Vincent c(_)

[Paul Clement]

On Wed, 21 Jan 2004 05:46:08 -0800, CoffeeMug <an*******@discussions.microsoft.com> wrote:

¤ Hi Ken,
¤
¤ Thanks again for the reply...
¤ I'm getting the following error:
¤
¤ Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'
¤ This error occurs when VB tries to execute the code cmd.ExecuteNonQuery
¤
¤ The database is not in use, and the query is pretty straightforward:
¤ It copies data from a linked (through ODBC) table (pwd and UID are stored) and puts it in a new table...
¤
¤ Can you help me out please?

You may want to post your code and the contents of your Access QueryDef.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Ken Tucker [MVP]]

Hi,

Sorry forgot this line add it before you use the the oledbcommand.
cmd.CommandType = CommandType.StoredProcedure

Ken

--------------------------

"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:OC*************@tk2msftngp13.phx.gbl...

Hi,

Dim conn As OleDbConnection

Dim strConn As String

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn &= "Data Source = C:\Tmp\Testdatabase.mdb;"

conn = New OleDbConnection(strConn)

Dim cmd As New OleDbCommand("qryTest", conn)

conn.Open()

cmd.ExecuteNonQuery()

conn.Close()

Ken

---------------------------

"CoffeeMug" <an*******@discussions.microsoft.com> wrote in message
news:EC**********************************@microsof t.com...

Hi all,

I'm trying to execute a query in VB.Net that is stored in Access.

I already have a connection to the database ("C:\Tmp\Testdatabase.mdb")
and now I'm looking to execute the query ("qryTest") that creates a table
in Access. Can anyone give me the code that will do such a thing?

Thanks in advance,
c(_)