Questo è il codice che c'è dentro il modulo.
Come faccio a farlo partire senza aprire access?
Option Compare Database
Sub TD_DATI_DWH_VE()
DoCmd.SetWarnings False
Dim DWH_Conn As String
Dim dbAccessPath As String
Dim SqlAccess As String
Dim dbDWH As New ADODB.Connection
Set cmd = New ADODB.Command
Dim dbAccess As Database
Dim rs1 As ADODB.Recordset
Dim rs2 As Recordset
' Stringa connesione sql DWH
DWH_Conn = "Driver={SQL Server};Server=192.168.1.225;Database=db_DWH_STD;Uid=access;Pwd=h;"
' Apertura connessione sql DWH
dbDWH.Open DWH_Conn
cmd.ActiveConnection = dbDWH
cmd.CommandTimeout = 0
'__________________________________________________________________________________________________________
' Apertura data base access VD_AC
dbAccessPath = Application.CurrentProject.Path & "\VD_AC.accdb"
Set dbAccess = OpenDatabase(dbAccessPath)
'__________________________________________________________________________________________________________
' TRASFERISCO LE VENDITE ANNO IN CORSO DA F_Vendite
SqlAccess = "DELETE VD_AC.* FROM VD_AC;"
dbAccess.Execute SqlAccess
Set rs2 = dbAccess.OpenRecordset("VD_AC", dbOpenTable)
'Carico tabella VD_AC da DWH
cmd.CommandText = "SELECT F_Vendite.ID_Azienda, F_Vendite.cdCompetenzaVendita, F_Vendite.cdMagazzino, F_Vendite.cdVenditore, " & _
"F_Vendite.cdAgente, F_Vendite.cdSubAgente, F_Vendite.cdCliente, F_Vendite.cdTipoVendita, F_Vendite.cdCausaleMagazzino, " & _
"F_Vendite.NrFattura, F_Vendite.NrBolla, F_Vendite.ID_DataRiferimento AS DTR, F_Vendite.ID_DataBolla AS DTB, " & _
"F_Vendite.cdArticolo, F_Vendite.Qta, F_Vendite.Valore , F_Vendite.PVLN " & _
"FROM F_Vendite WHERE F_Vendite.ID_DataRiferimento>=20170101 and F_Vendite.ID_DataRiferimento<= 20171231 AND (F_Vendite.ID_TipoVendita=11 OR F_Vendite.ID_TipoVendita=12);"
Set rs1 = cmd.Execute
Set rs2 = dbAccess.OpenRecordset("VD_AC", dbOpenTable)
Do Until rs1.EOF
rs2.AddNew
For Each f In rs2.Fields
rs2.Fields(f.Name) = rs1.Fields(f.Name).Value
Next
rs2.Update
rs1.MoveNext
Loop
'Rilascio gli oggetti RecordSet
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
DoCmd.SetWarnings True
DoCmd.Hourglass False
'chiudo VD_AC
dbAccess.Close
DoCmd.SetWarnings True
DoCmd.Hourglass False
MsgBox ("Trasferimento dati completata")
End Sub
Allegati:
22011_41f7e8880c49e5cf5139830878448e27.png