[VBA - EXCEL MACRO] - ACCESS E PARAMETROS

FSOFELIPE 19/11/2015 19:28:45
#454177
Boa tarde pessoal,
Seguinte tenho um projeto de faculdade no qual eu tenho um banco de dados no Access com varias tabelas e queries.
Em uma query eu utilizo uma passagem de parametros (fornecida pelo usuario) como filtro.
Agora preciso criar uma conexao com um excel file que aceite o usuario informar esse parametro.
Até esse ponto eu tenho um codigo que executa as queries retorna na minha tabela no excel:
  Sub ReadAccessUsingSQLCommand(SQLQuery As String)
[ô]   Dimension (Define) Variables
    Dim DatabaseName As String               [ô] This string will store the name of an MS Access Database File
    Dim DatabasePath As String               [ô] This string will be set to the path of this current Excel Workbook and Assumes the Database ACCDB file is in the same folder
    Dim intColIndex As Integer               [ô] Will be used to track which column of the Recordset is currently active as we parse data
    Dim intRowIndex As Integer               [ô] Will be used to track which row of the Recordset is currently active as we parse data
    
[ô]   Dimension (Define) Data Object Variables for Use in Reading the MS Access Tables and Queries
    Dim DB_Connection As ADODB.Connection    [ô] This is an Active Data Objects [Ô]Object[Ô] which provides the connection information and structure to link to any Database from Excel
    Dim DB_Recordset As ADODB.Recordset      [ô] This is an Active Data Objects [Ô]Object[Ô] which provides a repository of rows and columns with navigation resembling the MS Access DataSheet view

[ô]   Establish a Query - We could do this using an Input Box or Hard Coded
[ô]   Here is it hard coded after (for testing) but we included an Input Box for [Ô]Real[Ô] Use
    
[ô]   Point the Database Connection to the Correct Path (Folder Location) and File Name
    DatabasePath = Application.ActiveWorkbook.Path
    DatabaseName = DatabasePath & [Ô]\OSF2395_ITM2500_FL15_A03_Northwinds.accdb[Ô]
    
[ô]   View the Query Entered and Database Location
   [ô] MsgBox SQLQuery & vbCrLf & DatabaseName

[ô]   Open the Database
    Set DB_Connection = New ADODB.Connection
    DB_Connection.Open [Ô]Provider=Microsoft.ACE.OLEDB.12.0; Data Source=[Ô] & DatabaseName & [Ô];[Ô]
[ô]   Open The Recordset
[ô]        SQL Query Contains the Commands
[ô]        DB_Connection is the Database Against Which The Commands Will Run
[ô]        AdCmdText is a Reserve Word Which Indicates that SQLQuery Contains an SQL Based Actual Query (Not A Table Name, etc)
    Set DB_Recordset = New ADODB.Recordset
    DB_Recordset.Open SQLQuery, DB_Connection, adCmdTable
    
[ô]   Establish the position of the cursor in the first cell of the worksheet (not strictly necessary, but should be done)
    Range([Ô]A1[Ô]).Select
    
[ô]   As of the .Open Statement Above We Have Filled the Recordset With Data ** And With MetaData ** Such as Column Names, Data Types, etc., Which Can Be Accessed Via the Recordset
    

    
    For intColIndex = 0 To DB_Recordset.Fields.Count - 1
        ActiveCell.Offset(0, intColIndex).Value = DB_Recordset.Fields(intColIndex).Name
    Next
    
    DB_Recordset.MoveFirst
    
    intRowIndex = 1
    
    Do While Not DB_Recordset.EOF
        For intColIndex = 0 To DB_Recordset.Fields.Count - 1
        
            ActiveCell.Offset(intRowIndex, intColIndex).Value = DB_Recordset.Fields(intColIndex).Value
        
        Next
        intRowIndex = intRowIndex + 1
        DB_Recordset.MoveNext
        
    Loop
End Sub


Mas com esse codigo não é possivel passar o parametro.

Alguem tem algum exemplo pra isso?

Grato
DS2T 19/11/2015 20:28:36
#454185
Não entendi bem a sua pergunta sobre os parâmetros.
Se você quer limitar sua pesquisa através dos parâmetros colocados, basta você acrescentá-los a sua variável de Query.
FSOFELIPE 19/11/2015 20:34:40
#454187
Citação:

:
Não entendi bem a sua pergunta sobre os parâmetros.
Se você quer limitar sua pesquisa através dos parâmetros colocados, basta você acrescentá-los a sua variável de Query.



Na real o que eu quero é importar os dados de uma tabela do access e os dados dessa tabela sao obtidos por essa query 
  SELECT C_Q5.OSF_SupplierID, C_Q5.SupplierName, C_Q5.CategoryName, Sum(C_Q5.GrossSales) AS TotalSales
FROM C_Q5
GROUP BY C_Q5.OSF_SupplierID, C_Q5.SupplierName, C_Q5.CategoryName;


Só que o parametro é passado para dentro dessa query quando ele executa 
  C_Q5.OSF_SupplierID


Entao a partir dela nao tem exatamente um lugar fixo para colocar a variavel

