Actualização de BD em Access a partir do Excel

jcabral

12-07-2020 16:13:28

Estou a começar a trabalhar na integração entre o Excel e o Access, mantendo o Excel como o Front-end das minhas aplicações e o Access apenas como base de dados.

Contudo surgiu-me uma duvida que não sei como resolver. Na imagem cujo link deixo aqui está resumido o fluxo de informação que tenho - https://www.sendspace.com/file/z5tlwr
Imagem1.png
- ou seja tenho 3 usuários que trabalham em três planilhas do Excel, iguais no formato mas com informação diferente e o que pretendo é verter para uma base de dados em Access a informação gerada e depois através de uma outra planilha em Excel, o meu FRONT-END, vou buscar e tratar a informação de cada usuário que está na BD do Access.

O que pretendo e não sei se é possível é que sempre que eu abrir o meu Front-end ele "abre" a BD em Access (para buscar dados) e esta base de dados actualiza-se automaticamente com os dados das três planilhas, isto é possível de fazer de uma forma automática e directamente a partir do Front-End, sem ter que ir ao Access?
É possível , pex., eu ter um botão num menu no meu Front-end que me faça a actualização dos dados da BD em Access para que quando eu fizer uma consulta a partir do Front-end os dados estejam actualizados?

Espero ter sido claro na minha duvida.
Agradecido
Jorge Cabral

NB: Tb aqui - http://www.planilhando.com.br/forum/vie ... 25&t=33607

Raygsson

12-07-2020 17:41:45

É uma demanda bem viável, porém voce deve ter algum conhecimento da linguagem SLQ para fazer a Inserção, Atualização, Exclusão e Seleção (INSERT, UPDATE, DELETE e SELECT) no banco de dados Access, comandos basicos SQL.
Deixe selecionada a biblioteca VBA "Microsoft ActiveX Data Object" na versão mais atual que possuir no seu Excel.
Vai precisar tambem conectar o Excel ao Access, pra isso segue um modelo básico da conexão com o banco access na minha area de trabalho e de uma consulta (SELECT) básica:

Sub ConsultarBD()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strCn As String
Dim SQL As String

Set cn = New ADODB.Connection
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Raygsson\Desktop\MeuBanco.accdb"
cn.Open strCn

Set rs = New ADODB.Recordset

SQL = " SELECT * FROM MinhaTabela "
rs.Open SQL, cn

Plan1.Range("C3").CopyFromRecordset rs

cn.Close

End Sub

jcabral

13-07-2020 13:11:50

Boa tarde caro Raygsson

Desde já agradeço a sua resposta, mas julgo que não consegui expressar bem qual a minha dúvida.
O meu problema não é fazer a Inserção, Actualização, Exclusão e Selecção de dados na BD do Access a partir do FRONT-END, o meu problema é actualizar de forma automática a BD em Access com os dados das três folhas de cálculos, ver o fluxo de informação que tenho, sempre que abro o FRONT-END, ou seja, como posso garantir que os dados na BD do Access estão sempre actualizados, sem ter que ir directamente ao Access e fazer manualmente essa actualização?

Obrigado
Jorge Cabral

Reinaldo

13-07-2020 18:43:24

Deprende-se que existem 4 front-end "acessando" uma base, onde 3 desses F-E "realizam" input's (porem não informa tipo/maneira/frequência...., e um "retira" informações; tambem sem informar tipo/necessidade/frequencia.
"...fazer manualmente..."
A que chama de manualmente, qual o(s) procedimento(s) / etapa(s) para atualização?
"...como posso garantir..."
Essa garantia seria do próprio Access, caso seu front-end seja também Access(.mde)
Sugestão:
Não é possível, sendo apenas 4 envolvidos "marcar" um horário para que os procedimentos seja efetuados

Raygsson

13-07-2020 22:04:17

