Hello, I am making a form that builds a SQL statement in a text box called txtSQL, then I want run it with the code below under a button, but I keep getting a error saying RunSQL requires a SQL Statement, I have copied and pasted my SQL statement in to the query builder and it works fine.
I suspect its the " & Me.txtSQL.Value & " and I have tried forms!.... this did not work either. Any insight would be appreciated, Tim
What do you want to do with the resulting SQL? Display the results to the user? If so, you would need to save the query first. See CreateQueryDef or is it SaveQueryDef... not sure, I hardly ever do it, but I think this is the answer to your issue.
Hey StarTrekker, I have a mind numming amount of data to scrub gathered from a acrobat program. I found a bit of code that alowed you to build a sql statement and displayed the results in a textbox, this piqued my imagination, I thought to create a sql statement that would
1. Change some field names from a chosen table(one of twelve)then
2. SELECT these fields then
3. DELETE record's were a particular field entry's are null
4. UNION this with a table for the clean data
After hours into this I find out that Access dose not support RENAMING a field or SELECT statments using DocmdRun.SQL
But this following work even better then UNION(a silver lining)
INSERT INTO mytable(field1, field2, field3) SELECT field1, field2, field3 FROM othertable
and this worked as a subsuitute for RENAME
Sub RenameField(strTableName As String, _
strFieldFrom As String, _
strFieldTo As String) _
Dim dbs As DAO.Database
Dim tDef As DAO.TableDef
Dim fDef As DAO.Field
Set dbs = CurrentDb()
Set tDef = dbs.TableDefs(strTableName)
Set fDef = tDef.Fields(strFieldFrom)
fDef.Name = strFieldTo
Set fDef = Nothing
Set tDef = Nothing
Set dbs = Nothing
'Then just call it like: RenameField "tblRenCol","Jeff","Jeffrey"
So it all worked out and my form works!
I,m know going to google
CreateQueryDef or is it SaveQueryDef
and see what comes up.
Thank's for your reply, I always appriciate the responce's I get. Tim