SQL statement for ADODB and Access

Question

I am basically clueless when it comes to Access and Excel however, I have managed to cobble together some code that will return a filtered ADODB record set from an Excel file.

What has me stumped now is how to use Access for the same purpose.  I started by opening my Excel.xlsx file using Access and creating a database file.  I now have a Taxonomy.accdb file that contains a table named "tblTaxonomy."  When I try to retrieve the data using the Access file I keep getting error messages.  The current message is:

" -2147217904 No value given for one or more required parameters" and occurs on this line of my code:

fcnQuery_GetList.Open strCompositeQuery, oConnection, adOpenStatic

The value of strCompositeQuery that is passed is:   Select * from tblTaxonomy where PARENT_ID = 'A' Order by 4

I feel pretty sure the problem is something simple in the "Select * From ...."  string or my strConnection.  I have tried many variations and just keep getting various errors.

Could someone assist with the correct Select * From ... statemen and strConnection statement?  I would also like to have the correct statements for using a legacy .mdb file.  Thank you.

Sub Test()

Dim oRS As ADODB.RecordSet

Dim strQuery As String

'From Excel file.

strQuery = "Select * from [Taxonomy$]"

'From Access file.

'strQuery = "Select * from tblTaxonomy"

Set oRS = fcnQuery_GetList(strQuery, "4", "PARENT_ID", "A")

MsgBox oRS.RecordCount

End Sub

'************************************************************************************************

Function fcnQuery_GetList(strQuery As String, strSortField As String, strFilterField As String, _

                          strFilterValue As String) As ADODB.RecordSet

Dim strConnect As String

Dim strCompositeQuery

  'Initialize objects.

  Set fcnQuery_GetList = CreateObject("ADODB.Recordset")

  Set oConnection = CreateObject("ADODB.Connection")

  'Establish connection.

  'This will work with my Excel file.

  'strConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & m_Directory & "Taxonomy.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"""

  'This errors

  strConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & m_Directory & "Taxonomy.accdb;Persist Security Info=False"

  oConnection.ConnectionString = strConnect

  oConnection.Open

  'Using the filter parameters passed - generate a where clause appropriate to the filter value presented.

  strCompositeQuery = strQuery & " where " & strFilterField & " = '" & strFilterValue & "'"

  'If a sort field specified, add an order by clause.

  If strSortField <> "" Then strCompositeQuery = strCompositeQuery & " Order by " & strSortField

  'Get the data.

  fcnQuery_GetList.Open strCompositeQuery, oConnection, adOpenStatic

lbl_Exit:

  Exit Function

End Function

Answer 1

HansV,

I think I am getting close.  When I opened the Excel file in Access (being ignorant of what was happening) I think what I have now is a read only table linked to the the Excel file.  The first record is the heading from my Excel file Key, UIC, PARENT_ID, LIST_VALUE. 

I just forced strCompositeQuery to:

Select * from tblTaxonomy where F3 = 'TOP' Order by 4

as "F3" is the column heading in Access and the code ran and did return some results.

So I think I just need to figure out how to unlink and unlock the table, delete the first record and rename the Access table columns.  If you know how to do that then please let me know.

Thanks.

Answer 2

EUREKA!!!

That was the problem.  Works fine now.  I just need to see if I can do something similar with a .mdb type file.

By the way the whole purpose of this exericise is to streamline a process for linking dependent userform listboxes.  It is working for up to 7 levels (that is as far as I went).  If you are interested I will send you the files.

Answer 3

The SQL string looks correct. Are you sure that Parent_ID is a text field?

Answer 4

HansV,

That was a typo. Sorry. It doesn't work with the strQuery line for Access uncommented.

I have made some progress.  Modifying the strCompositeQuery just the strQuery that is passed:

strCompositeQuerry = strQuery

or further modifying to use the sort

strCompositeQuery = strQuery & " Order by " & strSortField

The code runs without error and returns all 38 records.  So the issue must be related to:

strCompositeQuery = strQuery & " where " & strFilterField & " = '" & strFilterValue & "'"

I have versfied that my table column heading is in fact "PARENT_ID" so I don't know what might be wrong.

Thanks.

Answer 5

For Access, you must comment out the line

strQuery = "Select * from [Taxonomy$]"

and uncomment the line

strQuery = "Select * from tblTaxonomy"