Connecting Excel to Access Query, not working with Nz()

[Stormin]

Hello,

I am trying to connect an Excel 2010 workbook to an Access 2010 database (2007 type) query to use as a PivotTable source, using Data > Connections > Add... . I can establish a connection to the db itself but I noticed that a few queries do not show up in the table/query selector wizard, one of those being my target query.

I have done the following:
• Created a connection to a working query and then changed the command text to point to the target query. Set as PivotTable source. Error: "The query did not run, or the database table could not be opened." then "Problems obtaining data."
• Copied the target query's SQL as the command text. Set as PT source. Error: "Undefined function 'Nz' in expression." then "Problems obtaining data."
• Created a connection to a similar-sized working query that uses many tables and queries, including linked tables and union queries. Set as PT source, working. In Access, changed one of the fields from "[Field1]" to "Nz([Field1])". Refreshed PT, same error as first bullet point.
• Created a copy of the target query, removed all instances of "Nz" in its SQL, and created the connection in Excel. Set as PT source, confirmed working.

So it looks like Excel doesn't like the Nz() function, however this plays an important role in my target query. Given the complexity of the query, it will take several hours of work to avoid using Nz() but maintain the same level of data integrity/validation.

I am posting this here with the the following purposes:

1) to check if anyone knows of a workaround to this issue before I invest the hours to redesign the query and all those that contribute to it
2) to provide the findings above in the hope that it will help somebody Googling the same problem at some point in the future (hello from the past! ) because I sure couldn't find anything at all.

Going forward I will bear this issue in mind and probably avoid using Nz(), as nice as the function is, since I use both Excel and Access in equal amounts and I'm sure there'll be many more overlaps in my work in the future.

So for the meantime... any ideas on how to get around Excel not recognising the Nz() function when connecting to an Access query?

Cheers

 

[CJ_London]

it is a problem if using ms query - you will need to change query to use iif/is null functions. See these links

http://ewbi.blogs.com/develops/2007/11/why-no-nz-via-j.html
http://windowssecrets.com/forums/showthread.php/55084-Invalid-NZ-function-(Excel-Access-VBA-2k)

not tried, but because nz is an access application function, in vba you could instantiate an access application object and run some code to open a recordset and then populate your spreadsheet.

some suggested links

http://stackoverflow.com/questions/...-ms-access-table-from-ms-excel-2010-using-vba

http://www.learnexcelmacro.com/2011/12/how-to-connect-to-access-database-excel-macro/

 

[CJ_London]

sorry those links are for adodb, not the access application. This is a quick mockup for some code to put behind a button, whatever. Change the bits in red as required

Public Sub importFromAccess()
Dim accessApp As Object
Dim rs As Object
Dim db As Object
Dim i As Long
Dim j As Integer
Set accessApp = CreateObject("access.application")
accessApp.opencurrentdatabase ("[COLOR=red]C:\pathtodb.accdb[/COLOR]")
Set rs = accessApp.currentdb.openrecordset("[COLOR=red]myQuery[/COLOR]")
i = 2
While Not rs.EOF
    For j = 0 To rs.Fields.Count - 1
        Cells(i, j + 1) = rs.Fields(j)
    Next j
    i = i + 1
    rs.movenext
Wend
Set rs = Nothing
Set db = Nothing
Set accessApp = Nothing
End Sub

You'll need additional code to clear out previous results, set ranges etc

 

[Stormin]

Thanks CJ. That's a nice little bit of code, however the reason for the ODBC as a direct PivotTable source is to avoid storing the mass of data in the Excel file.

I bit the bullet and replaced all instances of "Nz(..." with "IIF(IsNull(..." as per your suggestion. It took some time since the final query was a huge culmination of many other queries, not all instances were used in the same fashion, and the output is critical so some scrunitising data validation. However, with all Nz() functions removed I have successfully connected my PT to the query result.

As I mentioned previously the lesson I take from this is to prefer the more compatible "IIF(IsNull(),,)" function over the Access-specific (albeit really neat) "Nz()" function.

Thanks for your help!