Can't get Insert Into to work 1

[FancyPrairie]

I'm trying to archive some data. Data in 3 live tables need to be copied to 3 archive tables. After the data has been copied the data in each of the 3 live tables will be deleted. I want to wrap all 6 SQL statements within a transaction. I can't get the following to work. If I copy and paste the SQL statement into a query and run the query, it works fine. However, if I execute the SQL statement or the query via code, I get an error. What am I doing wrong?

Example 1:
(Error message: You cannot add or change a record because a related record is required in Table abc) Note that there is no relationship with table abc and, if I copy and paste the SQL statement into a new query and run the query, it works fine)

    Dim cnn As ADODB.Connection
    Dim strSQL As String

    strSQL = "INSERT INTO tblPatient_copyto_Admission  "
    strSQL = strSQL & "SELECT lngAccount, lngMRN, dteAdmit, dteDischarge, strService, strPatientLoc, strRoomBed, ysnIsolation, ysnOptOut, dtmAdded, dtmModified, strModifiedBy, ArchivedDateTime() AS dtmArchived "
    strSQL = strSQL & "FROM tblPatient_copyfrom_Admission "
    strSQL = strSQL & "WHERE ((Not (tblPatient_copyfrom_Admission.dteDischarge) Is Null)) AND (tblPatient_copyfrom_Admission.dteDischarge < #1/1/2005#);"
    strSQL = Replace(strSQL, "copyto", "Archive")
    strSQL = Replace(strSQL, "copyfrom", "Live")

    Set cnn = CurrentProject.Connection
    cnn.BeginTrans
    cnn.Execute strSQL

Example 2 (not sure how to set transaction here...doesn't work as written anyway):

    Dim strSQL As String
    Dim cmd As ADODB.Command

    strSQL = "INSERT INTO tblPatient_copyto_Admission  "
    strSQL = strSQL & "SELECT lngAccount, lngMRN, dteAdmit, dteDischarge, strService, strPatientLoc, strRoomBed, ysnIsolation, ysnOptOut, dtmAdded, dtmModified, strModifiedBy, ArchivedDateTime() AS dtmArchived "
    strSQL = strSQL & "FROM tblPatient_copyfrom_Admission "
    strSQL = strSQL & "WHERE ((Not (tblPatient_copyfrom_Admission.dteDischarge) Is Null)) AND (tblPatient_copyfrom_Admission.dteDischarge < #1/1/2005#);"
    strSQL = Replace(strSQL, "copyto", "Archive")
    strSQL = Replace(strSQL, "copyfrom", "Live")
    
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText
    cmd.CommandText = strSQL
    cmd.Execute

Example 3 (works but can't emcompass all 6 in one Transaction):

    Dim strSQL As String

    strSQL = "INSERT INTO tblPatient_copyto_Admission  "
    strSQL = strSQL & "SELECT lngAccount, lngMRN, dteAdmit, dteDischarge, strService, strPatientLoc, strRoomBed, ysnIsolation, ysnOptOut, dtmAdded, dtmModified, strModifiedBy, ArchivedDateTime() AS dtmArchived "
    strSQL = strSQL & "FROM tblPatient_copyfrom_Admission "
    strSQL = strSQL & "WHERE ((Not (tblPatient_copyfrom_Admission.dteDischarge) Is Null)) AND (tblPatient_copyfrom_Admission.dteDischarge < #1/1/2005#);"
    strSQL = Replace(strSQL, "copyto", "Archive")
    strSQL = Replace(strSQL, "copyfrom", "Live")

    DoCmd.RunSQL strSQL, True

 

[Trevil]

I was able to run your first set of code without a problem. I did replace "ArchivedDateTime()" with "Now()" as I don't know what that routine may actually be producing (yes, I understand it is SUPPOSED to be a date).

Why don't you do the same and see if the first part works?

Also just add a "Cnn.CommitTrans" after the execute.

Let me know what happens...

"Have a great day today and a better day tomorrow!

 

[FancyPrairie]

Discovered the problem Friday morning. I thought I had deleted a relationship between 2 tables, but, in my haste, I must not have saved it. So now it works in code. Still doesn't make sense that it worked when I ran it as a query. But, anyway, it's working. Thanks Trevil for taking the time to verify that it worked as is. I'll give you a star for your time and effort.