Se voce sabe os comandos pra mexer no banco pode escrever o codigo da rotina de atualização do banco e chama com algum evento ou botão no excel. exemplo:
Private Sub Workbook_Open()
Call Atualizar_BD
End Sub
ou
Private Sub Worksheet_Activate()
Call Atualizar_BD
End Sub
ou qualquer outro evento que servir.
Fora isso tem que verificar possíveis conflitos na manipulação do banco por diversos usuarios ao mesmo tempo, nao sei muito bem como o Access lida com isso.

jcabral

13-07-2020 23:35:19

Reinaldo escreveu: ↑Seg Jul 13, 2020 3:43 pm Deprende-se que existem 4 front-end "acessando" uma base, onde 3 desses F-E "realizam" input's (porem não informa tipo/maneira/frequência...., e um "retira" informações; tambem sem informar tipo/necessidade/frequencia.
"...fazer manualmente..."
A que chama de manualmente, qual o(s) procedimento(s) / etapa(s) para atualização?
"...como posso garantir..."
Essa garantia seria do próprio Access, caso seu front-end seja também Access(.mde)
Sugestão:
Não é possível, sendo apenas 4 envolvidos "marcar" um horário para que os procedimentos seja efetuados

Caro Reinaldo
Não existem 4 Front-End, existe apenas um.
O que tenho, e vamos simplificar e dar um exemplo muito simplista, são três lojas que geram informação, pex peça vendida e custo de cada peça. Todos as três geram informação do mesmo género mas diferentes – as três folhas de Excel.
O que pretendo é que sempre que abro o Front-End que é o meu ficheiro Excel (não considero os outros três como Front-End) a Base de Dados em Access atualiza as tb1, tb2 e tb3 com os dados de cada uma das lojas (Peça Vendida e Custo Peça Vendida) e eu depois trabalho no meu Front-End essa informação, o que faço no Front-End não é importante, o importante é como posso atualizar a BD Access sem ter que abrir o ficheiro e fazer a atualização manualmente no Access – Botão Atualizar tudo.
Outra coisa que pretendia era ter um botão ao qual estará associada uma macro(?) que me atualizaria a BD Access sempre que eu quisesse, já que não é possível ter horas marcadas para fazer os updates da BD Access.
Não sei se fui claro.
Obrigado
Jorge

Reinaldo

14-07-2020 14:14:06

Como disse, se fosse tudo em Access, as infos seriam incluídas diretamente no banco, sem necessidade de qualquer atualização, e disponibilidade de dados sempre atuais.
Como e/está, o proposto pelo colega Raygsson creio ser a melhor alternativa.
Em seu front-end (ou onde desejar) deve ser montado/estruturado a rotina de atualização, que lê e separa os dados desejados e a inclui e/ou altera no banco/tabela correspondente.

jcabral

14-07-2020 17:45:01

Obrigado, pelas suas dicas, mas de facto não pode ser tudo em Access devido às limitações que existem não só em termos de cálculos como em termos de apresentação de resultados, além de que não estou muito à vontade a trabalhar em Access, apenas uso como BD.
Acresce que a solução do Raygsson não resolve a minha necessidade, já que a BD em Access apenas vai servir para agregar os dados das diferentes lojas, ou seja apenas vai existir actualizações das tabelas referentes a cada loja, e depois o Front-End apenas vai ler dados, não vai haver inserções, actualizações, modificações de dados quer a partir do Front-End quer a partir a partir de cada ficheiro das lojas.
A solução tem que ser mais simples e estou crente que existe.

Reinaldo

14-07-2020 20:10:09

Creio que está a fazer confusão.
A proposta/sugestão a que me referi

Código: Selecionar todos

