MS Access 2003 "Read-Only" transferspreadsheet export question

[gmazza76]

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:

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?

 

[xenou]

I would test it locally where you know you have proper permissions. If it works, then there's your answer. You might want to talk to the company IT department about appropriate permissions. It's a little confusing because you are saying that have access to the database but can't "read" the data? But they can run queries ... (?) So if they can run the query why can't they see the data?

 

[gmazza76]

Cheers, well I suppose logically I could have confirmed the first question anyway as I tested it & the export works with full permissions.

I suppose a better question would be: which part of my script is likely to affected by the read-only permissions (I am guessing creating the table to export, and possibly the transferspreadsheet export function)?
Without anyone I can find having the same 'locked-down' access been unable to test this.

Also they can run the queries & see the data. No problem there. Just in the export part I have quoted.

 

[xenou]

Hard to say. It seems likely that if IT is not giving out write permissions then they may not be able to write a file either (which is what an export does). I don't quite know what you mean by a locked down database. It is rather unusual. Access is a file based database and normally users do need write access to the database location (since Access creates a lock file that must be written to during use). This is, however, changing somewhat with new releases of Access -- 2013 will be available "in the cloud" with a SQL server engine, and 2010 is often used with sharepoint.