Big problem in Linking Excel to queries in ACCESS containing VBA function

Dear All, I am reading Access 2010 VBA programming inside out. in the chapter 13 the authors mentions a potential issue about linking Excel to queries in access containing VBA functions. I tried to link excel to a query in access using MS query but it dind't work as the access query contains a VBA function. I was wondering if there is a way to overcome this issue, unfortunately the books doesn't provide any solution to this. Thank you very much for your help. Diego
1 person has
this question
+1
Reply
  • Hi Deigo,
    Yes on page 470 I show an example where when attempting to link to an Access query containing the Nz function you get an error and indicate that it is an unsupported function.
    A workaround for this problem would be to read the data from Excel using program code and transfer the results directly into the spreadsheet.
    If you make a copy of the sample spreadsheet ExcelOpeningAccess.xlsm, and change the code behind the button to the following then this will demonstrate the technique.
    Sub StartAccess_Click()
    Dim appAccess As Access.Application
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim strDatabase As String
    Dim lngRowIndex As Long
    Dim lngColumnIndex As Long
    Dim lngStartColumnIndex As Long
    lngStartColumnIndex = 1
    lngColumnIndex = lngStartColumnIndex
    lngRowIndex = 10
    strDatabase = ActiveWorkbook.Path & "\ExcelAnalysis.accdb"
    Set appAccess = GetObject(strDatabase)
    Set db = appAccess.CurrentDb
    Set rst = db.OpenRecordset("qryProductSalesAllProductsUsingNZ", dbOpenDynaset)
    If Not rst.EOF Then
    For Each fld In rst.Fields
    Sheet1.Cells(lngRowIndex, lngColumnIndex) = fld.Name
    lngColumnIndex = lngColumnIndex + 1
    Next
    lngRowIndex = lngRowIndex + 1
    End If
    Do While Not rst.EOF
    lngColumnIndex = lngStartColumnIndex
    For Each fld In rst.Fields
    'Debug.Print fld.Value
    Sheet1.Cells(lngRowIndex, lngColumnIndex) = fld.Value
    lngColumnIndex = lngColumnIndex + 1
    Next
    lngRowIndex = lngRowIndex + 1
    rst.MoveNext
    Loop
    Stop
    appAccess.Quit
    End Sub
    You would also need some code to clear out the area of the spread sheet before writing the data.

    Regards

    Andrew Couch
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated