Migrate a table in the database to another database (1 Viewer)

[azhar2006]

Hello guys .
I am migrating a table in the database to another database in partition(D) through an append query. And use a button to run this query based on the event below on click. What I want is when I run the append query through the button, a message appears saying that I have appended or updated data, and the number of records is such a record. How to do it . Thanks to all .

On Error Resume Next

    DoCmd.Hourglass True       
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "QueryAppend", acViewNormal, acEdit
    DoCmd.Hourglass False
    DoCmd.SetWarnings True

 

[theDBguy]

Have you tried it this way?

With CurrentDb
    .Execute "QueryAppend", dbFailOnError
    MsgBox "Appended " & .RecordsAffected & " records.", vbInformation, "Info"
End With

 

[azhar2006]

Have you tried it this way?

With CurrentDb
    .Execute "QueryAppend", dbFailOnError
    MsgBox "Appended " & .RecordsAffected & " records.", vbInformation, "Info"
End With

Thank you dear DBG ..yes, I have now tried it and it shows me the number of records is zero

 

[theDBguy]

Thank you dear DBG ..yes, I have now tried it and it shows me the number of records is zero

Is that correct or no? If not, try running the query from the nav pane and see how many records it's suppose to say.

 

[azhar2006]

Is that correct or no? If not, try running the query from the nav pane and see how many records it's suppose to say.

Show me this message. After pressing yes, another message appears. But when removing the password from the database, this message appeared

 

[theDBguy]

Show me this message. After pressing yes, another message appears. But when removing the password from the database, this message appeared

So, if you remove the password from the database and run the code I gave you earlier, does it say the same number of records?

 

[azhar2006]

So, if you remove the password from the database and run the code I gave you earlier, does it say the same number of records?

keeps giving zero

On Error Resume Next


DoCmd.Hourglass True
      
        DoCmd.SetWarnings False
With CurrentDb
   .Execute "QueryAppend", dbFailOnError
    MsgBox "Appended " & .RecordsAffected & " records.", vbInformation, "Info"
            
End With

    DoCmd.Hourglass False
    
        DoCmd.SetWarnings True

 

[theDBguy]

keeps giving zero

On Error Resume Next


DoCmd.Hourglass True
     
        DoCmd.SetWarnings False
With CurrentDb
   .Execute "QueryAppend", dbFailOnError
    MsgBox "Appended " & .RecordsAffected & " records.", vbInformation, "Info"
           
End With

    DoCmd.Hourglass False
   
        DoCmd.SetWarnings True

Can you post the SQL statement for QueryAppend?

 

[azhar2006]

Can you post the SQL statement for QueryAppend?

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. (Error 3022)

You tried to duplicate a value in a field that is the underlying table's primary key or an index that does not allow duplicates.

The message includes the following instruction: Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries, and try again.

 

[theDBguy]

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. (Error 3022)

You tried to duplicate a value in a field that is the underlying table's primary key or an index that does not allow duplicates.

The message includes the following instruction: Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries, and try again.

You should get that same error message whether you use code or manually run the APPEND query. Which means, you'll have to fix the query to exclude the index violations or fix the data.

 

[azhar2006]

You should get that same error message whether you use code or manually run the APPEND query. Which means, you'll have to fix the query to exclude the index violations or fix the data.

Thank you my dear friend DBG you saved me. It was the (On Error Resume Next) line that prevented me from knowing where the problem lay. Thanks a lot for the help. Your code worked well.

 

[Pat Hartman]

What I want is when I run the append query through the button, a message appears saying that I have appended or updated data, and the number of records is such a record.

You turned off Warnings so you are not getting the message you expect. I prefer the .execute method myself but try removing the commands to turn off warnings and see if your original code works.

 

[theDBguy]

Thank you my dear friend DBG you saved me. It was the (On Error Resume Next) line that prevented me from knowing where the problem lay. Thanks a lot for the help. Your code worked well.

LOL. Glad to hear you got it sorted out. Cheers!

 

[azhar2006]

LOL. Glad to hear you got it sorted out. Cheers!

Do I delete the ID number field from the query? I sent you a sentence SQL have you seen it?

 

[azhar2006]

You turned off Warnings so you are not getting the message you expect. I prefer the .execute method myself but try removing the commands to turn off warnings and see if your original code works.

Your words are always accurate. We are beginners. We go to turn off the warning messages haha and we know there's a lot of trouble behind it, a bit like that of an old car.

 

[theDBguy]

Do I delete the ID number field from the query? I sent you a sentence SQL have you seen it?

If ID is the primary key in the destination table, then check your data and make sure you're not trying to add duplicate ID values. If so, then yes, try taking them out.

 

[azhar2006]

If ID is the primary key in the destination table, then check your data and make sure you're not trying to add duplicate ID values. If so, then yes, try taking them out.

thank you my dear (theDBguy)