The form closes my table even after docmd.close

Question

Sorry for the shorthand guys, but this requires an explanation, too much code to publish ...

I am importing fixed-width files to access methods that require data entry. I import the file using transferText into two specifications (one global, the other a special circumstance).

I have a function that uses DAO to cycle through all Field objects in TableDefs to create a duplicate table, including AutoIncrement PK, so I have the ability to edit these records. I am inserting data into this table using INSERT INTO.

It works great. Errors are detected, the user goes into data entry to manually correct them, which is superior to sifting through 400 character lines and reorganizing everything as it should be. It works great!

Problem : when changing data entry, the commit button is pressed, which calls a function inside the module outside the form. It closes the data entry form and returns information back to the original table minus auto-increment PK and SUPPOSED DROP for the replicated table with identifiers and generates a new search again for errors ...

It completely discards the source text, but it will not be a DROP ID table. He always comes back to me with a message that this table is locked. I noticed that the table is closed until all functions / subtitles exit. At any time, while sifting through the code, I cannot delete it manually, as soon as the execution is completed, I can delete it.

I assume that since I called it through a command in the form, that the lock will not be released until all the code has finished and the end of the form has been called and does its job. Any suggestions? Yes, this is very barbaric, but it works very well, I just need to remove this other table from the planet so that I can update the updated copy ...

, . , .

-Edit -

FormA (Role: Load in and search for problems)

Examine button is pressed that:

 - Uses TextTransfer based on predefined specs into tempExtract to
       import the file

 - DAO fires off on the Fields collection in tableDefs for
   tempExtract, creates new table tempExtractID

 - Performs searches through the file to find errors.  Errors are saved to
   a table Problem_t.  Table contains Problem_ID (Set from the ID field
   added to tempExtractID) and Description

 - Execution of these tasks is successfully requerying the initial
   form to showing a list of problems and number of occurances.  A button
   gains visibility, with onClick that opens the form DataEntry.            

 - At this point in the code after DAO execution, I can DROP the table
   tempExtractID.  DAO is NOT used again and was only used to build a new table.

FormB -

, tempExtractID , . tempExtractID Problem_t, , .

, . , 5 , , .

*Xargs refers to the list of Field names pulled earlier through DAO.  As DAO loops through Field objects, the physical names are added to an Xargs String which is placed in this table.  Basically everything but the AutoNumber is being inserted back

    docmd.Close acForm, "frmDataEntry", acSaveNo
    call reInitializeExtract
         > docmd.RunSQL "DELETE FROM tempExtract"
         > docmd.RunSQL "INSERT INTO tempExtract SELECT (" & DLookup("Value", "CONFIG_t", "Item = 'Xargs'" & ") FROM tempExtractID"
    docmd.DeleteObject acTable, "tempExtractID"

, ( ) , .

Answer 1

vbNullString . , .., .

Answer 2

, DAO, . Nothing Nothing .

  Dim db As DAO.Database
  Dim rs As DAO.Recordset

  Set db = DBEngine.OpenDatabase("[path to database]")
  Set rs = db.OpenRecordset("[SELECT statement]")
  rs.Close
  Set rs = Nothing
  db.Execute("[DML or DDL statement]", dbFailOnError)
  db.Close
  Set db = Nothing

  Set db =CurrentDB
  Set rs = db.OpenRecordset("[SELECT statement]")
  rs.Close
  Set rs = Nothing
  Set db = Nothing  ' you don't close a db variable initialized with CurrentDB

VBA , , 100% ( VBA , , , ).

, , , , , .

, , DoCmd.RunSQL:

DoCmd.RunSQL, , . , , , . DAO , , DoCmd.RunSQL, , . DML DDL , . , 100 , 10 , DoCmd.RunSQL 90 10 ​​. DML/DDL. DoCmd.RunSQL "" , , .

, , , , , , PK CPU , , .

.

, , DoCmd.RunSQL DAO Execute . SO ( ), , , :

  Public Function SQLRun(strSQL As String, Optional db As Database, _
       Optional lngRecordsAffected As Long) As Long
  On Error GoTo errHandler
    Dim bolCleanup As Boolean

    If db Is Nothing Then
       Set db = CurrentDb
       bolCleanup = True
    End If
    'DBEngine.Workspaces(0).BeginTrans
    db.Execute strSQL, dbFailOnError
    lngRecordsAffected = db.RecordsAffected
    'DBEngine.Workspaces(0).CommitTrans

  exitRoutine:
    If bolCleanup Then
       Set db = Nothing
    End If
    SQLRun = lngRecordsAffected
    'Debug.Print strSQL
    Exit Function

  errHandler:
    MsgBox "There was an error executing your SQL string: " _
       & vbCrLf & vbCrLf & Err.Number & ": " & Err.Description, _
       vbExclamation, "Error in SQLRun()"
    Debug.Print "SQL Error: " & strSQL
    'DBEngine.Workspaces(0).Rollback
    Resume exitRoutine
  End Function

( , , )

:

  DoCmd.RunSQL "DELETE FROM tempExtract"
  DoCmd.RunSQL "INSERT INTO tempExtract SELECT (" _
    & DLookup("Value", "CONFIG_t", "Item = 'Xargs'" & ") FROM tempExtractID"

... :

  SQLRun "DELETE FROM tempExtract"
  SQLRun "INSERT INTO tempExtract SELECT (" _
    & DLookup("Value", "CONFIG_t", "Item = 'Xargs'" & ") FROM tempExtractID"

:

  Debug.Print SQLRun("DELETE FROM tempExtract") & " records deleted."
  Debug.Print SQLRun("INSERT INTO tempExtract SELECT (" _
    & DLookup("Value", "CONFIG_t", "Item = 'Xargs'" _
    & ") FROM tempExtractID") & " records inserted."

.RecordsAffected Execute, Immediate, :

  Dim lngRecordsAffected As Long
  ...
  Call SQLRun("DELETE FROM tempExtract", , lngRecordsAffected)
  Debug.Print lngRecordsAffected & " records deleted."
  Call SQLRun("INSERT INTO tempExtract SELECT (" _
    & DLookup("Value", "CONFIG_t", "Item = 'Xargs'" _
    & ") FROM tempExtractID", , lngRecordsAffected)
  Debug.Print lngRecordsAffected & " records inserted."

, Execute , ( - , -1 MsgBox).

, , , . , CurrentDB(), , , db, Nothing. , LDB .