Access VBA- Copy Query results into Table(in Access)--need help? (1 Viewer)

bnfkru4567

New member
Local time
Tomorrow, 06:10
Joined
Dec 2, 2021
Messages
10
Dear All Experts,
I need your help into ACCESS VBA as I am facing the following problem:

Background:
In my Access file, I have a query and 3 tables.
Query was named as "run-query" (has 14 fields)
1st table was named as "source1"
2nd table was named as "source2"
3rd table was named as "result" (has 14 fields)
The first two tables are the sources and use the query "run-query" to run those sources. After run the Query, I would like to copy Query Results and paste them to < result>table.

(Remark: the name of fields in "run-query" and <result> are the same.)

I use the ACCESS VBA to run "Queries" a lot of times per day (so will not consider to use Access -Query --Append function). After each run the "query", I have to copy and paste the results to <results>table.

Problem:
Now I am facing the problem to copy and paste the Query-Results into <result> table. If the <result>table is empty(let say it is the first time to run today), my VBA code is working. However, if it is not the first time of day to run VBA (Hence: there are a few records/rows in <result>table), then it will not work. Thus, I have to keep all the query results in <result>tables for further processing.


Could you all please check the following VBA codes and tell me how to improve them further ?
Thanks in advance

Private sub btnTest_Click()

Dim db as DAO.database
Dim rs as DAO.Recordset
Dim I1 as integer

DoCmd.OpenQuery "run-query"
Docmd. Close

<remark: it works for the above first part to run Query>

Set db=CurrentDb()
DoCmd.OpenQuery "run-query", acViewNormal, acReadOnly
DoCmd. SelectObject acQuery,. "run-query"

DoCmd.RunCommand acCmdSelectAllRecords
RunCommand acCmdCopy

Set rs=db.OpenRecordset("result", dbOpenDynast)
DoCmd.OpenTable"result", acViewNormal, acEdit

On Error Resume Next
rs.MoveFirst
rs.MoveLast
I1=rs.Record.Count

If I1>1 then
rs.MoveLast
rs. AddNew
DoCmd.GoToRecord, , acNewRec

DoCmd.RunCommand acCmdPasteAppend <remark: even I used "acCmdPaste" but it is not working>

Else
DoCmd.RunCommand acCmdPasteAppend <remark: it works as <result>table has no record>

end if

rs.Update
DoCmd.Close acQuery, "run-query", acSaveNo <remark: it works>

end sub
 
Last edited:

Ranman256

Well-known member
Local time
Today, 18:10
Joined
Apr 9, 2015
Messages
4,339
run an append query. No code needed.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:10
Joined
May 21, 2018
Messages
8,463
Definitely do this with an append.
 

bnfkru4567

New member
Local time
Tomorrow, 06:10
Joined
Dec 2, 2021
Messages
10
run an append query. No code needed.
I think your idea is not suitable for my case. Why? I clearly mentioned that I had to run query a lot of times per day. Thus, my table "sources 1&2" will be updated each time before run query (I didnt mentioned). So Append is not suitable for this case.
 

bnfkru4567

New member
Local time
Tomorrow, 06:10
Joined
Dec 2, 2021
Messages
10
Dear All Experts,
I need your help into ACCESS VBA as I am facing the following problem:

Background:
In my Access file, I have a query and 3 tables.
Query was named as "run-query" (has 14 fields)
1st table was named as "source1"
2nd table was named as "source2"
3rd table was named as "result" (has 14 fields)
The first two tables are the sources and use the query "run-query" to run those sources. After run the Query, I would like to copy Query Results and paste them to < result>table.

(Remark: the name of fields in "run-query" and <result> are the same.)

I use the ACCESS VBA to run "Queries" a lot of times per day (so will not consider to use Access -Query --Append function). After each run the "query", I have to copy and paste the results to <results>table.

Problem:
Now I am facing the problem to copy and paste the Query-Results into <result> table. If the <result>table is empty(let say it is the first time to run today), my VBA code is working. However, if it is not the first time of day to run VBA (Hence: there are a few records/rows in <result>table), then it will not work. Thus, I have to keep all the query results in <result>tables for further processing.


Could you all please check the following VBA codes and tell me how to improve them further ?
Thanks in advance

Private sub btnTest_Click()

Dim db as DAO.database
Dim rs as DAO.Recordset
Dim I1 as integer

