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

[bnfkru4567]

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

 

[Ranman256]

run an append query. No code needed.

 

[MajP]

Definitely do this with an append.

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[bnfkru4567]

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]

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]

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]

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]

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]

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]

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]

Do not make them duplicates ?

 

[bnfkru4567]

Do not make them duplicates ?

It is not possible ---I will expect that a few of them will be duplicated

 

[Gasman]

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]

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]

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]

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]

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]

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]

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]

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