none
Access 2007 (VBA) code that queries SharePoint 2013 Lists works fine in Windows 7 but not in Windows 8 RRS feed

  • Question

  • Hi,

    My MS Access 2007 .accdb file contains VBA code for querying (on-premise) SharePoint 2013 Lists and generating reports on my Windows 7 machine.  I copied that .accdb into my colleague's Windows 8.1 machine so he could open it in his MS Access 2007, run the queries and generate the reports himself.  

    After re-linking/refreshing the SharePoint Lists of the .accdb copied to his Windows 8.1 machine, I tried to run the module to execute the queries and display a report, but then program execution stops with this error:

    Clicking "Debug" opened the built-in Visual Basic Editor and it highlighted this line of code:  

    "Set tblSource = qrySource.OpenRecordset(dbOpenSnapshot)"  

    I'm sure my VBA code is bug free and working in Windows 7.  Is there something I need to download and install (.DLL, Add-in, whatever) to make the same VBA code work in Windows 8.1?

    Thanks.

    Tuesday, July 14, 2015 9:46 AM

Answers

  • Hi Everyone,

    I found out that this was a SharePoint security issue. I logged on the Windows 8.1 with a Domain Administrator account and run the .accdb's VBA code -- And it works just like in my Windows 7 machine. I'll forward this problem to our SharePoint Server administrator/consultant for further handling.

    My thanks to all of you for sharing your thoughts.

    • Marked as answer by riceroles Thursday, July 16, 2015 5:34 AM
    Thursday, July 16, 2015 5:34 AM

