• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

MS Access SQL INSERT doesn't write to table!

Greeting to All Experts,

I have a series of forms.  When a user creates record on one subform and click a button a sQL insert statefires without error.  However, when I look at the table, I don't see the record inserted.  When I open the next form (which is not a subform) which relies on the record the form query returns null, of course.

Does Access SQL require a commit statement?  Do I need to refresh? requery?

I'm really very frustrated with this and hope one of you can help me.
  • 5
  • 2
1 Solution
Prisoner362670Author Commented:
Also, if I navigate on and off the next form (the one I'm trying to open to pointing to the new record) eventually the record writes to the table and I open to the new record.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
My first thought is that you will need to requery the form's record source after the insert.


Open in new window

If that doesn't fix the issue then please post the code you are using to insert the record.
Prisoner362670Author Commented:
If IsNull(DLookup(strFieldName, strTableName, strLinkCriteria)) Then
        ' record not found code
        Dim strSQL As String
        'if the record does not exist create the record
        strSQL = "INSERT INTO " & strTableName & " (" & strFieldName & ") VALUES (" & strFieldContent & ");"
        DoCmd.RunSQL strSQL
        Me.Requery 'this requeries the coursesite form
        'then open the form to the new record
        DoCmd.OpenForm strFormName, , , strLinkCriteria

        'record found code
        'since the record already exists, open the form to the existing record
        DoCmd.OpenForm strFormName, , , strLinkCriteria
    End If

If I check the table after the insert (which runs without error) the new record does not appear.  when next form opens it opens to a record without the identifiers.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
That looks like standard code that should would without issue.

It could be an table index issue with inserting new records. To clean up the indexed you need to regularly backup aun the the compact function.  Have you compacted the database with the tables lately?  If not, make a backup and then run the compact function.

If compacting does not fix the issue then:

Turn on the system warnings and confirmations.  When you run the code you will get confirmation messages about the append query and if any records were inserted.

If you are still having issues then it would also help to know:

1) What version of Access you are running?
2) is this a linked table?
3) If linked, is the back end an Access database?
Prisoner362670Author Commented:
The database compacts on close.
Yesterday I also tracked the variables--they all seem fine.
Access version 2003 with SP3 on XP.
Not linked tables.

I will try running with the user warnings on within the hour.
Prisoner362670Author Commented:
This was an issue of timing.  

When the warnings for the INSERT into tbl2 was removed it indeed revealed that the insert was not performed because it would cause key violations.  The key violation was not from a duplicate identifier in tbl2 it was because the record had created an identifier in tbl1 and a record had to exist in tbl1 before tbl2 (referrential integrity).

There was also a second issue.  In the code I pass the linkcriteria yet on the second form I set the form query to look at a textbox containing the linking information on the first form using Forms!etc.  When I removed this criteria, form2 opened perfectly pointing to the correct records.

Kudos to the HiTechCoach!
Prisoner362670Author Commented:
Constant application of basics makes for efficient troubleshooting!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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