How to run an access query from visual basic

How do I run an Access Query from Visual Basic?
Thanks in advance,

Best Regards,

Ronald Kersbergen

I assume you mean "How do I issue an SQL request against an Access database"

For DAO/JET, if you've opened the database with

DIM dB as DataBase
Set dB = DBEngine.Workspaces(0).OpenDatabase("Leader.MDB")

Or for ADO, if you've opened the database with

DIM dB As New adodb.Connection
dB.Open "DSN=LEADER"

You can issue SQL statements with the .Execute method

dB.Execute "DELETE FROM TheTable WHERE State = 'MA'"



Helmar B. Herman, VP ProtoProducts

Thanks for your reply!

What I was really looking for, was a way to run an access
macro or query from visual basic. I've got an access database with a lot of queries which i don't want to make
again ;-). That's why I want to run them from visual basic.

Another, maybe an easier question:

How can i import data from excel into an access database through visual basic?

Thanks in advance,

Best Regards,

Ronald Kersbergen.

Using DAO you have a collection of objects called QueryDefs. A QueryDef object has a SQL property, so you can get your SQL strings out of the these objects and then do what you want with them.. try this:

JonL

Dim myDatabase As Database
Dim myQueryDef As QueryDef
Dim mySQLString As String

Set myDatabase = OpenDatabase(....)
For Each myQueryDef In myDatabase.QueryDefs
mySQLString = myQueryDef.SQL
' *** now do whatever with the SQL string
Next myQueryDef
mDatabase.Close

If you are using DAO, you can open a recordset based on an existing query. (you need to set a reference to DAO 3.51 for Access 97 or DAO 3.6 for Access 2000)

E.G. Say your query is called qselData then you could access your query like this:<CODE><PRE>Private Sub GetData()
Dim dbRef As DAO.Database
Dim recData As DAO.Recordset

Set dbRef = DAO.OpenDatabase("D:VBData.mdb")

<font color=blue>With dbRef.QueryDefs("qselData")
Set recData = .OpenRecordset(dbOpenDynaset)
End With</font color=blue>


Do Until recData.EOF
' Do whatever you need

recData.MoveNext
Loop

End Sub</CODE></PRE>

An alternative method is:<CODE><PRE>Private Sub GetData()
Dim dbRef As DAO.Database
Dim recData As DAO.Recordset

Set dbRef = DAO.OpenDatabase("D:VBData.mdb")
<font color=red>Set recData = dbRef.OpenRecordset("qselData", dbOpenDynaset)</font color=red>


Do Until recData.EOF
' Do whatever you need

recData.MoveNext
Loop

End Sub</CODE></PRE>


For larger database's, SQL is the go. But if you have already done all the work and you are only looking at using access for now, use your existing queries.


HTH