MS Access 2003 "Read-Only" transferspreadsheet export question

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
628
Office Version
  1. 365
Platform
  1. Windows
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:

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?
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 19, 2011
Messages
628
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top