[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 919
  • Last Modified:

Microsoft Access - Append Query and VBA Procedure

Im creating a database where there are three tables (call them table1, table2 and table3) which have most of the same fields in all of them. I have created two append (call them append1 and append2) and two delete queries (call them delete1 and delete2).
Now when i want to transfer records from table1 to table2 via a form, i press a button on this form and it transfers the records from table1 to table2 using append1 and then deletes the records from table1 using delete1. This works perfectly.

The problem is that i cant seem to transfer the records from table2 to table3 using append2 and it does not delete using delete2. I have checked everything i possibly can from the data format to going through the VBA code where i have embedded the sql statements. Keep in mind that append1 and append2 use the same data to transfer, the same applies to delete1 and delete2.

Any help would be greatly appreciated, Thank You.
0
deevyas
Asked:
deevyas
  • 3
  • 3
2 Solutions
 
gbentleyCommented:
If you're creating the queries on the fly in VBA, try saving the full text of the query. Then manually create a query to do the job, and compare the two. You'll probably see where they differ.

If the queries are created using the Query Editor, do they run manually? If not, there's no chance they'll work programmatically.

Hope that helps.
0
 
deevyasAuthor Commented:
Im not quite sure i understand what your trying to say, anyways whenever i press the button in the table to transfer the record from table2 to table3, this is the error message that i am getting -

Autos ‘R’ Us cant append all the records in the append query

Autos ‘R’ Us set 0 field(s) to null due to a type conversion failure, and it didn’t add 1 record(s) to the table due to key violations, 0 records(s) due to lock violations and 0 record(s) due to validation rule violations.

I have looked everywhere but no field has been locked and all the validation rules are the same but im not sure about the key violations. Could it be that because i have the same primary keys in all the tables. If so then i dont understand how append1 is able to transfer records from table1 to table2
0
 
Leigh PurvisDatabase DeveloperCommented:
It just reads as you've already appended a record to the target table which has the same primary key a a record you're attempting to append.  i.e. there's a crossover of records from the appends - somehow.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Leigh PurvisDatabase DeveloperCommented:
Sorry - bad choice of words. "crossover" eurgh.
"Overlay", "intersection", "co-incidence" or perhaps just simple "similarity".
0
 
deevyasAuthor Commented:
What can i do to get rid of this "Overlay"? Could it be because i have used the same variable name for both procedures even though they are for different forms and buttons.
0
 
Leigh PurvisDatabase DeveloperCommented:
No - you have a record appended to the target table already in place - that has the same Primary Key as one of the records you're attempting to append.
You need to address that (or look at your PK choices).
0
 
deevyasAuthor Commented:
Thanks for all the help but ive worked it out and now it is working perfectly. I will distribute the points evening. Thank you very much once again
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now