DoCmd.OpenQuery "run-query"
Docmd. Close

<remark: it works for the above first part to run Query>

Set db=CurrentDb()
DoCmd.OpenQuery "run-query", acViewNormal, acReadOnly
DoCmd. SelectObject acQuery,. "run-query"

DoCmd.RunCommand acCmdSelectAllRecords
RunCommand acCmdCopy

Set rs=db.OpenRecordset("result", dbOpenDynast)
DoCmd.OpenTable"result", acViewNormal, acEdit

On Error Resume Next
rs.MoveFirst
rs.MoveLast
I1=rs.Record.Count

If I1>1 then
rs.MoveLast
rs. AddNew
DoCmd.GoToRecord, , acNewRec

DoCmd.RunCommand acCmdPasteAppend <remark: even I used "acCmdPaste" but it is not working>

Else
DoCmd.RunCommand acCmdPasteAppend <remark: it works as <result>table has no record>

end if

rs.Update
DoCmd.Close acQuery, "run-query", acSaveNo <remark: it works>

end sub
Dear Experts

If NOT use "Append", do you have alternative idea to update the above VBA codes in order to run it smoothly ?

Thanks
 

Minty

AWF VIP
Local time
Today, 22:10
Joined
Jul 26, 2013
Messages
10,355
I think your idea is not suitable for my case. Why? I clearly mentioned that I had to run query a lot of times per day. Thus, my table "sources 1&2" will be updated each time before run query (I didnt mentioned). So Append is not suitable for this case.
Why not - you are doing a record count, checking you have some records then copying a record.
All of this is normally achievable in an append query.

Make your "run-query" the source for your append query.
If there are no records it won't do anything, if there are it will.
Simples.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:10
Joined
Feb 19, 2013
Messages
16,553
Your code is not indented so difficult to read. For the future please use the code tags (highlight code and click the </> button) to preserve indentation.

Suggest you step through your code a line at a time and check values are as you expect.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:10
Joined
Sep 21, 2011
Messages
14,047
Plus I do not think you can select a bunch of records and then expect to paste it into an individual record.?

