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
|
With CreateObject("Adodb.connection")
.Open "Provider=SQLOLEDB;Data Source=serveur;Initial Catalog=base;User ID=toto;Password=;"
Req = "SET IDENTITY_INSERT FCT_BASE ON "
Req = Req & "INSERT INTO [dbo].[FCT_BASE] "
Req = Req & "(N_Fct_Base, Nom_Fct_Base, Parent, Date_Creation, Num_Fct_Base, "
Req = Req & "Ref_Constructeur, Prix_Vente_Franc, Prix_Vente_Euro, Descriptif, User_Create, "
Req = Req & "Date_Create, Unite, Total_Heure, Pourcent_MO, Prix_Horaire_Moyen_Franc, "
Req = Req & "Prix_Horaire_Moyen_Euro, Total_HT_Heure_Euro, Total_HT_Heure_Franc, "
Req = Req & "Total_HT_Produit_Euro, Total_HT_Produit_Franc, Total_HT_Frais_Euro, "
Req = Req & "Total_HT_Frais_Franc, Total_HT_General_Euro, Total_HT_General_Franc) "
Req = Req & "SELECT "
Req = Req & "A.N_Fct_Base, LEFT(A.Nom_Fct_Base,50), A.Parent, CONVERT(DATE, GETDATE(), 103), "
Req = Req & "A.Num_Fct_Base, A.Ref_Constructeur, A.Prix_Vente_Euro, A.Prix_Vente_Euro, "
Req = Req & "A.Descriptif, A.User_Create, GETDATE(), A.Unite, A.Total_Heure, A.Pourcent_MO, "
Req = Req & "A.Prix_Horaire_Moyen_Euro, A.Prix_Horaire_Moyen_Euro, A.Total_HT_Heure_Euro, "
Req = Req & "A.Total_HT_Heure_Euro, A.Total_HT_Produit_Euro, A.Total_HT_Produit_Euro, 0, 0, "
Req = Req & "A.Total_HT_General_Euro, A.Total_HT_General_Euro "
Req = Req & "FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 8.0;Database=" & Path & ";"
Req = Req & "', 'SELECT * FROM [FCT_BASE$]') AS A "
Req = Req & "SET IDENTITY_INSERT FCT_BASE OFF "
.Execute (Req)
.Close
End With
Application.Wait Now + TimeValue("0:00:02") 'Tentative désespérée pour essayer de faire passer la requête
With CreateObject("Adodb.connection")
.Open "Provider=SQLOLEDB;Data Source=serveur;Initial Catalog=basededonnee;User ID=toto;Password=;"
Req = "INSERT INTO LFCT ("
Req = Req & "Man, DESIGNATION, Quantite, Prix_U_Franc, Prix_U_Euro, Prix_Total_Franc, Prix_Total_Euro, N_Fct_Base, N_Produit, "
Req = Req & "N_Rubrique, Prix_U_Remise_Franc, Prix_U_Remise_Euro, Reference, N_Position, Unite, Texte) "
Req = Req & "SELECT A.Man ,LEFT(A.DESIGNATION,50) ,A.Quantite ,A.Prix_U_Franc, A.Prix_U_Euro, A.Prix_Total_Franc, "
Req = Req & "A.Prix_Total_Euro, A.N_Fct_Base, A.N_Produit, A.N_Rubrique, A.Prix_U_Remise_Franc, A.Prix_U_Remise_Euro, "
Req = Req & "A.Reference, A.N_Position, A.Unite, A.Texte "
Req = Req & "FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 8.0;Database=" & Path & ";"
Req = Req & "', 'SELECT * FROM [LFCT$]') AS A "
.Execute (Req) 'Message d'erreur à ce niveau là
.Close
End With |
