By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,092 Members | 1,546 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,092 IT Pros & Developers. It's quick & easy.

Last SQL Update query is lost when table is copied to new database

P: 1
I am using adodb in python to connect to a database and run sql queries.
I run several update queries in a table.
Then I do a 'Select Into' to copy the table to a new database.
All of the updates work and the table is successfully copied into the new database.
However, the last update query that is run is absent from the table that is copied into the new database.

Is there some kind of flush statement that must be used to finalize the updates before I copy the table.
Note: If I close and reopen my connection to the database before I copy the table, the update does get copied to the final table.

The following is a portion of my code. In this example the update to the Family field in the CurrentInventory table does not get copied into the MasterInventory table.

Expand|Select|Wrap|Line Numbers
  1. #Connect to the species and temp databases
  2. connectionspec=win32com.client.Dispatch('ADODB.Connection')
  3. connectionspec.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+speciesdb+".mdb")
  5. connectiontemp=win32com.client.Dispatch('ADODB.Connection')
  6. connectiontemp.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+temporarydb)
  8. #Updates the species information in the master table.
  9. makemastertable = '''UPDATE CurrentInventory INNER JOIN (FamilyTable INNER JOIN GenusTable ON FamilyTable.FamilyID = GenusTable.FamilyID)
  10. ON CurrentInventory.GenusCode = GenusTable.GenusCode SET CurrentInventory.Family = [FamilyTable].[Family]'''
  11. self.connectionspec.Execute(makemastertable)
  13. #Copy the CurrentInventory table from the species #database to the temp database and call it #MasterInventory
  14. movemaster = "SELECT * INTO MasterInventory FROM " + self.speciesdb + ".CurrentInventory"
  15. self.connectiontemp.Execute(movemaster)
Apr 6 '11 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.