Run Select query from STRING (1 Viewer)

M

memaxt

Registered User.

Local time

Today, 08:33

Joined

Mar 12, 2013

Messages

62

• Nov 6, 2015

• #1

Hi there,

I would like to run a select query from a string. basically I want to store the SQL statements in a texbox and click a button to run the code.

Textbox contents =

SELECT CurrentVersion.AreaTeam, CurrentVersion.Version, CurrentVersion.Selected
FROM CurrentVersion
GROUP BY CurrentVersion.AreaTeam, CurrentVersion.Version, CurrentVersion.Selected
HAVING (((CurrentVersion.Selected)=True));

I want the button to execute the code above to create the select query.

I've tried:

Dim strSQL As String

StrSQL = me.txtString

docmd.RunSQL StrSQL

but I get the following error: A RunSQL action requires an argument consiting of an SQL Statement.

is this possible to do for select queries?

 

P

plog

Banishment Pending

Local time

Today, 10:33

Joined

May 11, 2011

Messages

11,638

• Nov 6, 2015

• #2

DoCmd.RunSQL (https://msdn.microsoft.com/en-us/library/office/ff194626.aspx?f=255&MSPPError=-2147217396) is for action queries (INSERT, UPDATE, DELETE) not SELECT queries.

What do you mean by 'run'? What are you hoping will occur?

You probably want to use DoCmd.OpenQuery (https://msdn.microsoft.com/en-us/library/office/ff192746.aspx)

 

M

memaxt

Registered User.

Local time

Today, 08:33

Joined

Mar 12, 2013

Messages

62

• Nov 6, 2015

• #3

Hi Plog,

Many thanks for you reply,

I do indeed mean run, is it possible to run the query from statement I have stored in my textbox?

 

P

plog

Banishment Pending

Local time

Today, 10:33

Joined

May 11, 2011

Messages

11,638

• Nov 6, 2015

• #4

I didn't ask if you meant run, I asked what do you meant by 'run'. What do you hope will occur on your computer screen with that SQL?

 

M

memaxt

Registered User.

Local time

Today, 08:33

Joined

Mar 12, 2013

Messages

62

• Nov 6, 2015

• #5

Ahh my apologies,

I would like it to execute the select statement stored in the textbox field and display the query results.

 

P

plog

Banishment Pending

Local time

Today, 10:33

Joined

May 11, 2011

Messages

11,638

• Nov 6, 2015

• #6

This is a good example of how to do that:

https://msdn.microsoft.com/en-us/library/office/ff195966.aspx

Basically you use your SQL to create a temporary query object, open that object and then delete it upon its close.

 

M

memaxt

Registered User.

Local time

Today, 08:33

Joined

Mar 12, 2013

Messages

62

• Nov 6, 2015

• #7

Thank you,

the part where it says:

qdfTemp = .CreateQueryDef("", _
"SELECT * FROM Employees")

I changed it to

qdfTemp = me.txtstring

but to no joy

 

P

plog

Banishment Pending

Local time

Today, 10:33

Joined

May 11, 2011

Messages

11,638

• Nov 6, 2015

• #8

Find out what me.txtstring actually contains. Then paste that into an actual query and see if it runs. Sounds like you have bad SQL in that variable.

 

G

Grumm

Registered User.

Local time

Today, 17:33

Joined

Oct 9, 2015

Messages

395

• Nov 6, 2015

• #9

I would have used recordsets since I didn't know that other methode...
Most of the time i just make an sSQL string with the entire correct query
Then use

Code:Copy to clipboard

Set rs_Records = CurrentDb.OpenRecordset(sSQL)

It may be a wrong practice but it works for me.

 

M

memaxt

Registered User.

Local time

Today, 08:33

Joined

Mar 12, 2013

Messages

62

• Nov 6, 2015

• #10

Thank you for your relies!

I ended using the following link:

http://www.utteraccess.com/forum/index.php?showtopic=1992179&view=findpost&p=2276028

There is an example database that shows you where to store your sql statement and run it via vba.

many thanks again for all your help everyone.