Executing a query against Access and Excel

Executing a query against Access and Excel

• pinoyz

  
  So are you getting an error I'm not sure what the problem is.
  
  

• Dietz

  I just don't know how to reference tables from each dataset in a sql statement. For example:

  "select <access_table>.my_code as projeto where <excel_table>.installation = <access_table>.client_code"

  Getting info from Excel spreasheets is ok, but how can I reference Access schema

  Yours,

  Flavia

  
  
  

• raj.ramesh

  That's exactly it. The question is how to reference both sources.

  Thanks,

  Flavia

  
  
  

• daxu

  
  What version of Visual Studio are you using Perhaps you could post the code you have so far

  
  
  

• Flydsp

  Hi,

  Flavia Lemes wrote:

  How can I use this against both Yours, Flavia

  Do you mean on each of your connections Well, if that's the case just change the connection object on the second paramter of the OleDbCommand constructor (the code that I posted earlier). Change it to its supposed connections object. IF you want to query against your Access Dbase then use the conneciton for your access do the same with your excel conneciton.

  cheers,

  Paul June A. Domag

  
  
  

• Todd W. Price

  Hi,

  Ok, sorry for the misunderstanding. You meant to have a query statement that uses two different data sources (connections). Sad to say but this is not possible. A more logical way is to manually query your excel table and loop through it and query your access table and store the querried result in a datatable..

  cheers,

  Paul June A. Domag

  
  
  

• Athan

  
  Well actually it is possible to include both Access and Excel in the same (heterogeneous) query although it still isn't clear to me what needs to be accomplished. Are you trying to provide a join between the data from each data store I think we just a need little more information as to how Access and Excel are being used together.

  OK, I'm editing my message here after looking at your SQL statement. Based upon what I see are you querying the Access table for all rows (my_code) where the "client_code" column of a row has a match in the "installation" column in the Excel Worksheet of a Workbook

  
  
  

• fagster

  
  Create your connection to the Access database and try the following:

  SELECT AccessTableName.my_code FROM AccessTableName WHERE my_code IN (SELECT Installation as my_code FROM [Excel 8.0;DATABASE=C:\Test Files\ExcelWB.xls;HDR=Yes].[ExcelSheetName$])

  
  
  

• Keith27

  Hi,

  You can execute queries on these datasources by using a Command object. Since these 2 sources are using OleDb then you would use the OleDbCommand object. Just use your connection on your datasources for your command to be directed to its proper datasource. Ex:

  OleDbCommand myComm = new OleDbCommand("SELECT * FROM AccessTable", connAccess);

  You can then use the execute method if your query returns a result and ExecuteNonQuery if your query doesn't return any result set...

  cheers,

  Paul June A. Domag

  
  
  

• HillBillyB

  ' Connecting to the Worksheet

  lblTarefaAtual.Text = "Connectando a planilha..."

  xlsConn = data.xlsConnect(arqOrigem)

  ' Executing query

  lblTarefaAtual.Text = "Executando consulta..."

  pbConverter.PerformStep()

  strQry = "select installation as projeto from [" & _

  utils.GetExcelSchema(tbOrigem.Text).Tables(0).TableName.ToString & "$] "

  PlanAdpt = New OleDbDataAdapter(strQry, xlsConn)

  PlanAdpt.Fill(Plandt)

  bsConsulta.DataSource = Plandt

  dgvConsulta.DataSource = bsConsulta

  
  
  

• ChandraP

  How can I use this against both

  Yours,

  Flavia

  
  
  
• Executing a query against Access and Excel