Making a SQL command line 1

[CapnOats]

Hopefully someone can help me out with this one...

All im trying to do is use the value of a textbox as an SQL query with the DoCmd.RunSQL but every time I try and get it to work, it throws up and error of "A RunSQL action requires an argument consisting of an SQL statement."

The value of the textbox is SELECT * FROM Work;
so it should return something.

Ive tried various properties of the textbox as well eg .Value and .Text but to no avail.

Can someone shed some light please...


Regards,
Mike

 

[Katy44]

Try replacing the * with a field name - does that work?

 

[CapnOats]

Nope.

It still returns the same massage whether i use "*", a real field name or one that doesnt exist - the original string is a direct copy and paste from a dummy query i made to test to make sure it worked beofre i started coding - and it works fine in the SQL window.

I also tried to use

"" & textBox

to try to trick it into accepting the string but nope again.

Regards,
Mike

 

[jksmi]

Hi, RunSQL must use an action query (update or insert) not a Select query... HTH, Jamie

 

[CapnOats]

So is there any way to do a dynamic select - or basically accept and run ANY valid SQL? Its supposed to act as a control panel so I can go in and manually query and change records in the database so that I can fix any problems that arise later when its running.

Is there any way to do this or should i just resort to the main database window and fiddle the tables manually - its not ideal, butit is still poss.



Regards,
Mike

 

[CapnOats]

i also tried

Dim Db As DAO.Database
Dim strSQL As String

Set Db = CurrentDb
Db.Execute inputText

but it says "Cannot execute a select query"

Regards,
Mike

 

[randy700]

Why not just create the query, open the query with your command button, and make necessary changes there? You could also base a form on the query and then make the changes on that form.

Randy

 

[jksmi]

Hi, create a view, and use docmd.OpenQuery "VW_WORK" to open it - if you need to change the SQL use a function like...

Public Function ViewOnTheFly(ByRef strSQL As String)
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim strViewName As String

cat.ActiveConnection = CurrentProject.Connection
strViewName = "VW_WORK"

Set cmd = cat.Views(strViewName).Command
cmd.CommandText = strSQL
Set cat.Views(strViewName).Command = cmd

DoCmd.OpenQuery strViewName
End Function

You'll need to add a reference to Microsoft ADO x.x for DLL & Security. Only trouble with this is if multiple users are trying to do this at the same time...

HTH, Jamie