SQL Query

[Guest]

Hi,

I relatively new to Access. I want to have a query excecuted by some SQL
code. Below is the codse I have written:

Option Compare Database


Private Sub Keycode_Change()
Dim sSql As String
sSql = "SELECT Table1.Dept, Table1.Keycode, Table1.Value FROM Table1
WHERE (((Table1.Dept)=2));"

DoCmd.RunSQL sSql
End Sub


Table 1 consists of:

Dept Keycode Value
1 123 1
1 321 1
1 444 55
1 555 99
2 341 2
2 454 3

When I run it I get the following error:

'A RunSQL action requires an argument consisting of an SQL statement'

 

[fredg]

Hi,

I relatively new to Access. I want to have a query excecuted by some SQL
code. Below is the codse I have written:

Option Compare Database

Private Sub Keycode_Change()
Dim sSql As String
sSql = "SELECT Table1.Dept, Table1.Keycode, Table1.Value FROM Table1
WHERE (((Table1.Dept)=2));"

DoCmd.RunSQL sSql
End Sub

Table 1 consists of:

Dept Keycode Value
1 123 1
1 321 1
1 444 55
1 555 99
2 341 2
2 454 3

When I run it I get the following error:

'A RunSQL action requires an argument consisting of an SQL statement'

That error is correct. From VBA help:

"SQLStatement Required Variant. A string expression that's a valid
SQL statement for an action query or a data-definition query"

The RunSQL method will only run an action (Delete, Update, Append,
etc.) query.

If you want a Select query, create one. Save it. Run that query using
the OpenQuery method.

 

[OfficeDev18 via AccessMonster.com]

Sorry, but .OpenQuery is identical to .RunSQL. They will both only execute
action queries.

To run a SELECT query and be able to read the data, DIM a recordset object,
say:

Dim rst as Recordset

Set rst = CurrentDb.OpenRecordset("SELECT Table1.Dept, Table1.Keycode, Table1.
Value FROM Table1
WHERE (((Table1.Dept)=2));",dbOpenDynaset)

To access records, use rst.FindFirst, or rst.MoveFirst, etc. See the
OpenRecordset, MoveFirst, FindFirst etc. methods in the Help for lots of
detail.

To access data within the record, you use rst!Dept

HTH

Hi,

[quoted text clipped - 24 lines]

'A RunSQL action requires an argument consisting of an SQL statement'

That error is correct. From VBA help:

"SQLStatement Required Variant. A string expression that's a valid
SQL statement for an action query or a data-definition query"

The RunSQL method will only run an action (Delete, Update, Append,
etc.) query.

If you want a Select query, create one. Save it. Run that query using
the OpenQuery method.

 

[John Spencer]

Sorry, I disagree.

Docmd.OpenQuery "Query1"

does open Query1 which is a select query in my test database.

Sorry, but .OpenQuery is identical to .RunSQL. They will both only execute
action queries.

To run a SELECT query and be able to read the data, DIM a recordset
object,
say:

Dim rst as Recordset

Set rst = CurrentDb.OpenRecordset("SELECT Table1.Dept, Table1.Keycode,
Table1.
Value FROM Table1
WHERE (((Table1.Dept)=2));",dbOpenDynaset)

To access records, use rst.FindFirst, or rst.MoveFirst, etc. See the
OpenRecordset, MoveFirst, FindFirst etc. methods in the Help for lots of
detail.

To access data within the record, you use rst!Dept

HTH

Hi,

[quoted text clipped - 24 lines]

'A RunSQL action requires an argument consisting of an SQL statement'

That error is correct. From VBA help:

"SQLStatement Required Variant. A string expression that's a valid
SQL statement for an action query or a data-definition query"

The RunSQL method will only run an action (Delete, Update, Append,
etc.) query.

If you want a Select query, create one. Save it. Run that query using
the OpenQuery method.

 

[OfficeDev18 via AccessMonster.com]

I should have qualified my statement. Yes, it'll execute, but you can't then
access individual records/data from VBA for analysis.

The way I suggested, individual records and data are available in VBA as I
gave Andrew the methods to use.

Sorry, I disagree.

Docmd.OpenQuery "Query1"

does open Query1 which is a select query in my test database.

Sorry, but .OpenQuery is identical to .RunSQL. They will both only execute
action queries.

[quoted text clipped - 34 lines]