Need Help with Querying an Excel Table in VBA. ADO?

I have a workbook with dozens of tables that I'm currently using VBA to query and gather information and thought a SQL based query setup would be alot more efficient in gather the data I need for the worksheets. After searching I found ADO examples, is ADO the best/only solution? If so I could use a little guidance understanding some of this as it looks like ADO is used to query alot more than just excel so the examples I found differ quite a bit. I have Office 2010 Pro Plus. In the examples it looks like I need to add a reference to Microsoft ActiveX Data Objects Library. I would assume to pick the latest listed on my computer which is 6.1 but I have several 2.x versions listed. Also will this affect users that may still be running an older version of Excel.

I currently have this exampled plugged into a test sheet to see if it will at least run but fails at the rs.Open point so I figure something probably isn't correct on the Connection string info.

Code:

---

Public Function SQL() As Variant
    Dim wb As Workbook
    Dim cd As Worksheet
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strRangeAddress As String
    Dim strFile As String
    Dim strCon As String
    Dim strSQL As String
   
    Set wb = ThisWorkbook
    Set cd = wb.Sheets("ConfigurationData")
   
    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
   
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
   
    cn.Open strCon
    'strRangeAddress = Mid(ActiveWorkbook.Names.Item("cnfTableSystems").RefersToLocal, 2)
    strRangeAddress = cd.Name & cd.Range("cnfTableSystems").Address
    strSQL = "SELECT * FROM [" & strRangeAddress & "]"
   
    rs.Open strSQL, cn
   
    Debug.Print rs.GetString
    SQL = rs.GetString
End Function

---

Please give an example (in a file) which information you want to retrieve from a table.
Only when we know what you are after we can advise whether a different approach could be better.

Post the workbook to be queried, makes life much easier.

…and don't forget to tell people where you've cross posted to:
http://www.mrexcel.com/forum/excel-q...tions-ado.html

Here's a stripped down version without customer information, there a basic userform with a button to the SQL function.. Seriously nothing much on this jus trying to figure out if ADO is the right path for what I'm trying to accomplish. I'm use to SQL/Sybase in the past all this excel is a major step back in my opinion but the requirement was a single file offline based spreadsheet format. If ADO is the way to go I'm assuming there a basic misconfiguration in what I've previously posted.

What is the relationship between those 2 tables ??

What result are you after ???

What is your question ??

In the end I would love to be able to have something like
select systems.Alias, Network.ServerType, Network.Host, Network.Hostname, Network.IPAddress from cnfTableSystems Systems, cnfTableQMNetwork Network Where Systems.AssetID = Network.AssetID
but right now I'm just trying to figure out the basics and return all the data from one table just to make sure I have the connection string etc setup properly. As I said I couldn't find very much info about ADO so I'm hoping thats the best method to query the Excel Tables and also hoping it truly does give the same usage as I would use SQL command with a database.

There are several ways to do it. The first step is to get the connection string. http://www.connectionstrings.com/

Second or even first might be to set the ADO reference if you are using early binding as you did in your code.

Once you get those two steps done, a search of this forum or general search will find many ways to do it. I have not worked with tables like that so I am not which approach is best.

QueryTables can be used.

Like your example, something from this might help. Of course there are several like that here on this forum as well.
'romperstomper, http://www.excelforum.com/excel-prog...-an-array.html

As a function:

Code:

---

'http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23640071.html
Public Sub RunSQLQuery( _
      ByVal SourceWorkbookPath As String, _
      ByVal SQLCommand As String, _
      ByRef Destination As Variant, _
      Optional TablesHaveHeaders As Boolean = True, _
      Optional WriteHeader As Boolean _
  )


' Run SQL command against one or more tables in a workbook and write the
' resulting recordset to the range starting at the destination specified.
'
' Syntax
'
' RunSQLQuery(SourceWorkbookPath, SQLCommand, Destination, [WriteHeader])
'
' SourceWorkbookPath - The full path to the workbook in which the source tables
'  reside. The workbook must have been previously saved to disk (although the
'  most current version does not need to be saved to get the most recent
'  data). To get the full path for an open workbook using the workbook object
'  use:
'      SourceWorkbookPath.Path & "\" & SourceWorkbookPath.Name
'  The source workbook can be open or closed.
'
' SQLCommand - Any valid SQL command. Reference tables either by cell
'  reference:
'      SELECT * FROM [Sheet1$A1:C100]
'  or by range name:
'      SELECT * FROM [Table1]
'  Field names are determined by the values in the first row of each table. To
'  reference a field name use square brackets around the name:
'      SELECT T1.[FieldName] FROM [Table1] AS T1
'
' Destination - Any valid range or a variant variable. If a range is passed
'  then the resulting recordset is copied to the cells starting at the top
'  left cell in Destination. As many cells are used as are needed. If an empty
'  array is passed (UBound = -1) then the query result is returned as an
'  array. Otherwise the resulting recordset is returned in Destination.
'
' TablesHaveHeaders - Pass True if the source tables have a header row. Pass
'  False if not. Optional. If omitted then True is assumed. If the source
'  tables do not have headers then the columns are named "F1", "F2", etc.
'
' WriteHeader - Pass True to write a header row, False to not write the
'  headers. Optional. If omitted then False is assumed.


  Dim RecordSet As Object ' ADODB.RecordSet
  Dim ConnectionString As String
  Dim Column As Long
  Dim Row As Long
  Dim ReturnArray As Boolean
  Dim RecordCount As Long
 
  Set RecordSet = CreateObject("ADODB.RecordSet") ' New ADODB.RecordSet
 
  If IsArray(Destination) Then
      If UBound(Destination) = -1 Then ReturnArray = True
  End If
 
  ' Open the ADODB connection and run the query
  ConnectionString = _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & SourceWorkbookPath & ";" & _
      "Extended Properties='Excel 8.0;HDR=" & IIf(TablesHaveHeaders, "Yes", "No") & "'"
  RecordSet.Open SQLCommand, ConnectionString, 2, 1, 1 ' adOpenDynamic, adLockReadOnly, adCmdText
 
  ' Handle the results according to what was passed in Destination
  If TypeName(Destination) = "Range" Then
      ' Copy recordset to range
      Application.ScreenUpdating = False
      If WriteHeader Then
        For Column = 1 To RecordSet.Fields.Count
            Destination.Cells(1, Column).Value = RecordSet.Fields(Column - 1).Name
        Next Column
        Destination.Cells(1, 1).Resize(1, RecordSet.Fields.Count).Font.Bold = True
        Set Destination = Destination.Offset(1)
      End If
      Destination.CopyFromRecordset RecordSet
      Application.ScreenUpdating = True
      RecordSet.Close
  ElseIf ReturnArray Then
      ' Return an array
      RecordSet.MoveFirst
      Do While Not RecordSet.EOF
        RecordCount = RecordCount + 1
        RecordSet.MoveNext
      Loop
      ReDim Destination(1 To RecordCount + IIf(WriteHeader, 1, 0), 1 To RecordSet.Fields.Count)
      Row = 1
      If WriteHeader Then
        For Column = 1 To RecordSet.Fields.Count
            Destination(Row, Column) = RecordSet.Fields(Column - 1).Name
        Next Column
        Row = Row + 1
      End If
      RecordSet.MoveFirst
      Do While Not RecordSet.EOF
        For Column = 1 To RecordSet.Fields.Count
            Destination(Row, Column) = RecordSet.Fields(Column - 1).Value
        Next Column
        Row = Row + 1
        RecordSet.MoveNext
      Loop
      RecordSet.Close
  Else
      ' Return recordset
      Set Destination = RecordSet
  End If


End Sub

---

Sometimes something simple works:

Code:

---

'northwolves, http://www.mrexcel.com/forum/showthread.php?t=371840
Sub Test()
  Dim sql As String
  With CreateObject("ADODB.connection")
  .Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;imex=1;hdr=no;';Data Source=" & ThisWorkbook.FullName
  sql = "SELECT f1,f3,f5,f8 FROM [sheet1$a2:h65536] where f1 in (select f1 from [sheet2$a2:a65536])"
  Sheet3.[a1].CopyFromRecordset .Execute(sql)
  .Close
  End With
