Run SQL from a VB code

[giannis]

How can run a SQL clause from a VB code?

 

[Fredg]

Is there something wrong with the answer given by Arvin Meyer in your
previous thread?

 

[giannis]

No, this is a different question !!!
I want to learn with what way can i
run a SQL clause from Access VB.
What command or function or procedure
can i use for this purpose ?

 

[Dirk Goldgar]

No, this is a different question !!!
I want to learn with what way can i
run a SQL clause from Access VB.
What command or function or procedure
can i use for this purpose ?

That depends on what kind of SQL statement (not "clause") it is. If
it's an action query, you might use either DoCmd.RunSQL or
CurrentDb.Execute to execute the query. If it's a select query, you can
open a recordset on it -- for processing records in code -- using
CurrentDb.OpenRecordset, or you can create a temporary QueryDef object
and open a recordset from that. If you want to open a SELECT statement
as a datasheet, though, you'll have to create a permanent, named
QueryDef (a stored query), then use DoCmd.OpenQuery specifying the name
of that QueryDef. You can, of course, delete the QueryDef object when
you're done with it.

 

[Arvin Meyer]

There's much more to it than that. In your first message, you wanted to use
the SQL as the recordsource of a form. After I straightened out your SQL
statement it would run just fine. But there are many other way to run SQL in
VB. All aircode follows:

For an action query you might do something like:

Dim strSQL As String
strSQL = "Delete * From MyTable Where ID =" & Me.txtID
CurrentDB.Execute strSQL

You can also use DoCmd.RunSQL:
DoCmd SetWarning False
DoCmd.RunSQL strSQL
DoCmd SetWarning True

Or you might build a recordset:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String

strSQL = "Select * From MyTable"
Set db = CurrentDB
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

There are multiple ways to do use SQL. For more information, you might get a
copy of John L. Viescas book "Running Access <version>" which has
significant amounts of SQL.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Drew]

Great posting!!

Drew

-----Original Message-----
There's much more to it than that. In your first message, you wanted to use
the SQL as the recordsource of a form. After I straightened out your SQL
statement it would run just fine. But there are many other way to run SQL in
VB. All aircode follows:

For an action query you might do something like:

Dim strSQL As String
strSQL = "Delete * From MyTable Where ID =" & Me.txtID
CurrentDB.Execute strSQL

You can also use DoCmd.RunSQL:
DoCmd SetWarning False
DoCmd.RunSQL strSQL
DoCmd SetWarning True

Or you might build a recordset:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String

strSQL = "Select * From MyTable"
Set db = CurrentDB
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

There are multiple ways to do use SQL. For more information, you might get a
copy of John L. Viescas book "Running Access <version>" which has
significant amounts of SQL.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.514 / Virus Database: 312 - Release Date: 8/28/2003


.