All replies

  • Hi,

    I have an MS Access 2007 file (accdb) that queries SharePoint 2013 Lists to generate reports, and it works fine on my Windows 7 machine.

    I then distributed said accdb file to my colleagues so they may run the reports on their Windows 8 laptops.  I've already tried re-linking/refreshing the SharePoint Lists on their accdb copies running on Windows 8, but still program execution would stop in this line of code:  "Set tblSource = qrySource.OpenRecordset(dbOpenSnapshot)"... I've insert some screenshots for your reference:

    Hope someone could help me with this. Thanks. 


    • Merged by L.HlModerator Wednesday, July 15, 2015 8:32 AM Repeat Case
    Saturday, July 11, 2015 5:21 AM
  • Hi

    Has Access DatabaseEngine for 2007 been installed in your  Windows 8 machine?
    If not, please download and install it: here's a link for download.
    2007 Office System Driver: Data Connectivity Components

    Regards,


    • Edited by Ashidacchi Saturday, July 11, 2015 8:03 AM
    Saturday, July 11, 2015 8:02 AM
  • Thanks for the tip, Ashidacchi.

    After installing said DB engine however, a new error message ensued:


    ...Then the debugger stops at the same line of code once more. Please note that "IT Service Request Application" is a SharePoint 2013 List and SharePoint is already online. Any idea what's going on?


    • Edited by riceroles Monday, July 13, 2015 10:00 AM
    Monday, July 13, 2015 9:59 AM
  • Hi,

    Have you 
    1) added an Object Library into "Reference", and 
    2) added a line "Imports" on your code?

    1) Access DB engine Object Library in "Reference"    

    2) Imports System.Data.OleDb  (if you use OLE for using Access DB)
        

    Regards,

    • Edited by Ashidacchi Monday, July 13, 2015 10:34 AM
    Monday, July 13, 2015 10:34 AM
  • I've downloaded and installed the Access Database Engine (higher version - for Access 2007 in Windows 8?), but I can't find it under references. Do you know its default path so I could just browse for it?

    Visual Basic Editor - References

    Just a refresher: I'm using MS Office 2007 and I'm trying to run a working (in Windows 7) accdb in Windows 8.

    Thanks.

    Tuesday, July 14, 2015 2:23 AM
  • Hi riceroles,

    Firstly, I don't use any versions of SharePoint, so I can't mension about it.

    Secondly, if you want to Access 2007 DataBse(it doesn't mean Access 2007 as a part of MS-Office), you can use Access DB engine 2007 in your application by 
       1) installing "Access DB engine 2007",
       2) adding it into Referece, and
       3) importing it.
         (I've mentioned the above three points before)
       4) The version of DB enging has no relation to that of MS-Office and that of Windows.

    Now, you can't find Access DB engine Object Library to be added into Reference. Is this correct?
    If it were correct, default path would be the below:

    C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Access.Dao\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Access.Dao.dll

    cf. This is my case: Windows 8.1 with Office 2013 installed. The version may be 12.0, instead of 15.0. Sorry, I'm not certain. 

    cf. An image of "Visual Basic Editor - References" is not shown in your post.
        Please edit your post and try to insert it again. 

    Best regards,

    • Edited by Ashidacchi Tuesday, July 14, 2015 3:33 AM
    Tuesday, July 14, 2015 3:32 AM
  • Hi,

    Too bad you don't have SharePoint, which is probably/somehow at the root of my problem. The source/back-end of the accdb is SharePoint 2013 and I have no problem running it in Windows 7 with MS Access 2007.  My problem is after I copied the accdb file to Windows 8.1 with MS Access 2007, code errors occur -- Well, you know the story. 

    Also, my MS Access is part of Office 2007 Professional Suite. I'm using VBA (Visual Basic Access) through said product's built-in editor, i.e., I've used VBA code instead of macros... I don't have a separate application or Visual Studio project file.

    Sorry, I've been trying to insert the VB Editor snapshot again to no avail. Must be an internet problem.

    I'll try to install the DLL you specified on one of the target (Windows 8.1) laptops, then keep you posted on how it went.

    Arigato.


    • Edited by riceroles Tuesday, July 14, 2015 5:30 AM
    Tuesday, July 14, 2015 5:29 AM
  • Hi,

    If you did NOT use Visual Studio, sorry, please forget what I've mentioned and showed before.

    Regards,
    Tuesday, July 14, 2015 5:43 AM
  • Just off the top of my head,

    it looks like you have a Field Name that needs brackets try, [Request Date] instead of Request Date.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, July 14, 2015 3:34 PM
  • Hi,

    I had a similar problem and I solved just doing:

    In the Visual Basic Editor I went to Tools -> References unchecked all references (you better take note of them)

    then Debug Compile (gives un error)

    and finally checked all the references needed again.

    It worked...

    João

    Tuesday, July 14, 2015 5:53 PM
  • According to your description, it is possible that this issue caused by incorrect date format.  You can make simple query with [Request Date] value to check your Sql code. Otherwise we suggest that you can try to  test VBA-build SQL code, put a breakpoint at the point where the SQL is passed to OpenRecordset, print it to the immediate window and copy it into a new SQL Query to check where the Query editor complains. You would have found your errors if you had at least tried to debug the resulting VBA SQL into the Query editor.
    Wednesday, July 15, 2015 1:27 AM
  • Rechecked my code and the SQL statement does contain brackets, not only for [Request Date] but for all two-word fields as well - here's the code:

    Private Sub BuildSQL_Form()
        Dim tblSource, tblTarget As Recordset
        Dim qrySource As QueryDef
        Dim cSqlSelek As String
        Dim oStatBar As Variant
        cSqlSelek = "SELECT " & _
            "qryHeader.[Status] AS [Status], " & _
            "qryHeader.[Request Date] AS [Request Date], " & _
            "qryHeader.[Request No] AS [Request No], " & _
            "qryHeader.[Charged To] AS [Charged To], " & _
            "qryHeader.[Request Title] AS [Request Title], " & _
            "qryHeader.[Issues/Problems] AS [Issues/Problems], " & _
            "qryHeader.[Created By] AS [Created By], " & _
            "qryHeader.[Requested By] AS [Requested By], " & _
            "qryHeader.[Approved By] AS [Approved By], " & _
            "qryHeader.[Assigned By] AS [Assigned By], " & _
            "qryHeader.[Assigned To] AS [Assigned To], " & _
            "qryHeader.[Deferred By] AS [Deferred By], " & _
            "qryHeader.[Time Created] AS [Time Created], " & _
            "qryHeader.[Time Submitted] AS [Time Submitted], " & _
            "qryHeader.[Time Approved] AS [Time Approved], " & _
            "qryHeader.[Time Assigned] AS [Time Assigned], " & _
            "qryHeader.[Time Deferred] AS [Time Deferred], " & _
            "qryHeader.[Time Completed] AS [Time Completed], " & _
            "qryHeader.[Time Accepted] AS [Time Accepted], " & _
            "qryHeader.[Time Closed] AS [Time Closed], "
        cSqlSelek = cSqlSelek & _
            "qryDetail.[IT Service Tasks].[Time Started] AS [Time Started], " & _
            "qryDetail.[IT Service Tasks].[Time Finished] AS [Time Finished], " & _
            "qryDetail.[Mins Worked] AS [Mins Worked], " & _
            "qryDetail.[Hrs Worked] AS [Hrs Worked], " & _
            "qryDetail.[Service Codes].[Service Code] AS [Service Code], " & _
            "qryDetail.[Service Codes].[Service Desc] AS [Service Desc], " & _
            "qryDetail.[IT Service Tasks].[Task Description] AS [Task Description], " & _
            "qryDetail.[Attended By] AS [Attended By], " & _
            "qryDetail.[IT Service Tasks].[Issues/Findings] AS [Issues/Findings]"
        cSqlSelek = cSqlSelek & " FROM qryDetail " & _
            " RIGHT JOIN qryHeader ON qryDetail.[Request No] = qryHeader.[Request No] " & _
            " WHERE qryHeader.[Request Date] BETWEEN " & cBegTime & " AND " & cEndTime
        If b_AllUser = False Then
            cSqlSelek = cSqlSelek & " AND (Trim(qryHeader.[Created By]) = " & Chr(34) & Trim(c_OneUser) & Chr(34) & ")"
        End If
        cSqlSelek = cSqlSelek & " ORDER BY qryHeader.[Request Date], qryHeader.[Request No], qryDetail.[IT Service Tasks].[Time Started];"
        Set tblTarget = CurrentDb.OpenRecordset("tblRep02_Form")
        Set qrySource = CurrentDb.CreateQueryDef("", cSqlSelek)
        Set tblSource = qrySource.OpenRecordset(dbOpenSnapshot) ' <-- Error here
        tblSource.Requery
        If tblSource.BOF Then
            MsgBox "No records found!"
            iTotRecs = 0
        Else
            tblSource.MoveLast
            iProgBar = 0
            iTotRecs = tblSource.RecordCount
            oStatBar = SysCmd(acSysCmdInitMeter, "Generating IT Service Request Forms...", iTotRecs)
            tblSource.MoveFirst
            Do While Not tblSource.EOF
                tblTarget.AddNew
                tblTarget![Status] = tblSource![Status]
                tblTarget![Request No] = tblSource![Request No]
                tblTarget![Request Date] = tblSource![Request Date]
                tblTarget![Request Title] = tblSource![Request Title]
                tblTarget![Issues/Problems] = tblSource![Issues/Problems]
                tblTarget![Charged To] = tblSource![Charged To]
                tblTarget![Created By] = tblSource![Created By]
                tblTarget![Deferred By] = tblSource![Deferred By]
                tblTarget![Requested By] = tblSource![Requested By]
                tblTarget![Approved By] = tblSource![Approved By]
                tblTarget![Assigned By] = tblSource![Assigned By]
                tblTarget![Assigned To] = tblSource![Assigned To]
                tblTarget![Deferred By] = tblSource![Deferred By]
                tblTarget![Time Created] = tblSource![Time Created]
                tblTarget![Time Submitted] = tblSource![Time Submitted]
                tblTarget![Time Approved] = tblSource![Time Approved]
                tblTarget![Time Assigned] = tblSource![Time Assigned]
                tblTarget![Time Deferred] = tblSource![Time Deferred]
                tblTarget![Time Completed] = tblSource![Time Completed]
                tblTarget![Time Accepted] = tblSource![Time Accepted]
                tblTarget![Time Closed] = tblSource![Time Closed]
                tblTarget![Time Started] = tblSource![Time Started]
                tblTarget![Time Finished] = tblSource![Time Finished]
                tblTarget![Mins Worked] = tblSource![Mins Worked]
                tblTarget![Hrs Worked] = tblSource![Hrs Worked]
                tblTarget![Service Code] = tblSource![Service Code]
                tblTarget![Service Desc] = tblSource![Service Desc]
                tblTarget![Task Description] = tblSource![Task Description]
                tblTarget![Attended By] = tblSource![Attended By]
                tblTarget![Issues/Findings] = tblSource![Issues/Findings]
                tblTarget.Update
                tblSource.MoveNext
                iProgBar = iProgBar + 1
                oStatBar = SysCmd(acSysCmdUpdateMeter, iProgBar)
            Loop
            oStatBar = SysCmd(acSysCmdRemoveMeter)
        End If
        tblSource.Close
        tblTarget.Close
    End Sub


    Wednesday, July 15, 2015 2:56 AM
  • Thanks... Tried this method without luck (didn't work for me).
    Wednesday, July 15, 2015 5:29 AM
  • Hi riceroles,

    Based on your query, I did not find obvious wrong code. First, I suggest you comment out where statement in your cSqlSelek or remove the “qryHeader.[Request Date]” filed to make a test. If it did not work either, I suggest you query a simple query with the “OpenRecordset”. Second, I suggest you query the cSqlSelek in the Access 2007 query design view to check whether it could work correctly.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, July 15, 2015 9:34 AM
  • Hi Everyone,

    I found out that this was a SharePoint security issue. I logged on the Windows 8.1 with a Domain Administrator account and run the .accdb's VBA code -- And it works just like in my Windows 7 machine. I'll forward this problem to our SharePoint Server administrator/consultant for further handling.

    My thanks to all of you for sharing your thoughts.

    • Marked as answer by riceroles Thursday, July 16, 2015 5:34 AM
    Thursday, July 16, 2015 5:34 AM