This is what I've got so far:
Code:
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim DBS As New ADODB.Connection
Dim RS As ADODB.Recordset
Dim table As String
Dim Cmpteur As Integer
Dim intcom As Integer
Dim strDBPath As String = Application.StartupPath & "\Choice.mdb"
Dim strquery_DropTempTable, strQuery_CopyFieldSet1, strQuery_CopyFieldSet2, strQuery_CopyFieldSet3 As String
' Data Table
strquery_DropTempTable = "DROP TABLE tmp_Data"
strQuery_CopyFieldSet1 = "SELECT ID, LineNum, Date_Case_Open, DateClosed, Data_Entry_Date, Data_Input_Date, " & _
"Housing_Status, Section_8, SupHousing_Level, SupHousing_LivesWith, Currently_Employed, " & _
"Date_Employed, Place_Employed, Hrs_Worked_Weekly, Date_of_Prior_Employment, Place_of_Prior_Employment, " & _
"Reason_Left_Prior_Employment, Highest_Grade, SSI, SSI_Status, SSDI, " & _
"Cocaine, Cocaine_Form, Heroin, Heroin_Form, Opiate, Narcotic, Hallucinogen, " & _
"Methadone_Maintenace, Methadone_Dose, Methadone_TakeHomes, Sub_Abuse_Treatment, SubAbuse_Treatment_Why, " & _
"SubAbuse_Treatment_Where, SubAbuse_Treatment_Specifics, SubAbuse_Meds_Taken, SubAbuse_Meds_Type, " & _
"SubAbuse_Meds_Other, Anxiety, Comments, Warnings, ChildViolence, Hallucin, " & _
"DestProp, FireSet, CrimHist, JailTerm, Depression, ViolOthers, Suicidal, Homicidal, " & _
"TO1, TO3, TO4, TO5, TO7, TO8, TO10, TO11 " & _
"INTO tmp_Data FROM tblData;"
strQuery_CopyFieldSet2 = "ALTER TABLE tmp_Data ADD TO12 INT, TO13 INT, TO14 INT, TO15 INT"
strQuery_CopyFieldSet3 = "SELECT tblData.CoPayFundGrant, tblData.M_DSS_SSA_Benefits, tblData.O_DSS_SSA_Benefits, tblData.Ref_MPS, tblData.Ref_LA, " & _
"tblData.Ref_DCMH, tblData.Place_Comm, tblData.Rem_Comm, tblData.Core_Case_Man, tblData.Ref_DCMH_Case_Man, tblData.Program, tblData.HistoryDrugUse " & _
"INTO tmp_Data FROM tblData;"
' Open the database.
Call OpenAccessDBConnection(DBS, strDBPath)
Try
' Drop the temporary Data table if it exists.
DBS.Execute(strquery_DropTempTable)
Catch
' The temp Data table does not exist.
End Try
' Copy first half of the Data table.
DBS.Execute(strQuery_CopyFieldSet1)
' Add TO12 -> TO15.
DBS.Execute(strQuery_CopyFieldSet2)
' Copy second half of the Data table.
DBS.Execute(strQuery_CopyFieldSet3)
MessageBox.Show("Finished altering table tmp_Data")
Call CloseAccessDBConnection(DBS)
End Sub
What I'm doing is copying the first X fields of the tblData table to a temp table. Next inserting Y new fields which are Long Integers. Next copying the last Z fields of the tblData table to the temp table.
My problem is on the red line above, which gives me the error:
Quote:An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Test.exe
Additional information: Table 'tmp_Data' already exists.
I presume the "SELECT aaa INTO bbb FROM ccc" SQL command tries to create a new table? If so, how do I add fields from one table to the end of another including all the data?
Code:
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim DBS As New ADODB.Connection
Dim RS As ADODB.Recordset
Dim table As String
Dim Cmpteur As Integer
Dim intcom As Integer
Dim strDBPath As String = Application.StartupPath & "\Choice.mdb"
Dim strquery_DropTempTable, strQuery_CopyFieldSet1, strQuery_CopyFieldSet2, strQuery_CopyFieldSet3 As String
' Data Table
strquery_DropTempTable = "DROP TABLE tmp_Data"
strQuery_CopyFieldSet1 = "SELECT ID, LineNum, Date_Case_Open, DateClosed, Data_Entry_Date, Data_Input_Date, " & _
"Housing_Status, Section_8, SupHousing_Level, SupHousing_LivesWith, Currently_Employed, " & _
"Date_Employed, Place_Employed, Hrs_Worked_Weekly, Date_of_Prior_Employment, Place_of_Prior_Employment, " & _
"Reason_Left_Prior_Employment, Highest_Grade, SSI, SSI_Status, SSDI, " & _
"Cocaine, Cocaine_Form, Heroin, Heroin_Form, Opiate, Narcotic, Hallucinogen, " & _
"Methadone_Maintenace, Methadone_Dose, Methadone_TakeHomes, Sub_Abuse_Treatment, SubAbuse_Treatment_Why, " & _
"SubAbuse_Treatment_Where, SubAbuse_Treatment_Specifics, SubAbuse_Meds_Taken, SubAbuse_Meds_Type, " & _
"SubAbuse_Meds_Other, Anxiety, Comments, Warnings, ChildViolence, Hallucin, " & _
"DestProp, FireSet, CrimHist, JailTerm, Depression, ViolOthers, Suicidal, Homicidal, " & _
"TO1, TO3, TO4, TO5, TO7, TO8, TO10, TO11 " & _
"INTO tmp_Data FROM tblData;"
strQuery_CopyFieldSet2 = "ALTER TABLE tmp_Data ADD TO12 INT, TO13 INT, TO14 INT, TO15 INT"
strQuery_CopyFieldSet3 = "SELECT tblData.CoPayFundGrant, tblData.M_DSS_SSA_Benefits, tblData.O_DSS_SSA_Benefits, tblData.Ref_MPS, tblData.Ref_LA, " & _
"tblData.Ref_DCMH, tblData.Place_Comm, tblData.Rem_Comm, tblData.Core_Case_Man, tblData.Ref_DCMH_Case_Man, tblData.Program, tblData.HistoryDrugUse " & _
"INTO tmp_Data FROM tblData;"
' Open the database.
Call OpenAccessDBConnection(DBS, strDBPath)
Try
' Drop the temporary Data table if it exists.
DBS.Execute(strquery_DropTempTable)
Catch
' The temp Data table does not exist.
End Try
' Copy first half of the Data table.
DBS.Execute(strQuery_CopyFieldSet1)
' Add TO12 -> TO15.
DBS.Execute(strQuery_CopyFieldSet2)
' Copy second half of the Data table.
DBS.Execute(strQuery_CopyFieldSet3)
MessageBox.Show("Finished altering table tmp_Data")
Call CloseAccessDBConnection(DBS)
End Sub
What I'm doing is copying the first X fields of the tblData table to a temp table. Next inserting Y new fields which are Long Integers. Next copying the last Z fields of the tblData table to the temp table.
My problem is on the red line above, which gives me the error:
Quote:An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Test.exe
Additional information: Table 'tmp_Data' already exists.
I presume the "SELECT aaa INTO bbb FROM ccc" SQL command tries to create a new table? If so, how do I add fields from one table to the end of another including all the data?
