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.
Thanks,
Alexis
-
#Connect to the species and temp databases
-
connectionspec=win32com.client.Dispatch('ADODB.Connection')
-
connectionspec.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+speciesdb+".mdb")
-
-
connectiontemp=win32com.client.Dispatch('ADODB.Connection')
-
connectiontemp.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+temporarydb)
-
-
#Updates the species information in the master table.
-
makemastertable = '''UPDATE CurrentInventory INNER JOIN (FamilyTable INNER JOIN GenusTable ON FamilyTable.FamilyID = GenusTable.FamilyID)
-
ON CurrentInventory.GenusCode = GenusTable.GenusCode SET CurrentInventory.Family = [FamilyTable].[Family]'''
-
self.connectionspec.Execute(makemastertable)
-
-
#Copy the CurrentInventory table from the species #database to the temp database and call it #MasterInventory
-
movemaster = "SELECT * INTO MasterInventory FROM " + self.speciesdb + ".CurrentInventory"
-
self.connectiontemp.Execute(movemaster)
-
-
