Select SQL in VBA - Possible

[Guest]

I am using the following code to run a Select query on the Click of a Command
Button

Private Sub CreateMovementRecords_Click()

DoCmd.RunSQL "SELECT [Aus Life Lookup].[Client Code] FROM [Aus Life Lookup];"

End Sub

But it does not recognise the SQL statement and returns error message when
running of Error 2342 and 'A RunSQL action requires an argument consisting
of a SQL statement'

This SQL is just copied from the SQL view of the query that works fine when
executed outside the code.

Any ideas ?

 

[tina]

RunSQL is used to run Action queries, not Select queries. what are you
trying to accomplish with the Select query?

hth

 

[Guest]

Hi

Thanks for the fast response

I am trying to populate a temp set of data (ie a number of fields) that will
be used in a susequent SQL query.

I have now tried using the OpenQuery command which does what I was
originally hoping for from the RunSQL command.

Does this OpenQuery approach have limitations if not then I do not see what
the diff is bewteen the OpenQuery and the RunSQL approach and so why RunSQL
does not allow Select (ie non action queries)?

Any ideas as it would help me understand more about how to code the rest of
my project.

Many thanks

Andy

 

[John W. Vinson]

I am trying to populate a temp set of data (ie a number of fields) that will
be used in a susequent SQL query.

What will you DO with that query?

If you're planning to open it as a query datasheet... you may want to
reconsider.

To display the results of a query onscreen, you can set a Form's Recordsource
property to the SQL string.

To print the results, set the Recordsource of a Report to the SQL string.

There are very few circumstances where it would be appropriate to open a Query
directly.

John W. Vinson [MVP]

 

[Baz]

OpenQuery opens a *saved* query object in exactly the same way as you might
open it manually from the database window.

RunSQL executes an ad-hoc SQL string (*not* a saved query) and has no
capability to handle results, hence it is only suitable for action queries
e.g. INSERT.

As John said, it is rarely appropriate to open a query directly rather than
using a form.

Hi

Thanks for the fast response

I am trying to populate a temp set of data (ie a number of fields) that will
be used in a susequent SQL query.

I have now tried using the OpenQuery command which does what I was
originally hoping for from the RunSQL command.

Does this OpenQuery approach have limitations if not then I do not see what
the diff is bewteen the OpenQuery and the RunSQL approach and so why RunSQL
does not allow Select (ie non action queries)?

Any ideas as it would help me understand more about how to code the rest of
my project.

Many thanks

Andy

I am using the following code to run a Select query on the Click of a Command
Button

Private Sub CreateMovementRecords_Click()

DoCmd.RunSQL "SELECT [Aus Life Lookup].[Client Code] FROM [Aus Life Lookup];"

End Sub

But it does not recognise the SQL statement and returns error message when
running of Error 2342 and 'A RunSQL action requires an argument consisting
of a SQL statement'

This SQL is just copied from the SQL view of the query that works fine when
executed outside the code.

Any ideas ?