This is Access not Excel? :(
 

plog

Banishment Pending
Local time
Today, 17:10
Joined
May 11, 2011
Messages
11,612
I use the ACCESS VBA to run "Queries" a lot of times per day (so will not consider to use Access -Query --Append function).

Your argument (run a lot of times per day) does not support your conclusion (cannot use APPEND).

Look, everyone who's responed on this thread so far could duct tape and bubble gum at least 10 different ways to accomplish this if it was a rube-goldberg contest (for loop, while loop, export to a text file and then re-import it, use a series of temporary tables, etc.), . However, if they just wanted to get it done the most efficiently, they would all use a simple APPEND query.

What's wrong with an APPEND query? Please expand on your prior dismissal.
 

bnfkru4567

New member
Local time
Tomorrow, 06:10
Joined
Dec 2, 2021
Messages
10
Dear all

I had already run my VBA code step by step.

Let say this way:
1st run "run-query", then I have 100 records in the result
-my VBA Code can copy and paste all the 100 records into <result>table

2nd run "run-query" ,then I have 25 records in the result
My VBA code is not working completely !
-15 record out of 25 are the brand new. So the 15 records were pasted to <result>table
-the remaining records (10 out of 25) were not pasted into<result>table. I found that the 10 records were presented into the first 1st run and were into <result>table

3rd run "run-query, then I have 30 records in the result
My VBA code is working again and can copy & paste all the records into the <result>table. As the 30 records are brand new and not related into 1st&2nd run.

Now, how can I change the VBA code in order to copy and paste the duplicate records into <result>table?
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:10
Joined
Sep 21, 2011
Messages
14,047
Do not make them duplicates ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:10
Joined
Sep 21, 2011
Messages
14,047
It is not possible ---I will expect that a few of them will be duplicated
Yes, but it sounds like the result table is set NOT to have duplicates?
That will likely be why not all records were appended on your previous run?
 

bnfkru4567

New member
Local time
Tomorrow, 06:10
Joined
Dec 2, 2021
Messages
10
Yes, but it sounds like the result table is set NOT to have duplicates?
That will likely be why not all records were appended on your previous run?
Yes, but it sounds like the result table is set NOT to have duplicates?<-------you are right from normal situation but for my situation , I will expect that some data were duplicated.

That will likely be why not all records were appended on your previous run?<---the answer is
Some data are as at 1pm but some data are as at 2pm



Dear Experts

Finally, I figure out how to solve the above problem.

Thanks for your help/ suggestion.
 

oleronesoftwares

Passionate Learner
Local time
Today, 15:10
Joined
Sep 22, 2014
Messages
1,159
i found this online

CurrentDb.Execute "INSERT INTO [Excel 12.0 Macro;HDR=Yes;DATABASE=M:\Merch. Strategy\Merch Forecasting\Automotive Division\File Sharing\Automotive Deal-Page-Slot View\Deal-Page-Slot-View.xlsm].[Sheet1$] SELECT * FROM Data_SlotView;"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:10
Joined
Feb 19, 2002
Messages
42,976
If you want to allow duplicates, then remove the primary key constraints.

There is no reason to write code to do this. You need an append query but we need the rules. An append query can ignore duplicates or it can raise errors, your choice. Removing the PK removes the possibility of duplicates. Why you would do that is a mystery but you can do it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:10
Joined
Sep 21, 2011
Messages
14,047
Yes, but it sounds like the result table is set NOT to have duplicates?<-------you are right from normal situation but for my situation , I will expect that some data were duplicated.

That will likely be why not all records were appended on your previous run?<---the answer is
Some data are as at 1pm but some data are as at 2pm



Dear Experts

Finally, I figure out how to solve the above problem.

Thanks for your help/ suggestion.:(
Well please post the solution, as it might help others, you never know? :(
That is what this site is about.
 

bnfkru4567

New member
Local time
Tomorrow, 06:10
Joined
Dec 2, 2021
Messages
10
Well please post the solution, as it might help others, you never know? :(
That is what this site is about.
@Gasman the answer is that in <result>table, I found the Primary Key that does not allow "duplicate". At the beginning, I copied from other table (i.e. the field only) but didnt know that Access would treat the first field as Primary Key and not allow "duplicate"

@Pat Hartman
In Query--Append, how did you change the setting to accept or ignore "duplicate" and raise the "duplicate" warning ?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:10
Joined
Feb 19, 2002
Messages
42,976
You can turn the warning messages off. I have two macros, the only ones in any app I create. One turns warnings off and the hourglass on and the other turns warnings on and the hourglass off. Turning warnings off is very dangerous so you don't want to ever forget you did it. Having once lost hours of work due to the warnings being off, I decided on the macro so I never forget about the hourglass. Having the hourglass on is so annoying, that you won't forget warnings are off and you can just run the on macro to restore everything. Access still won't allow duplicates. It just won't tell you about them. It definitely works with an append query. It might work with paste but since I would never, ever use paste, I have no idea. As I said earlier, remove the primary key constraint - leave the field there - just remove the PK attribute.

Access doesn't automatically assume the first field is a PK. You defined it as a PK when the table was created. If a PK is not defined by you when you first save the table, Access asks if you want to create one. It then creates an autonumber PK and names it ID. So, if you are pasting something numeric into that field, Access will reject any duplicates.

Also, don't paste data when you can use an append query. With the append query, you have much more control over what happens.
 

bnfkru4567

New member
Local time
Tomorrow, 06:10
Joined
Dec 2, 2021
Messages
10
You can turn the warning messages off. I have two macros, the only ones in any app I create. One turns warnings off and the hourglass on and the other turns warnings on and the hourglass off. Turning warnings off is very dangerous so you don't want to ever forget you did it. Having once lost hours of work due to the warnings being off, I decided on the macro so I never forget about the hourglass. Having the hourglass on is so annoying, that you won't forget warnings are off and you can just run the on macro to restore everything. Access still won't allow duplicates. It just won't tell you about them. It definitely works with an append query. It might work with paste but since I would never, ever use paste, I have no idea. As I said earlier, remove the primary key constraint - leave the field there - just remove the PK attribute.

Access doesn't automatically assume the first field is a PK. You defined it as a PK when the table was created. If a PK is not defined by you when you first save the table, Access asks if you want to create one. It then creates an autonumber PK and names it ID. So, if you are pasting something numeric into that field, Access will reject any duplicates.

Also, don't paste data when you can use an append query. With the append query, you have much more control over what happens.
Cool thanks for explanations
 

Users who are viewing this thread

Top Bottom