End Sub

---

You need to append "$" to the sheet name and remove the "$" from the range address:

Code:

---

    strRangeAddress = cd.Name & "$" & cd.Range("cnfTableSystems").Address(0, 0)

---

Also note that you can't use GetString twice in succession without using rs.MoveFirst in between.

Hey guys I really appreciate the help. I tried both of those methods and the longer version seems to be less error prone to what I've thrown at it. Had to add a piece for Null value handling but for the basics its working good. Now I'm having problems trying to pull data across two tables. Here's the code where I'm passing the info to the procedure.

Code:

---

Dim sTable1 As String
    Dim sTable2 As String
    Dim arResults() As String
    ReDim arResults(-1 To -1)
   
    sTable1 = cd.Name & "$" & cd.ListObjects("cnfTableUIPNetwork").Range.Address(Fale, False)
    sTable2 = cd.Name & "$" & cd.ListObjects("cnfTableSystems").Range.Address(Fale, False)
 
    Call RunSQLQuery(ThisWorkbook.Name, "Select NW.[AssetID], S.[Alias], NW.[Role] from [" & sTable1 & "] As NW, [" & sTable2 & "] As S Where NW.[AssetID] = S.[AssetID]", arResults, True, False)

---

Heres what the SQL Portion looks once it gets to the procedure.

Code:

---

"Select NW.[AssetID], S.[Alias], NW.[Role] from [ConfigurationData$N20:X76] As NW, [ConfigurationData$B20:F27] As S Where NW.[AssetID] = S.[AssetID]"

---

I'm guessing this is just a basic syntax in the SQL command, maybe with how ADO handles the where clause between the tables. If you need another workbook thrown together let me know.

Thanks,
Bruce

You might at least say what the problem is... ;)
I think you should remove the 'As' from the table aliasing.

Quote:

---

Originally Posted by Aflatoon

You might at least say what the problem is... ;)
I think you should remove the 'As' from the table aliasing.

---

The problem is I'm new to ADO and it's not liking the SQL query when I try to add a 2nd table. No As? The examples I've seen had that in there. The error I get with or without the AS is 'Cannot Update. Database or Object is Read-Only'

Please try:

Code:

---

"Select NW.[AssetID], S.[Alias], NW.[Role] from [ConfigurationData$N20:X76] NW, [ConfigurationData$B20:F27] S Where NW.[AssetID] = S.[AssetID]"

---

Quote:

---

Originally Posted by Aflatoon

Please try:

Code:

---

"Select NW.[AssetID], S.[Alias], NW.[Role] from [ConfigurationData$N20:X76] NW, [ConfigurationData$B20:F27] S Where NW.[AssetID] = S.[AssetID]"

---

---

Same error. Here's the SQL after the conversion to make sure it matches.

Code:

---

"Select NW.[AssetID], S.[Alias], NW.[Role] from [ConfigurationData$N20:X76] NW, [ConfigurationData$B20:F27] S Where NW.[AssetID] = S.[AssetID]"

---

It looks like now for some reason I cant even do a basic 'Select * from' on a single table without getting the 'Cannot Update. Database or Object is Read-Only' error. It's failing at the following line trying to Open the RecordSet.

Code:

---

RecordSet.Open SQLCommand, ConnectionString, 2, 1, 1 ' adOpenDynamic, adLockReadOnly, adCmdText

---

Looks like closing and reopening has fixed that problem. Somehow it was locking the tables. So after running it again and getting results from a single table I get a 'Type Mismatch in expression'.
After some trial and error making sure I can properly pull the fields I'm trying to get properly it looks like the part it doesn't like is the WHERE selection. If I take that out it runs and of course returns inproper data but with no errors.

Ok I think I've found the problem. I need to go back through all my code it looks like some of the AssetID's are getting written as a number and some are writing then as text. I put together two test tables with only text and was able to query across those with no issues just as I should. Thanks everybody for the help this is going to make things alot easier once I fix these few issues.