How to execute Insert query within VBA code
-1
I am trying to select rows from one table based on condition that it matches with user names from 2nd table. I am getting error just running this query.
Next, I need to insert these selected rows into 3rd table.
Currently I am getting syntax error for strSQL1. Thanks!
Public Sub CalculateUA1_Click()
Dim db As Database
Dim rst As Recordset
Dim UsersTname As String
Dim SCCMTname As String
Dim UsersAppList1 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQLWhere As String
Set db = DBEngine.Workspaces(0).Databases(0)
UsersTname = "UsersList"
SCCMTname = "SCCM1"
UsersAppList1 = "tbl_UsersApplicationList1"
Set rst = db.OpenRecordset(UsersTname)
strSQL1 = "SELECT s.User_Name0, s.[Machine name], s.displayName0, s.Mail0,
s.AD_Site_Name0, s.[Application Name], s.[Application Version] FROM '" &
SCCMTname & "' as S ; "
strSQLWhere = "user_name0 in ( SELECT [SOE ID] FROM '" & UsersTname & "'
)"
strSQL1 = strSQL1 & " Where " & strSQLWhere
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL1
DoCmd.SetWarnings True
strSQL2 = "INSERT INTO '" & UsersAppList1 & "' VALUES ('" & strSQL1 & "'
);"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True
sql excel ms-access access-vba
asked Nov 13 '18 at 1:54
JimmyJimmy
12
add a comment |
-1
I am trying to select rows from one table based on condition that it matches with user names from 2nd table. I am getting error just running this query.
Next, I need to insert these selected rows into 3rd table.
Currently I am getting syntax error for strSQL1. Thanks!
Public Sub CalculateUA1_Click()
Dim db As Database
Dim rst As Recordset
Dim UsersTname As String
Dim SCCMTname As String
Dim UsersAppList1 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQLWhere As String
Set db = DBEngine.Workspaces(0).Databases(0)
UsersTname = "UsersList"
SCCMTname = "SCCM1"
UsersAppList1 = "tbl_UsersApplicationList1"
Set rst = db.OpenRecordset(UsersTname)
strSQL1 = "SELECT s.User_Name0, s.[Machine name], s.displayName0, s.Mail0,
s.AD_Site_Name0, s.[Application Name], s.[Application Version] FROM '" &
SCCMTname & "' as S ; "
strSQLWhere = "user_name0 in ( SELECT [SOE ID] FROM '" & UsersTname & "'
)"
strSQL1 = strSQL1 & " Where " & strSQLWhere
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL1
DoCmd.SetWarnings True
strSQL2 = "INSERT INTO '" & UsersAppList1 & "' VALUES ('" & strSQL1 & "'
);"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True
sql excel ms-access access-vba
asked Nov 13 '18 at 1:54
JimmyJimmy
12
1
Too many errors. You'd better create SQL in query builder, then copy it to code, replacing constants with variables. strSQL1 cannot be executed using RunSQL and you don't need it. Create and execute INSERT
– Sergey S.
Nov 13 '18 at 2:30
I do have query within access working all good, I am trying to create access form for other users to use. For that I need to write this query within VBA. if not runsql then what command should I use like db.execute?
– Jimmy
Nov 13 '18 at 2:45
If the queries work in Access already, the main problem is generating proper SQL in VBA code. Sergey just gave you the code (a disservice in my opinion), but the real key is learning to use breakpoints, inspecting (i.e. watching) variables, stepping through code, etc.... learning how to debug properly. In particular, if you have a problem with a generated SQL statement, then you should inspect the final SQL text from the code so that you can verify the syntax. One easy way to do this is likePrint.Debug strSQL1to display the text in the VBA immediate window.
– C Perkins
Nov 13 '18 at 6:09
add a comment |
-1
-1
-1
I am trying to select rows from one table based on condition that it matches with user names from 2nd table. I am getting error just running this query.
Next, I need to insert these selected rows into 3rd table.
Currently I am getting syntax error for strSQL1. Thanks!
Public Sub CalculateUA1_Click()
Dim db As Database
Dim rst As Recordset
Dim UsersTname As String
Dim SCCMTname As String
Dim UsersAppList1 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQLWhere As String
Set db = DBEngine.Workspaces(0).Databases(0)
UsersTname = "UsersList"
SCCMTname = "SCCM1"
UsersAppList1 = "tbl_UsersApplicationList1"
Set rst = db.OpenRecordset(UsersTname)
strSQL1 = "SELECT s.User_Name0, s.[Machine name], s.displayName0, s.Mail0,
s.AD_Site_Name0, s.[Application Name], s.[Application Version] FROM '" &
SCCMTname & "' as S ; "
strSQLWhere = "user_name0 in ( SELECT [SOE ID] FROM '" & UsersTname & "'
)"
strSQL1 = strSQL1 & " Where " & strSQLWhere
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL1
DoCmd.SetWarnings True
strSQL2 = "INSERT INTO '" & UsersAppList1 & "' VALUES ('" & strSQL1 & "'
);"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True
sql excel ms-access access-vba
asked Nov 13 '18 at 1:54
JimmyJimmy
12
I am trying to select rows from one table based on condition that it matches with user names from 2nd table. I am getting error just running this query.
Next, I need to insert these selected rows into 3rd table.
Currently I am getting syntax error for strSQL1. Thanks!
Public Sub CalculateUA1_Click()
Dim db As Database
Dim rst As Recordset
Dim UsersTname As String
Dim SCCMTname As String
Dim UsersAppList1 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQLWhere As String
Set db = DBEngine.Workspaces(0).Databases(0)
UsersTname = "UsersList"
SCCMTname = "SCCM1"
UsersAppList1 = "tbl_UsersApplicationList1"
Set rst = db.OpenRecordset(UsersTname)
strSQL1 = "SELECT s.User_Name0, s.[Machine name], s.displayName0, s.Mail0,
s.AD_Site_Name0, s.[Application Name], s.[Application Version] FROM '" &
SCCMTname & "' as S ; "
strSQLWhere = "user_name0 in ( SELECT [SOE ID] FROM '" & UsersTname & "'
)"
strSQL1 = strSQL1 & " Where " & strSQLWhere
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL1
DoCmd.SetWarnings True
strSQL2 = "INSERT INTO '" & UsersAppList1 & "' VALUES ('" & strSQL1 & "'
);"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True
sql excel ms-access access-vba
sql excel ms-access access-vba
asked Nov 13 '18 at 1:54
JimmyJimmy
12
asked Nov 13 '18 at 1:54
JimmyJimmy
12
asked Nov 13 '18 at 1:54
JimmyJimmy
12
asked Nov 13 '18 at 1:54
JimmyJimmy
12
asked Nov 13 '18 at 1:54
JimmyJimmy
12
12
1
Too many errors. You'd better create SQL in query builder, then copy it to code, replacing constants with variables. strSQL1 cannot be executed using RunSQL and you don't need it. Create and execute INSERT
– Sergey S.
Nov 13 '18 at 2:30
I do have query within access working all good, I am trying to create access form for other users to use. For that I need to write this query within VBA. if not runsql then what command should I use like db.execute?
– Jimmy
Nov 13 '18 at 2:45
If the queries work in Access already, the main problem is generating proper SQL in VBA code. Sergey just gave you the code (a disservice in my opinion), but the real key is learning to use breakpoints, inspecting (i.e. watching) variables, stepping through code, etc.... learning how to debug properly. In particular, if you have a problem with a generated SQL statement, then you should inspect the final SQL text from the code so that you can verify the syntax. One easy way to do this is likePrint.Debug strSQL1to display the text in the VBA immediate window.
– C Perkins
Nov 13 '18 at 6:09
add a comment |
1
Too many errors. You'd better create SQL in query builder, then copy it to code, replacing constants with variables. strSQL1 cannot be executed using RunSQL and you don't need it. Create and execute INSERT
– Sergey S.
Nov 13 '18 at 2:30
I do have query within access working all good, I am trying to create access form for other users to use. For that I need to write this query within VBA. if not runsql then what command should I use like db.execute?
– Jimmy
Nov 13 '18 at 2:45
If the queries work in Access already, the main problem is generating proper SQL in VBA code. Sergey just gave you the code (a disservice in my opinion), but the real key is learning to use breakpoints, inspecting (i.e. watching) variables, stepping through code, etc.... learning how to debug properly. In particular, if you have a problem with a generated SQL statement, then you should inspect the final SQL text from the code so that you can verify the syntax. One easy way to do this is likePrint.Debug strSQL1to display the text in the VBA immediate window.
– C Perkins
Nov 13 '18 at 6:09
1
1
Too many errors. You'd better create SQL in query builder, then copy it to code, replacing constants with variables. strSQL1 cannot be executed using RunSQL and you don't need it. Create and execute INSERT
– Sergey S.
Nov 13 '18 at 2:30
Too many errors. You'd better create SQL in query builder, then copy it to code, replacing constants with variables. strSQL1 cannot be executed using RunSQL and you don't need it. Create and execute INSERT
– Sergey S.
Nov 13 '18 at 2:30
I do have query within access working all good, I am trying to create access form for other users to use. For that I need to write this query within VBA. if not runsql then what command should I use like db.execute?
– Jimmy
Nov 13 '18 at 2:45
I do have query within access working all good, I am trying to create access form for other users to use. For that I need to write this query within VBA. if not runsql then what command should I use like db.execute?
– Jimmy
Nov 13 '18 at 2:45
If the queries work in Access already, the main problem is generating proper SQL in VBA code. Sergey just gave you the code (a disservice in my opinion), but the real key is learning to use breakpoints, inspecting (i.e. watching) variables, stepping through code, etc.... learning how to debug properly. In particular, if you have a problem with a generated SQL statement, then you should inspect the final SQL text from the code so that you can verify the syntax. One easy way to do this is like
Print.Debug strSQL1 to display the text in the VBA immediate window.– C Perkins
Nov 13 '18 at 6:09
If the queries work in Access already, the main problem is generating proper SQL in VBA code. Sergey just gave you the code (a disservice in my opinion), but the real key is learning to use breakpoints, inspecting (i.e. watching) variables, stepping through code, etc.... learning how to debug properly. In particular, if you have a problem with a generated SQL statement, then you should inspect the final SQL text from the code so that you can verify the syntax. One easy way to do this is like
Print.Debug strSQL1 to display the text in the VBA immediate window.– C Perkins
Nov 13 '18 at 6:09
add a comment |
1 Answer
1
active
oldest
votes
0
Something like this:
Dim db As Database
Dim UsersTname As String
Dim SCCMTname As String
Dim UsersAppList1 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQLWhere As String
Set db = DBEngine.Workspaces(0).Databases(0)
UsersTname = "UsersList"
SCCMTname = "SCCM1"
UsersAppList1 = "tbl_UsersApplicationList1"
strSQL1 = "SELECT s.User_Name0, s.[Machine name], s.displayName0, s.Mail0, " & _
"s.AD_Site_Name0, s.[Application Name], s.[Application Version] FROM [" & SCCMTname & "] as S; "
strSQLWhere = "user_name0 in (SELECT [SOE ID] FROM [" & UsersTname & "])"
strSQL1 = strSQL1 & " Where " & strSQLWhere
'variant 1: append to existing table, it should have columns with the same names as in SQL1
strSQL2 = "INSERT INTO [" & UsersAppList1 & "] " & strSQL1
'variant 2: create new table. Existing table with the same name will be deleted
strSQL2 = "SELECT * INTO [" & UsersAppList1 & "] FROM (" & strSQL1 & ");)"
db.Execute strSQL2, dbFailOnError
answered Nov 13 '18 at 2:47
Sergey S.Sergey S.
5,6101927
Thanks it is working for creating new table now. However, for my solution I require to append into the existing table. I tried to modify your code but couldn't get it working. Currently I am trying this code strSQL2 = "INSERT INTO [" & UsersAppList1 & "] (User Name,Machine Name, Display Name, Email Address, Site Name, Application Name, Application Version) (" & strSQL1 & ");"
– Jimmy
Nov 13 '18 at 3:27
Sorry, my bad. When tried again your code does work. Thank you very much! :-)
– Jimmy
Nov 13 '18 at 4:26
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53272661%2fhow-to-execute-insert-query-within-vba-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Something like this:
Dim db As Database
Dim UsersTname As String
Dim SCCMTname As String
Dim UsersAppList1 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQLWhere As String
Set db = DBEngine.Workspaces(0).Databases(0)
UsersTname = "UsersList"
SCCMTname = "SCCM1"
UsersAppList1 = "tbl_UsersApplicationList1"
strSQL1 = "SELECT s.User_Name0, s.[Machine name], s.displayName0, s.Mail0, " & _
"s.AD_Site_Name0, s.[Application Name], s.[Application Version] FROM [" & SCCMTname & "] as S; "
strSQLWhere = "user_name0 in (SELECT [SOE ID] FROM [" & UsersTname & "])"
strSQL1 = strSQL1 & " Where " & strSQLWhere
'variant 1: append to existing table, it should have columns with the same names as in SQL1
strSQL2 = "INSERT INTO [" & UsersAppList1 & "] " & strSQL1
'variant 2: create new table. Existing table with the same name will be deleted
strSQL2 = "SELECT * INTO [" & UsersAppList1 & "] FROM (" & strSQL1 & ");)"
db.Execute strSQL2, dbFailOnError
answered Nov 13 '18 at 2:47
Sergey S.Sergey S.
5,6101927
Thanks it is working for creating new table now. However, for my solution I require to append into the existing table. I tried to modify your code but couldn't get it working. Currently I am trying this code strSQL2 = "INSERT INTO [" & UsersAppList1 & "] (User Name,Machine Name, Display Name, Email Address, Site Name, Application Name, Application Version) (" & strSQL1 & ");"
– Jimmy
Nov 13 '18 at 3:27
Sorry, my bad. When tried again your code does work. Thank you very much! :-)
– Jimmy
Nov 13 '18 at 4:26
add a comment |
0
Something like this:
Dim db As Database
Dim UsersTname As String
Dim SCCMTname As String
Dim UsersAppList1 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQLWhere As String
Set db = DBEngine.Workspaces(0).Databases(0)
UsersTname = "UsersList"
SCCMTname = "SCCM1"
UsersAppList1 = "tbl_UsersApplicationList1"
strSQL1 = "SELECT s.User_Name0, s.[Machine name], s.displayName0, s.Mail0, " & _
"s.AD_Site_Name0, s.[Application Name], s.[Application Version] FROM [" & SCCMTname & "] as S; "
strSQLWhere = "user_name0 in (SELECT [SOE ID] FROM [" & UsersTname & "])"
strSQL1 = strSQL1 & " Where " & strSQLWhere
'variant 1: append to existing table, it should have columns with the same names as in SQL1
strSQL2 = "INSERT INTO [" & UsersAppList1 & "] " & strSQL1
'variant 2: create new table. Existing table with the same name will be deleted
strSQL2 = "SELECT * INTO [" & UsersAppList1 & "] FROM (" & strSQL1 & ");)"
db.Execute strSQL2, dbFailOnError
answered Nov 13 '18 at 2:47
Sergey S.Sergey S.
5,6101927
Thanks it is working for creating new table now. However, for my solution I require to append into the existing table. I tried to modify your code but couldn't get it working. Currently I am trying this code strSQL2 = "INSERT INTO [" & UsersAppList1 & "] (User Name,Machine Name, Display Name, Email Address, Site Name, Application Name, Application Version) (" & strSQL1 & ");"
– Jimmy
Nov 13 '18 at 3:27
Sorry, my bad. When tried again your code does work. Thank you very much! :-)
– Jimmy
Nov 13 '18 at 4:26
add a comment |
0
0
0
Something like this:
Dim db As Database
Dim UsersTname As String
Dim SCCMTname As String
Dim UsersAppList1 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQLWhere As String
Set db = DBEngine.Workspaces(0).Databases(0)
UsersTname = "UsersList"
SCCMTname = "SCCM1"
UsersAppList1 = "tbl_UsersApplicationList1"
strSQL1 = "SELECT s.User_Name0, s.[Machine name], s.displayName0, s.Mail0, " & _
"s.AD_Site_Name0, s.[Application Name], s.[Application Version] FROM [" & SCCMTname & "] as S; "
strSQLWhere = "user_name0 in (SELECT [SOE ID] FROM [" & UsersTname & "])"
strSQL1 = strSQL1 & " Where " & strSQLWhere
'variant 1: append to existing table, it should have columns with the same names as in SQL1
strSQL2 = "INSERT INTO [" & UsersAppList1 & "] " & strSQL1
'variant 2: create new table. Existing table with the same name will be deleted
strSQL2 = "SELECT * INTO [" & UsersAppList1 & "] FROM (" & strSQL1 & ");)"
db.Execute strSQL2, dbFailOnError
answered Nov 13 '18 at 2:47
Sergey S.Sergey S.
5,6101927
Something like this:
Dim db As Database
Dim UsersTname As String
Dim SCCMTname As String
Dim UsersAppList1 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQLWhere As String
Set db = DBEngine.Workspaces(0).Databases(0)
UsersTname = "UsersList"
SCCMTname = "SCCM1"
UsersAppList1 = "tbl_UsersApplicationList1"
strSQL1 = "SELECT s.User_Name0, s.[Machine name], s.displayName0, s.Mail0, " & _
"s.AD_Site_Name0, s.[Application Name], s.[Application Version] FROM [" & SCCMTname & "] as S; "
strSQLWhere = "user_name0 in (SELECT [SOE ID] FROM [" & UsersTname & "])"
strSQL1 = strSQL1 & " Where " & strSQLWhere
'variant 1: append to existing table, it should have columns with the same names as in SQL1
strSQL2 = "INSERT INTO [" & UsersAppList1 & "] " & strSQL1
'variant 2: create new table. Existing table with the same name will be deleted
strSQL2 = "SELECT * INTO [" & UsersAppList1 & "] FROM (" & strSQL1 & ");)"
db.Execute strSQL2, dbFailOnError
answered Nov 13 '18 at 2:47
Sergey S.Sergey S.
5,6101927
answered Nov 13 '18 at 2:47
Sergey S.Sergey S.
5,6101927
answered Nov 13 '18 at 2:47
Sergey S.Sergey S.
5,6101927
answered Nov 13 '18 at 2:47
Sergey S.Sergey S.
5,6101927
5,6101927
Thanks it is working for creating new table now. However, for my solution I require to append into the existing table. I tried to modify your code but couldn't get it working. Currently I am trying this code strSQL2 = "INSERT INTO [" & UsersAppList1 & "] (User Name,Machine Name, Display Name, Email Address, Site Name, Application Name, Application Version) (" & strSQL1 & ");"
– Jimmy
Nov 13 '18 at 3:27
Sorry, my bad. When tried again your code does work. Thank you very much! :-)
– Jimmy
Nov 13 '18 at 4:26
add a comment |
Thanks it is working for creating new table now. However, for my solution I require to append into the existing table. I tried to modify your code but couldn't get it working. Currently I am trying this code strSQL2 = "INSERT INTO [" & UsersAppList1 & "] (User Name,Machine Name, Display Name, Email Address, Site Name, Application Name, Application Version) (" & strSQL1 & ");"
– Jimmy
Nov 13 '18 at 3:27
Sorry, my bad. When tried again your code does work. Thank you very much! :-)
– Jimmy
Nov 13 '18 at 4:26
Thanks it is working for creating new table now. However, for my solution I require to append into the existing table. I tried to modify your code but couldn't get it working. Currently I am trying this code strSQL2 = "INSERT INTO [" & UsersAppList1 & "] (User Name,Machine Name, Display Name, Email Address, Site Name, Application Name, Application Version) (" & strSQL1 & ");"
– Jimmy
Nov 13 '18 at 3:27
Thanks it is working for creating new table now. However, for my solution I require to append into the existing table. I tried to modify your code but couldn't get it working. Currently I am trying this code strSQL2 = "INSERT INTO [" & UsersAppList1 & "] (User Name,Machine Name, Display Name, Email Address, Site Name, Application Name, Application Version) (" & strSQL1 & ");"
– Jimmy
Nov 13 '18 at 3:27
Sorry, my bad. When tried again your code does work. Thank you very much! :-)
– Jimmy
Nov 13 '18 at 4:26
Sorry, my bad. When tried again your code does work. Thank you very much! :-)
– Jimmy
Nov 13 '18 at 4:26
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53272661%2fhow-to-execute-insert-query-within-vba-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Too many errors. You'd better create SQL in query builder, then copy it to code, replacing constants with variables. strSQL1 cannot be executed using RunSQL and you don't need it. Create and execute INSERT
– Sergey S.
Nov 13 '18 at 2:30
I do have query within access working all good, I am trying to create access form for other users to use. For that I need to write this query within VBA. if not runsql then what command should I use like db.execute?
– Jimmy
Nov 13 '18 at 2:45
If the queries work in Access already, the main problem is generating proper SQL in VBA code. Sergey just gave you the code (a disservice in my opinion), but the real key is learning to use breakpoints, inspecting (i.e. watching) variables, stepping through code, etc.... learning how to debug properly. In particular, if you have a problem with a generated SQL statement, then you should inspect the final SQL text from the code so that you can verify the syntax. One easy way to do this is like
Print.Debug strSQL1to display the text in the VBA immediate window.– C Perkins
Nov 13 '18 at 6:09