UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Append Query Not Working!, Access 2016    
 
   
ciapul12
post Feb 18 2020, 04:56 AM
Post#1



Posts: 309
Joined: 7-June 14



Hi There,
I have a couple of append queries that are linked to main database located on networked drive.
These two queries are triggered by command button but I've noticed that records are not being copied into main database each time... It only works 1 in 3 or 5 cases...

My code:
CODE
Private Sub Command32_Click()



    'DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryAppendResponseSession"
    DoCmd.OpenQuery "qryAppendResponses"
    DoCmd.RunSQL "DELETE FROM ResponseSessions"
    DoCmd.RunSQL "DELETE FROM Responses"

    'DoCmd.SetWarnings True

    
    
    MsgBox "AwesomeJob! Thanks for submitting your Audit"
    DoCmd.Close acForm, Me.Name, acSaveNo
    DoCmd.OpenForm "frmOpen"
End Sub


Would appreciate if someone could give me some hints.

Thanks
Dan
Go to the top of the page
 
cheekybuddha
post Feb 18 2020, 06:08 AM
Post#2


UtterAccess Moderator
Posts: 12,621
Joined: 6-December 03
From: Telegraph Hill


Hi Dan,

Not much we can offer without seeing the SQL of the queries in question.

Also, how are you accessing the db on the network drive, or is this code from within it?

--------------------


Regards,

David Marten
Go to the top of the page
 
ciapul12
post Feb 18 2020, 08:32 AM
Post#3



Posts: 309
Joined: 7-June 14



Hi David,
Please see my SQL below:
CODE
INSERT INTO ResponseSessions ( ResponseSessionID, ResponseSessionDate, RespondentID, QuestionnaireID, [Audit Area], Shift, Site, [Frequency?] ) IN 'C:\Users\dress\Test GMP\GMP System_be.accdb'
SELECT ResponseSessions.ResponseSessionID, ResponseSessions.ResponseSessionDate, ResponseSessions.RespondentID, ResponseSessions.QuestionnaireID, ResponseSessions.[Audit Area], ResponseSessions.Shift, ResponseSessions.Site, ResponseSessions.[Frequency?]
FROM ResponseSessions;


My main database has a ResponseSessionID PK as auto number, could that be causing problems? I'm appending all fields including ResponseSessionID

Attached File(s)
Attached File  Capture.JPG ( 48.41K )Number of downloads: 4
 
Go to the top of the page
 
Jeff B.
post Feb 18 2020, 08:37 AM
Post#4


UtterAccess VIP
Posts: 10,409
Joined: 30-April 10
From: Pacific NorthWet


What is your BE db engine (e.g., Access, SQL-Server, Oracle, …)?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
projecttoday
post Feb 18 2020, 08:57 AM
Post#5


UtterAccess VIP
Posts: 11,811
Joined: 10-February 04
From: South Charleston, WV


Do the users ever click on the button twice? If you try to enter the same value into a unique-index field more than once you will get that error. Which fields are set to unique?

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Feb 18 2020, 08:58 AM
Post#6


UtterAccess Moderator
Posts: 12,621
Joined: 6-December 03
From: Telegraph Hill


It certainly looks like it - the INSERT fails due to key violations.

Do you ever read back from the network database? I'm asking because we need to know whether the keys from GMP System_be.accd need to be preserved in your local version

Also, why do it this way? Why not just link against the network BE?

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ciapul12
post Feb 18 2020, 11:37 AM
Post#7



Posts: 309
Joined: 7-June 14



Hi All,
Thanks for all suggestions!
The reason for that database not being linked directly to main DB is because I want users to use tablets and there are parts of site where wifi isn't available or very slow which affects the performance of GMP app.
The idea is to use it as stand alone system and once back in the office, the user can click on command button to append results into main DB.
It works in some instances but fails most of the time... Not sure how to overcome this
My DB engine is MS access
Users may click button twice but that can be limited with code, I've clicked on button once and it failed to append records..

Dan
Go to the top of the page
 
projecttoday
post Feb 18 2020, 11:43 AM
Post#8


UtterAccess VIP
Posts: 11,811
Joined: 10-February 04
From: South Charleston, WV


