Hi,
Wondering if someone can help. I am trying to provide some data to an Outsourced team we work with. Due to company restrictions the team only has 'Read-Only' access to the MS Access Application. I could do with giving them the functionality to export the results of a query. The initial query updates the results to be viewed on a form in the first instance (which works fine & has been tested), but would then like the team to be able to use a command button to trigger an export (which doesn't work).
Currently I'm creating a table and then trying to use the DoCmd.transferspreadheet but (I think) due to the 'Read-only' status one of these actions isn't able to be performed (I'm guessing its the create table part).
They receive the error message: "Execution of this application has stopped due to a run-time error. The application can't continue and will be shut down"
The export code I am using is:
Firstly: Can anyone confirm that the issue is likely to be due to Read Only access?
Secondly: If so is there any way around this, to still be able to export the data required?
Wondering if someone can help. I am trying to provide some data to an Outsourced team we work with. Due to company restrictions the team only has 'Read-Only' access to the MS Access Application. I could do with giving them the functionality to export the results of a query. The initial query updates the results to be viewed on a form in the first instance (which works fine & has been tested), but would then like the team to be able to use a command button to trigger an export (which doesn't work).
Currently I'm creating a table and then trying to use the DoCmd.transferspreadheet but (I think) due to the 'Read-only' status one of these actions isn't able to be performed (I'm guessing its the create table part).
They receive the error message: "Execution of this application has stopped due to a run-time error. The application can't continue and will be shut down"
The export code I am using is:
Code:
Private Sub Cmd_Export_Click()
Dim mymsg As String
Dim ExportMPAN As String
Dim strSQL As String
mymsg = MsgBox("This will send the results above to Excel, are you sure you want to continue?", vbYesNo, "Export Results")
If mymsg = No Then
Exit Sub
Else
DoCmd.SetWarnings False
ExportMPAN = [Forms]![MainForm_2]![TxtMPAN]
strSQL = "SELECT Data INTO tbl_ExportMPAN" & ExportMPAN & " FROM MITREAD_HIST_2 WHERE (((MITREAD_HIST_2.MPAN) =" & ExportMPAN & ")) ORDER BY MITREAD_HIST_2.[Read Date and Time], MITREAD_HIST_2.[Meter Reg ID];"
DoCmd.RunSQL strSQL
DoCmd.TransferSpreadsheet acExport, , "tbl_ExportMPAN" & ExportMPAN, "[URL="file://\\Location"]\\Location[/URL]"& ExportMPAN & ".xlsx"
DoCmd.DeleteObject acTable, "tbl_ExportMPAN" & ExportMPAN
End If
End Sub
Firstly: Can anyone confirm that the issue is likely to be due to Read Only access?
Secondly: If so is there any way around this, to still be able to export the data required?