An easy way to convert all queries in an access database to SQL statements so as to use the same inside forms and reports as data source .
An easy way to convert all queries in an access database to SQL statements so as to use the same inside forms and reports as data source .
You can just use the saved query as the record source for any form or report?
There is no magic way to convert all of them - you can just cut and paste the SQL view from a query into the row source property.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
You can use my SQL to VBA converter to assist with the process
https://www.access-programmers.co.uk...d.php?t=293372
Use the latest version in post #5
Thank you for your reply.
1.Please let me know whether an sql statement of an append query can be used in a macro ? If so give an example.
2.The vba to be used instead of an append query.
I want to remove queries from my database objects as far as possible so as to secure my application. Thank you in advance.
If you compile you database and save it as accde the end users can't get to anything much anyway.
In VBA you simply use something like
Or to execute an update qryCode:Me.RecordSource = "SELECT Field1 from Table2"
Code:Dim sSql as string sSql = "UPDATE YourTable SET StringField1 = 'Somestring', NumberField2 = 99 " currentdb.execute sSql, dbSeechanges
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Thank you very much.
sql2vba converter is extremely helpful. The converted vba works fine.
Let me know the vba for the following also.
Hourglass Yes/No
Set warning No/Yes
Echo No/Yes
Requery
MsgBox
If you pl inform the vba for the above macro actions also my macro can be converted fully to vba.
Your help should be so much appreciable. THANK YOU.
I don't use macros - the following are for VBAsql2vba converter is extremely helpful. The converted vba works fine.
Let me know the vba for the following also.
Hourglass Yes/No
Set warning No/Yes
Echo No/Yes
Requery
MsgBox
If you pl inform the vba for the above macro actions also my macro can be converted fully to vba.
Your help should be so much appreciable. THANK YOU.
DoCmd.Hourglass True / DoCmd.Hourglass False
DoCmd.SetWarnings True / DoCmd.SetWarnings False
Application.Echo True / Application.Echo False
Me.Requery for whole form / Me.ControlName.Requery for individual control
MsgBox "Some message",vbInformation+vbOKCancel,"Message Title" and various other possible types of message
BTW - the Access Help file has all this info and much more - just click F1 for help
My vba on click event of a button(Add) is as follows. You may please examine and let me know the error in it. After the event execute the application is hanged.(ie. the command buttons are become inactive ) I suppose some declaration is needed at the beginning but I don't know. Please Examine and correct the code. Thank You.
Private Sub Command27_Click()
DoCmd.Hourglass True
DoCmd.SetWarnings False
Application.Echo True
DoCmd.RunSQL "INSERT INTO [tblAppendCL-CN] ( ID, [CL-Case No] ) " & _
" SELECT 2 AS ID, Right([F1],Len([F1])-4) AS [CL-CaseNo] " & _
" FROM [tblCL-CaseNo] " & _
" WHERE ((([tblCL-CaseNo].[F1]) Like ""WPC*""));"
DoCmd.RunSQL "INSERT INTO [tblAppendCL-CCC] ( ID, [CL-Case No] ) " & _
" SELECT 2 AS ID, Right([F1],Len([F1])-10) AS [CL-CaseNo] " & _
" FROM [tblCL-CaseNo] " & _
" WHERE ((([tblCL-CaseNo].[F1]) Like ""Con.CaseC*""));"
DoCmd.RunSQL "INSERT INTO [tblAppendCL-OAEKM] ( ID, [CL-Case No] ) " & _
" SELECT 2 AS ID, Right([F1],Len([F1])-8) AS [CL-CaseNo] " & _
" FROM [tblCL-CaseNo] " & _
" WHERE ((([tblCL-CaseNo].[F1]) Like ""OA*EKM*""));"
DoCmd.RunSQL "INSERT INTO [tblAppendCL-OP] ( ID, [CL-Case No] ) " & _
" SELECT 2 AS ID, Right([F1],Len([F1])-6) AS [CL-CaseNo] " & _
" FROM [tblCL-CaseNo] " & _
" WHERE ((([tblCL-CaseNo].[F1]) Like ""OPKAT*""));"
DoCmd.RunSQL "INSERT INTO [tblAppendCL-WA] ( ID, [CL-Case No] ) " & _
" SELECT 2 AS ID, Right([F1],Len([F1])-3) AS [CL-CaseNo] " & _
" FROM [tblCL-CaseNo] " & _
" WHERE ((([tblCL-CaseNo].[F1]) Like ""WA*""));"
DoCmd.RunSQL "UPDATE [tblCL-CaseNo] SET [tblCL-CaseNo].F1 = ""0"";"
Me.Requery
DoCmd.Hourglass False
DoCmd.SetWarnings True
Application.Echo False
MsgBox "Causelist added", vbDefaultButton2, "CaseInformation System"
End Sub
You are confused with your quotes around the strings in your criteria.
Personally I would create a string variable (sSql) and use CurrentDb.Execute sSql to avoid the need to set warnings on and off.
Try this;
Code:Private Sub Command27_Click() DoCmd.Hourglass True DoCmd.SetWarnings False Application.Echo True DoCmd.RunSQL "INSERT INTO [tblAppendCL-CN] ( ID, [CL-Case No] ) " & _ " SELECT 2 AS ID, Right([F1],Len([F1])-4) AS [CL-CaseNo] " & _ " FROM [tblCL-CaseNo] " & _ " WHERE ((([tblCL-CaseNo].[F1]) Like 'WPC*'));" DoCmd.RunSQL "INSERT INTO [tblAppendCL-CCC] ( ID, [CL-Case No] ) " & _ " SELECT 2 AS ID, Right([F1],Len([F1])-10) AS [CL-CaseNo] " & _ " FROM [tblCL-CaseNo] " & _ " WHERE ((([tblCL-CaseNo].[F1]) Like 'Con.CaseC*'));" DoCmd.RunSQL "INSERT INTO [tblAppendCL-OAEKM] ( ID, [CL-Case No] ) " & _ " SELECT 2 AS ID, Right([F1],Len([F1])-8) AS [CL-CaseNo] " & _ " FROM [tblCL-CaseNo] " & _ " WHERE ((([tblCL-CaseNo].[F1]) Like 'OA*EKM*'));" DoCmd.RunSQL "INSERT INTO [tblAppendCL-OP] ( ID, [CL-Case No] ) " & _ " SELECT 2 AS ID, Right([F1],Len([F1])-6) AS [CL-CaseNo] " & _ " FROM [tblCL-CaseNo] " & _ " WHERE ((([tblCL-CaseNo].[F1]) Like 'OPKAT*'));" DoCmd.RunSQL "INSERT INTO [tblAppendCL-WA] ( ID, [CL-Case No] ) " & _ " SELECT 2 AS ID, Right([F1],Len([F1])-3) AS [CL-CaseNo] " & _ " FROM [tblCL-CaseNo] " & _ " WHERE ((([tblCL-CaseNo].[F1]) Like 'WA*'));" DoCmd.RunSQL "UPDATE [tblCL-CaseNo] SET [tblCL-CaseNo].F1 = '0';" Me.Requery DoCmd.Hourglass False DoCmd.SetWarnings True Application.Echo False MsgBox "Causelist added", vbDefaultButton2, "CaseInformation System" End Sub
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Agree with Minty's response including the use of CurrentDB.Execute instead of DoCmd.RunSQL
Two other things
1. Recommend you add error handling to your code
2. The message box code at the end is wrong. You have not specified a button so you will just get an OK button by default.
Remove the vbDefaultButton2 and just have two commas with nothing between them.
Personally, I never use "SetWarnings " nor "DoCmd.RunSQL" commands.
I use the "CurrentdB.Execute" command.
My understanding:
Using the "RunSQL" command causes Access to parse (not the best word to use) the SQL before it is sent to the dB engine.
The execute method bypasses Access and sends the SQL directly to the dB engine (whichever one you are using)
My code for CurrentDb.Execute sSql creates errors. If you please edit my above code as you instructed and intimated me will be a great help.
If your queries code are correct then simply change it to
The Debug.print will display in the immediate window the actual query being run.Code:Dim sSql As String DoCmd.Hourglass True sSql = "INSERT INTO [tblAppendCL-CN] ( ID, [CL-Case No] ) " & _ " SELECT 2 AS ID, Right([F1],Len([F1])-4) AS [CL-CaseNo] " & _ " FROM [tblCL-CaseNo] " & _ " WHERE ((([tblCL-CaseNo].[F1]) Like 'WPC*'));" Debug.Print sSql CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError sSql = "INSERT INTO [tblAppendCL-CCC] ( ID, [CL-Case No] ) " & _ " SELECT 2 AS ID, Right([F1],Len([F1])-10) AS [CL-CaseNo] " & _ " FROM [tblCL-CaseNo] " & _ " WHERE ((([tblCL-CaseNo].[F1]) Like 'Con.CaseC*'));" Debug.Print sSql CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError sSql = "INSERT INTO [tblAppendCL-OAEKM] ( ID, [CL-Case No] ) " & _ " SELECT 2 AS ID, Right([F1],Len([F1])-8) AS [CL-CaseNo] " & _ " FROM [tblCL-CaseNo] " & _ " WHERE ((([tblCL-CaseNo].[F1]) Like 'OA*EKM*'));" Debug.Print sSql CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError sSql = "INSERT INTO [tblAppendCL-OP] ( ID, [CL-Case No] ) " & _ " SELECT 2 AS ID, Right([F1],Len([F1])-6) AS [CL-CaseNo] " & _ " FROM [tblCL-CaseNo] " & _ " WHERE ((([tblCL-CaseNo].[F1]) Like 'OPKAT*'));" Debug.Print sSql CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError sSql = "INSERT INTO [tblAppendCL-WA] ( ID, [CL-Case No] ) " & _ " SELECT 2 AS ID, Right([F1],Len([F1])-3) AS [CL-CaseNo] " & _ " FROM [tblCL-CaseNo] " & _ " WHERE ((([tblCL-CaseNo].[F1]) Like 'WA*'));" Debug.Print sSql CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError sSql = "UPDATE [tblCL-CaseNo] SET [tblCL-CaseNo].F1 = '0';" Debug.Print sSql CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError Me.Requery DoCmd.Hourglass False
Any that fail should be obvious.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Queries, forms, tables (and I think reports and macros) can all be opened in an mde or accde. Queries, macros and tables can even be opened in design view. In the case of queries, you can even see the sql statement.If you compile you database and save it as accde the end users can't get to anything much anyway.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.