Running Access Crosstab from Excel with split database

[MilaK]

Hello,

I have code in Excel 2016 that executes a Crosstab query from Access which works well. However, I'm concerned what would happen after I split the database.
Since all the queries will be in the front end, should I reference a read-only copy of the front end where the query is located, or not?

Here is the code:

Public Sub GetVariantData()

 Dim db As Database
 Dim qdf As QueryDef
 Dim rs As Recordset
 Dim ws As Worksheet
 Dim strConnection As String
 Dim j As Long
 Dim xls As Object    'your Excel.Application with sheet activated

 Set ws = ThisWorkbook.Worksheets("SNVs")


 Set db = OpenDatabase("C:\Users\Test\UpdataDb.accdb")
 Set qdf = db.QueryDefs("graph_variant_final_Crosstab")
 Set rs = qdf.OpenRecordset
 With ws
     For j = 0 To rs.Fields.Count - 1
         .Cells(1, j + 1).Value = rs.Fields(j).Name
     Next
     .Range("A2").CopyFromRecordset rs
 End With
 rs.Close

'clear memory
Set rs = Nothing

'Enable the screen.
MsgBox "The data has been successfully Imported", vbInformation, "Import successful"
 
 
End Sub

 

[Ranman256]

Nothing should change.
The queries are still there, the tables are still there.

 

[MilaK]

I've added 'Nz' to the query and now Excel is throwing an error "Undefined function NZ in expression". The query executes fine in Access. Do you have any suggestions how to fix this problem?

here is the query:

TRANSFORM Nz(Sum(graph_variant_final.AF))+0 AS SumOfAF
SELECT graph_variant_final.run_date
FROM graph_variant_final
GROUP BY graph_variant_final.run_date, graph_variant_final.run_name
PIVOT graph_variant_final.variant;

Thanks again

 

[Minty]

If that's in Excel you are out of luck, Excel doesn't have a Nz function. https://www.pcreview.co.uk/threads/nz-function-equivalent-in-excel.3157343/

 

[MilaK]

Is there a work around? Thanks

 

[Minty]

You could force the value to 0 in the crosstab?

 

[MilaK]

I've tried:
SumOfAF: IIf(IsNull(Sum([graph_variant_final].[AF])),"0",[graph_variant_final].[AF])
but it didn't work.

Please demonstrate how to force the value to 0 in crosstab. Thanks

 

[Minty]

Is your cross tab based on a table or a further query? If it's a query then set the missing values in that to 0 and they will pull through to the crosstab.

If not then have a read here for some other tips https://www.access-programmers.co.uk/forums/showthread.php?t=172671