Quando eu executo ela pelo access uma janela de input é acionada (http://puu.sh/lrBXr/23c3a8ee8f.png)

queria fazer o mesmo pelo excel
DS2T 19/11/2015 20:49:57
#454188
Vamos lá.

Primeiro:

SELECT C_Q5.OSF_SupplierID, C_Q5.SupplierName, C_Q5.CategoryName, Sum(C_Q5.GrossSales) AS TotalSales
FROM C_Q5
GROUP BY C_Q5.OSF_SupplierID, C_Q5.SupplierName, C_Q5.CategoryName;


Essa query aí não está filtrando nada. Você só está mudando o valor das colunas e deixando fixo e pegando o valor total das vendas da tabela toda. Acredito que você queira pegar só o valor das vendas dos parametros que você assinalar, não?

Para isso, você precisará de um WHERE.
Exemplo:

SELECT minhaTabela.IDFornecedor, minhaTabela.NomeFornecedor, Sum(minhaTabela.VendasBrutas) AS VendasTotais
FROM minhaTabela
Where minhaTabela.IdFornecedor=ParametroIdFornecedor
GROUP BY minhaTabela.IDFornecedor, minhaTabela.NomeFornecedor

Aí nesse caso abriria o dialogo só para inserir o valor do ParametroIdFornecedor. Você colocaria e ele já te daria o nome do fornecedor, assim como o total de vendas.


Mas isso foi resposta bônus... vamos para o seu problema:

Esse lance de abrir inputbox quando não reconhece o nome do campo e entende como parâmetro é coisa da IDE do Access. Se você tentar fazer isso numa conexão ADODB, vai levar um erro na cara dizendo que o campo não existe.
Isso significa que você terá que criar sua Query de forma dinâmica.

Exemplo:

Dim parametro as string 
dim suaQuery as String

parametro = inputbox([Ô]Coloca aí o valor do parâmetro[Ô])
suaQuery = [Ô]SELECT * FROM tabela Where Campo1=[ô][Ô] & parametro & [Ô][ô][Ô]



Abraços!
FSOFELIPE 19/11/2015 21:03:18
#454189
Talvez eu nao tenho me expressado corretamente, mas a minha query esta correta.
ela realmente nao usa um parametro, mas ela utiliza outras queries e essas utilizam um parametro.
Segue aqui abaixo a query que necessita o parametro:
  SELECT OSF2395_Supplier.OSF_SupplierID, OSF2395_Supplier.CompanyName AS SupplierName
FROM OSF2395_Supplier
WHERE (((OSF2395_Supplier.OSF_SupplierID)=[supplierCode]));


E infelizmente nao posso utilizar ela diretamente no excel :/
DS2T 19/11/2015 21:11:39
#454190
Como eu falei, só o Access dá essa mamata de entender como parâmetro os campos que não encontra no banco de dados.
Além do mais, esse tipo de problema é muito comum. E se você quisesse consultar dados com parametros de combobox ou um textbox? é a mesma coisa... Só montar a query na munheca.

Faça assim:

dim query as string 
query = [Ô]SELECT OSF2395_Supplier.OSF_SupplierID, OSF2395_Supplier.CompanyName AS SupplierName
FROM OSF2395_Supplier
WHERE (((OSF2395_Supplier.OSF_SupplierID)=[supplierCode]));[Ô]

dim code as string
code = inputbox([Ô]Digite o código do fornecedor[Ô])

query = replace(query, [Ô][supplierCode][Ô],code)
msgbox query


Não testei, mas a lógica é essa... você modificar a query em tempo de execução...
FSOFELIPE 19/11/2015 21:11:52
#454191
Query 1:
SELECT OSF2395_Supplier.OSF_SupplierID, OSF2395_Supplier.CompanyName AS SupplierName
FROM OSF2395_Supplier
WHERE (((OSF2395_Supplier.OSF_SupplierID)=[supplierCode]));


Query 2:
SELECT C_Q1.SupplierName, C_Q1.OSF_SupplierID, OSF2395_Product.OSF_ProductID, OSF2395_Product.CategoryID
FROM C_Q1 INNER JOIN OSF2395_Product ON C_Q1.OSF_SupplierID = OSF2395_Product.SupplierID;


Query 3:
SELECT C_Q2.OSF_SupplierID, C_Q2.SupplierName, OSF2395_Category.CategoryName, C_Q2.OSF_ProductID
FROM OSF2395_Category INNER JOIN C_Q2 ON OSF2395_Category.OSF_CategoryID = C_Q2.CategoryID
ORDER BY OSF2395_Category.CategoryName;


Query 4: 
SELECT OSF2395_Supplier.OSF_SupplierID, OSF2395_Order_Detail.OSF_OrderID, OSF2395_Product.OSF_ProductID, [OSF2395_Order_Detail].[UnitPrice]*[OSF2395_Order_Detail].[Quantity] AS GrossSales
FROM (OSF2395_Supplier INNER JOIN OSF2395_Product ON OSF2395_Supplier.OSF_SupplierID = OSF2395_Product.SupplierID) INNER JOIN OSF2395_Order_Detail ON OSF2395_Product.OSF_ProductID = OSF2395_Order_Detail.OSF_ProductID;


Query 5:
SELECT C_Q3.OSF_SupplierID, C_Q3.SupplierName, C_Q3.CategoryName, C_Q4.GrossSales
FROM C_Q4 INNER JOIN C_Q3 ON C_Q4.OSF_SupplierID = C_Q3.OSF_SupplierID
ORDER BY C_Q3.CategoryName;


Obs. 1: So posso utilizar a query 5
Obs. 2: As queries estao assim feias e mal formatadas porque foram criadas no design mode do access, infelizmente era o método que o professor ensinou para a turma e mandou utilizar.

Grato
Tópico encerrado , respostas não são mais permitidas
Voltar