Function GeraBases_Por_Region()
' Author: André Bernardes
' Date: 25/07/14 - 10:30
' AppliCategoryion: *** SalesForce®
Dim nSQL As String
Dim nQuery As String
Dim i As Integer
Dim nRegion As String
Dim db_Q As QueryDef
' Cria uma tabela atualizada para cada Local.
For i = 1 To 6 'Nº de Locais.
Debug.Print i
Let nRegion = Trim(Str(i)) 'Nº de Locais
Let nSQL = "SELECT SQLServer_View_BIGDATA_Relatorio_LAB.OBSERVE_MYLAB, SQLServer_View_BIGDATA_Relatorio_LAB.Category_CT, SQLServer_View_BIGDATA_Relatorio_LAB.STATUS, SQLServer_View_BIGDATA_Relatorio_LAB.AREA, SQLServer_View_BIGDATA_Relatorio_LAB.BRAND AS Product, Left([DEPTO],2) AS Region, Left([DEPTO],3) AS DISTRICT, SQLServer_View_BIGDATA_Relatorio_LAB.DEPTO, SQLServer_View_BIGDATA_Relatorio_LAB.ID, SQLServer_View_BIGDATA_Relatorio_LAB.PROFILE, SQLServer_View_BIGDATA_Relatorio_LAB.CLIENT, SQLServer_View_BIGDATA_Relatorio_LAB.Citie, SQLServer_View_BIGDATA_Relatorio_LAB.FMonth AS PX00, SQLServer_View_BIGDATA_Relatorio_LAB.SMonth AS PX01, SQLServer_View_BIGDATA_Relatorio_LAB.TRI_00, SQLServer_View_BIGDATA_Relatorio_LAB.TRI_03, SQLServer_View_BIGDATA_Relatorio_LAB.SEMF_00, SQLServer_View_BIGDATA_Relatorio_LAB.SEMF_01, SQLServer_View_BIGDATA_Relatorio_LAB.MAT_00 " & _
"INTO tbl_tmp_B" & nRegion & "_PX " & _
"FROM SQLServer_View_BIGDATA_Relatorio_LAB " & _
"GROUP BY SQLServer_View_BIGDATA_Relatorio_LAB.OBSERVE_MYLAB, SQLServer_View_BIGDATA_Relatorio_LAB.Category_CT, SQLServer_View_BIGDATA_Relatorio_LAB.STATUS, SQLServer_View_BIGDATA_Relatorio_LAB.AREA, SQLServer_View_BIGDATA_Relatorio_LAB.BRAND, Left([DEPTO],2), Left([DEPTO],3), SQLServer_View_BIGDATA_Relatorio_LAB.DEPTO, SQLServer_View_BIGDATA_Relatorio_LAB.ID, SQLServer_View_BIGDATA_Relatorio_LAB.PROFILE, SQLServer_View_BIGDATA_Relatorio_LAB.CLIENT, SQLServer_View_BIGDATA_Relatorio_LAB.Citie, SQLServer_View_BIGDATA_Relatorio_LAB.FMonth, SQLServer_View_BIGDATA_Relatorio_LAB.SMonth, SQLServer_View_BIGDATA_Relatorio_LAB.TRI_00, SQLServer_View_BIGDATA_Relatorio_LAB.TRI_03, SQLServer_View_BIGDATA_Relatorio_LAB.SEMF_00, SQLServer_View_BIGDATA_Relatorio_LAB.SEMF_01, SQLServer_View_BIGDATA_Relatorio_LAB.MAT_00 " & _
"HAVING (((Left([DEPTO],2))='B" & nRegion & "'))"
DoCmd.SetWarnings (False)
' Cria a query.
Let nQuery = "qry_tmp_" & nRegion & "_Region" ' Nome da query
If Not IsNull(DLookup("Type", "MSYSObjects", "Name='nQuery'")) Then
' Deleta o objeto query.
DoCmd.DeleteObject acQuery, nQuery
Else
CurrentDb.CreateQueryDef nQuery, nSQL
End If
' Configura o timeout da query
Set db_Q = CurrentDb.QueryDefs(nQuery)
Let db_Q.ODBCTimeout = 0
db_Q.Close
' Executa a query
'DoCmd.RunSQL (nSQL)
DoCmd.OpenQuery nQuery, acViewNormal, acEdit
' Deleta o objeto query.
DoCmd.DeleteObject acQuery, nQuery
DoCmd.SetWarnings (True)
Next
Set db_Q = Nothing
End Function