Private Sub Workbook_Open()
Call Atualizar_BD
End Sub
"...BD em Access..." / "...não vai haver inserções, atualizações, modificações..."
Sozinho o DataBase não agrega nenhum dado, É preciso um estimulo, seja diretamente no Access ou via externa.
Esse estimulo deve ser considerado como uma inserção / atualização / modificação (nem todas obrigatoriamente juntas)
Porem esse estimulo externo deve ser "construido" (pois pelo que demonstra o mesmo não existe) e atribuído um nome a essa rotina (sugerido pelo colega "Atualizar_BD");
a partir da existência dessa rotina, deve ser decidido como inicia-la, foi sugerido utilizar o evento Open da planilha Front-End ("Private Sub Workbook_Open()".
Como estamos às cegas não é possível sugerir qualquer detalhe para construção dessa rotina, pois apenas tu sabes o que contem as planilhas e o DataBase, onde estão quais tabelas correspondem , ainda se uma nova planilha substitui completamente a tabela existente ou não.

jcabral

15-07-2020 09:18:18

Caro Reinaldo
Acho que agora estamos quase a falar sobre o mesmo.
Só um pequeno esclarecimento, quando referi que na BD_Access "...não vai haver inserções, actualizações, modificações...", pensei que tinha ficado claro que isto não é um modelo de cadastro, a BD_Access serve apenas para agregar dados de todas as lojas, obviamente que as tabelas têm que ser actualizadas, mas apenas isso, não vai haver mais nenhum tipo de acção sobre os dados da BD_Access, nem alterar, nem deletar, nem modificar um campo, nem editar nada, apenas actualizar cada tabela com novos dados que existam em cada ficheiro das lojas.
Agora você falou a palavra correta, "estimulo", e o meu problema é esse, quero que o estimulo para a BD_Access se actualizar seja dado a partir do Front-End, de duas formas quando abro o Front-End.....ele estimula (não sei como) a BD_Access e ela actualiza-se com os dados de cada ficheiro das lojas e quando carrego num botão qualquer (pode estar no ribbon) no Front-End.
O meu problema está precisamente em como "programar" esse estimulo.
Preparei os ficheiros com dados fictícios mas tudo preenchido à mão, se achar que pode ajudar.
Front_End.zip
Obrigado mais uma vez
Jorge

Reinaldo

15-07-2020 13:07:22

"...pensei que tinha ficado claro..."
Para mim; a partir do seu segundo post; ficou bem claro que é uma base a qual podemos chamar de "repositório de dados", e não sofrerá manipulação individual de itens, somente em lote.
Também é bastante claro que "...ser actualizadas, mas apenas isso..." , e uma manipulação dos dados existentes, seja pela sobreposição total ou parcial ou seja pela adição de novo movimento diário.
Para uma indicação da maneira de "produzir o estimulo" à atualização precisamos saber que que deve ser feito?
Seu Database contem uma tabela nomeada tblLoja1 contendo, vamos supor, o movimento do dia 13.
Hoje dia 15 há disponível uma planilha Excel nomeada"LOJA1" com, vamos supor, movimento do dia 14.
O que proceder:
Substituir inteiramente o conteúdo existente na tlbLoja1, pelo constante na planilha Loja1
Adicionar inteiramente o conteudo da planilha Loja1 na tblLoja1
Alterar alguma coluna, vamos supor: valor, se por exemplo o nome do produto, já existir e adicionar os não existentes

jcabral

17-07-2020 19:33:32

Mais uma vez obrigado

Com as duvidas colocadas tenho duas soluções :
1º - Actualizar a tblLoja1 com todos os valores que estão em LOJA1 e não estão em tblLoja1 mais a actualização dos que foram alterados;

Se não for possível a primeira solução e tendo em atenção o caso real terei de adicionar nos ficheiros LOJAi um campo "Status" que pode ter o valor "OK" ou "KO".
2º - E então o que se vai pretender é que a tabela seja actualizada com todos os valores que têm Status="OK"

No caso real julgo ser mais fácil porque cada record tem um IdTask que é único (não é um número sequencial), pelo que cada "transacção" tem um número único, ou seja pode ser o mesmo artigo e mesmo preço mas o numero é diferente, quase que posso dizer que o iDTask pode funcionar como Chave Principal.