Irina Much
asked on
Access - INSERT INTO SELECT VBA Code
Hello Experts,
I have a query within Access, which I want to have in VBA.
As a query it works fine:
SELECT [2b QD_Key_Tab].Key, [2b QD_Key_Tab].Unrestricted, [2a ZD_Key_Tab].Unrestricted, Round(([2b QD_Key_Tab].Unrestricted-[
FROM [2b QD_Key_Tab] LEFT JOIN [2a ZD_Key_Tab] ON [2b QD_Key_Tab].Key = [2a ZD_Key_Tab].Key;
I tried a VBA code:
Dim strGesamtabfrage As String
strGesamtabfrage = "INSERT INTO Gesamtabfrage SELECT [2b QD_Key_Tab].Key, [2b QD_Key_Tab].[Unrestricted]
"FROM [2b QD_Key_Tab] LEFT JOIN [2a ZD_Key_Tab] ON [2b QD_Key_Tab].Key = [2a ZD_Key_Tab].Key"
DoCmd.RunSQL (strGesamtabfrage)
And its not woking :(. I don't really understand why. I have similiar select statements done before using a VBA code and it works.
Access says ;): The query must based on a table or query. But both tables are existing.
Can anybody help :(?
I have a query within Access, which I want to have in VBA.
As a query it works fine:
SELECT [2b QD_Key_Tab].Key, [2b QD_Key_Tab].Unrestricted, [2a ZD_Key_Tab].Unrestricted, Round(([2b QD_Key_Tab].Unrestricted-[
FROM [2b QD_Key_Tab] LEFT JOIN [2a ZD_Key_Tab] ON [2b QD_Key_Tab].Key = [2a ZD_Key_Tab].Key;
I tried a VBA code:
Dim strGesamtabfrage As String
strGesamtabfrage = "INSERT INTO Gesamtabfrage SELECT [2b QD_Key_Tab].Key, [2b QD_Key_Tab].[Unrestricted]
"FROM [2b QD_Key_Tab] LEFT JOIN [2a ZD_Key_Tab] ON [2b QD_Key_Tab].Key = [2a ZD_Key_Tab].Key"
DoCmd.RunSQL (strGesamtabfrage)
And its not woking :(. I don't really understand why. I have similiar select statements done before using a VBA code and it works.
Access says ;): The query must based on a table or query. But both tables are existing.
Can anybody help :(?
ASKER
Hello Ryan,
thank you for the answer. I tried to put the line in, but nothing happens :(.
thank you for the answer. I tried to put the line in, but nothing happens :(.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
then a error comes: Access excpects Select, Update.....statement.I learned Access with google help and videos a few month ago...so I am a beginner in the VBA environment..maybe I do something wrong here :(.
...I opened the immediate window and put in the code. Thats what comming out....
ASKER
Hello Anders Ebro,
I put a space in between but the error stays the same :(. But thank you very much for the answer!
I put a space in between but the error stays the same :(. But thank you very much for the answer!
eye seeing debugging sometimes take time and it's painful.
do you think can upload the file here for debugging? (pls removed confidential data)
do you think can upload the file here for debugging? (pls removed confidential data)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have deleted everything that is not needed to execute the query. In the form is a button related to the needed query. Have to have the queries in the VBA. Unfortunatly excute the excisting query using the OpenQuery method is not an option.
Maybe someone sees the mistake here.
TEST.accdb
Maybe someone sees the mistake here.
TEST.accdb
>> [2a ZD_Key_Tab].[Unrestricted]
this seems wrong to me, try:
[2a ZD_Key_Tab].[Unrestricted]
this seems wrong to me, try:
[2a ZD_Key_Tab].[Unrestricted]
and ... >> INSERT INTO Gesamtabfrage
Gesamtabfrage clearly is a Query not a Table, you can't insert into a query! you need to insert into a table instead.
Gesamtabfrage clearly is a Query not a Table, you can't insert into a query! you need to insert into a table instead.
You need to check your aliases.
ASKER
Ryan you are right. Have forgotten to delete it. I tried some things out, because Access shows me some other errors before.
I do need the table name in the column name for the further queries :(.
If you execute the query 'Gesamtabfrage', you will see how it should looks like...I just do not understand why the query as a query is working as I need it and if I try to write the code in the VBA as I did before, it doesn't work.
And thank you so much for your effort!
I do need the table name in the column name for the further queries :(.
If you execute the query 'Gesamtabfrage', you will see how it should looks like...I just do not understand why the query as a query is working as I need it and if I try to write the code in the VBA as I did before, it doesn't work.
And thank you so much for your effort!
ASKER
..don't know whats wrong with the aliases, if it's working fine as a query. :(
...have change the Insert into ...select....to SELECT INTO...
TEST.accdb
...have change the Insert into ...select....to SELECT INTO...
TEST.accdb
for simplicity.. you can try rename the Query Gesamtabfrage to another name, like: Gesamtabfrage_tmp
and then since you got the table: Gesamtabfrage
you can directly try as:
and then since you got the table: Gesamtabfrage
you can directly try as:
insert into Gesamtabfrage
select * from Gesamtabfrage_tmp
When I try to execute the INSERT INTO...SELECT in the database the first error message points towards [2a ZD_Key_Tab].[Unrestricted]
If I change the alias there to a simple string, eg 2aZDUnrestricted, I can get past the error but then I get other errors that appear to be connected with the aliases.
When I remove the INSERT INTO... part the SELECT query doesn't work either.
If I change the alias there to a simple string, eg 2aZDUnrestricted, I can get past the error but then I get other errors that appear to be connected with the aliases.
When I remove the INSERT INTO... part the SELECT query doesn't work either.
ASKER
.....so why this is working as query and not within the VBA environment? Just don't get it.....
ASKER
And I want to replace the 'Gesamtabfrage' query with a VBA code. So afterwards the query will be deleted.
a bit confusing now, so you got 2 source tables:
2a ZD_Key_Tab
2b QD_Key_Tab
from the joining sql statement, do you want to:
1. insert data into a table called as: Gesamtabfrage, OR
2. create a query named as Gesamtabfrage ?
2a ZD_Key_Tab
2b QD_Key_Tab
from the joining sql statement, do you want to:
1. insert data into a table called as: Gesamtabfrage, OR
2. create a query named as Gesamtabfrage ?
ASKER
I want to create a table named: Gesamtabfrage :).
Sorry for the confusion. So the VBA code should do the same as the query I named: Gesamtabfrage. After ...the query will be deleted.
Sorry for the confusion. So the VBA code should do the same as the query I named: Gesamtabfrage. After ...the query will be deleted.
got a feel that you need to rename your fields since you didn't do that in your original query.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh that works! Thanks a lot! :)
So is there a reason why vba had problems with the names of the columns and in the query it works? For me it makes no sense...
vbCrLf <- and what is this standing for?
Thank you so much again!!!
So is there a reason why vba had problems with the names of the columns and in the query it works? For me it makes no sense...
vbCrLf <- and what is this standing for?
Thank you so much again!!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
do you need further assistance here or else we can proceed to close this question.
as suggested
Dim strGesamtabfrage As String
strGesamtabfrage = "INSERT INTO Gesamtabfrage SELECT [2b QD_Key_Tab].Key, [2b QD_Key_Tab].[Unrestricted]
"FROM [2b QD_Key_Tab] LEFT JOIN [2a ZD_Key_Tab] ON [2b QD_Key_Tab].Key = [2a ZD_Key_Tab].Key"
debug.print strGesamtabfrage
'DoCmd.RunSQL (strGesamtabfrage)