In any case you can't put the same value into a unique field more than once.

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Feb 18 2020, 11:45 AM
Post#9


UA Admin
Posts: 36,988
Joined: 20-June 02
From: Newcastle, WA


"The idea is to use it as stand alone system and once back in the office, the user can click on command button to append results into main DB.
It works in some instances but fails most of the time... Not sure how to overcome this"

Ah. That's not going to work, as you've found.

I'm betting that you are, indeed, getting duplicate Primary/Foreign Key values because each user is creating their own records independently of the others. When the FIRST person attempts to append their new records, it should work because there are no duplicates yet in the "master" accdb. However, when the next person up tries to append their new records, those records now have duplicates of the existing primary/foreign key values just appended by the first user.

I think you need to reconsider this approach. Perhaps it can be accomplished with a lot more coding to append and merge those records that would otherwise duplicate values. Big task.

Look into alternatives.
This post has been edited by GroverParkGeorge: Feb 18 2020, 11:46 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
BruceM
post Feb 18 2020, 01:04 PM
Post#10


UtterAccess VIP
Posts: 8,053
Joined: 24-May 10
From: Downeast Maine


It appears there is a main table and another table with child records. In that case it does become more complex. If you append to the main table only, without related records, I think you could just leave out the autonumber field from the INSERT SQL. However, if that value is used as the linking field (foreign key) in a related table it is not simple. One option may be to create your own primary key/ foreign key rather than using autonumber, and do it in such a way that each user has a distinct version of the number. I can think of some variations on that approach, but nothing especially simple.
Go to the top of the page
 
ciapul12
post Feb 19 2020, 05:37 AM
Post#11



Posts: 309
Joined: 7-June 14



The problem is that I'm testing this with a single user for now and it still don't work...
I've checked main db before appending records and last PK was 241 whereas my offline db ID had a PK 242 so in theory there was no key violation but it still failed to append records..

Not sure what's causing this, it drives me mad :-)

Thanks
Dan
Go to the top of the page
 
projecttoday
post Feb 19 2020, 06:46 AM
Post#12


UtterAccess VIP
Posts: 11,811
Joined: 10-February 04
From: South Charleston, WV


It could be another field.

Your PK are not autonumbers, correct?

--------------------
Robert Crouser
Go to the top of the page
 
ciapul12
post Feb 19 2020, 07:09 AM
Post#13



Posts: 309
Joined: 7-June 14



Hi
PK is auto-number, see below image:


Attached File(s)
Attached File  Capture1.JPG ( 41.4K )Number of downloads: 3
 
Go to the top of the page
 
projecttoday
post Feb 19 2020, 07:48 AM
Post#14


UtterAccess VIP
Posts: 11,811
Joined: 10-February 04
From: South Charleston, WV


Is the field in the destination table an autonumber? Since Access assigns autonumbers for you it doesn't seem to me that you can say here's an autonumber of 242 even if there isn't an existing 242. That's as far as I know, anyway. But I don't know that for certain. Could you do a simple mock-up test on that? Or do you know that you can do that? I know you said it works part of the time.

As George said, syncing up databases is a challenge because of the keys.

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Feb 20 2020, 10:26 AM
Post#15


UtterAccess VIP
Posts: 8,053
Joined: 24-May 10
From: Downeast Maine


Robert, I'm quite sure you are correct. It is possible to reset the autonumber to start from a specific value, but I don't know why that would be done, nor does it seem to apply here. SQL Server has an option to enable IDENTITY insert, but I don't think there is any such Access option.
Go to the top of the page
 
projecttoday
post Feb 20 2020, 12:07 PM
Post#16


UtterAccess VIP
Posts: 11,811
Joined: 10-February 04
From: South Charleston, WV


Okay, Bruce. That error message could be from some other field (column) in the table although there still could be a problem with the handling of the autonumbers as well.

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Feb 21 2020, 07:31 AM
Post#17


UtterAccess VIP
Posts: 8,053
Joined: 24-May 10
From: Downeast Maine


Well, that could be. I suppose a test INSERT query that omits the autonumber field could narrow it down.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th April 2020 - 05:29 PM