1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
|
Sub CreerUnetable
Dim Cn As New OleDbConnection
Dim Cmd, CmdCreertable As OleDbCommand
Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & My.Application.Info.DirectoryPath & "\MaBase.mdb"
Cn.Open()
Dim Strsql As String = "SELECT A.ChampA1, A.ChampA2, B.ChampB1," & _
" A.ChampA3, A.ChampA4, A.ChampA5, A.ChampA6," & _
" C.ChampC1, C.ChampC2, " & _
" C.ChampC3, C.ChampC4, C.ChampC5, D.ChampD1, D.ChampD2, " & _
" E.ChampE1, " & _
" F.ChampF1, G.ChampG1, " & _
" H.ChampH1, I.ChampI1 INTO Editnotes" & _
" FROM (((((((C LEFT JOIN D ON C.ChampA6 = D.ChampA6) LEFT JOIN A" & _
" ON (C.ChampA4 = A.ChampA4) AND (C.ChampA3 = A.ChampA3) " & _
" AND (C.ChampA2 = A.ChampA2) AND (C.ChampA1 = A.ChampA1) " & _
" AND (C.ChampA6 = A.ChampA6)) LEFT JOIN I " & _
" ON A.ChampA4 = I.ChampA4) LEFT JOIN H ON A.ChampA3 = H.ChampA3)" & _
" LEFT JOIN G ON A.CODEMATI = G.CODEMATI) LEFT JOIN J " & _
" ON (A.ChampA7 = J.ChampA7) AND (A.ChampA4 = J.ChampA4) " & _
" AND (A.ChampA3 = J.ChampA3)) LEFT JOIN (F LEFT JOIN E" & _
" ON F.ChampF2 = E.ChampF2) ON A.ChampA2 = F.ChampA2)" & _
" INNER JOIN B ON (C.ChampA1 = B.ChampA1) AND (C.ChampA2 = B.ChampA2)" & _
" AND (C.ChampA3 = B.ChampA3) AND (C.ChampA4 = B.ChampA4) " & _
" AND (C.ChampC8 = B.ChampC8) WHERE(((A.ChampA1) =?) and ((A.ChampA2) = ?) and ((A.ChampA3) = ?) " & _
" AND ((A.ChampA4) =?) AND ((A.ChampA5) =? ) AND ((A.ChampA6) = ?))" & _
" ORDER BY A.ChampA8, A.ChampA6, A.ChampA9;"
'chercher la table TableAcreer si elle existe on la supprime sinon on la crée
Dim table As DataTable = Cn.GetSchema("Tables")
For i As Integer = 0 To table.Rows.Count - 1
If table.Rows(i).Item("TABLE_NAME") = "TableAcreer" Then
Try
Dim sql As String = "DROP Table TableAcreer"
Cmd = New OleDbCommand
Cmd.CommandText = sql
Cmd.Connection = Cn
Cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
Finally
End Try
End If
Next
'Créer la table TableAcreer
Try
CmdCreertable = New OleDbCommand
CmdCreertable.CommandText = Strsql
CmdCreertable.Connection = Cn
CmdCreertable.Prepare()
CmdCreertable.Parameters.Add(New OleDbParameter("prm1", OleDbType.Double, 4, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ChampA1", DataRowVersion.Original, TextBox1.Text))
CmdCreertable.Parameters.Add(New OleDbParameter("prm2", OleDbType.VarWChar, 8, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ChampA2", DataRowVersion.Original, TextBox2.Text))
CmdCreertable.Parameters.Add(New OleDbParameter("prm3", OleDbType.Integer, 1, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ChampA3", DataRowVersion.Original, TextBox3.Text))
CmdCreertable.Parameters.Add(New OleDbParameter("prm4", OleDbType.VarWChar, 3, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ChampA4", DataRowVersion.Original, TextBox4.Text))
CmdCreertable.Parameters.Add(New OleDbParameter("prm5", OleDbType.Integer, 1, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ChampA5", DataRowVersion.Original, TextBox5.Text))
CmdCreertable.Parameters.Add(New OleDbParameter("prm6", OleDbType.Double, 8, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ChampA6", DataRowVersion.Original, TextBox6.Text))
CmdCreertable.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message & " _ " & Err.Number)
Finally
Cn.Close()
End Try
End Sub |
Partager