Table Name problem in VBA using Access 2003 adp and SQL Server

[anne.rowe]

Hi,
I have an ADP database connected to a SQL Server database.
I need to export/import data from a table to Excel.
I use the following code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tblSaving", strFile, True, "Saving".

If I log in Access as the owner of the database the code works, however
ifg I log as another user with Select, Insert, Update and Delete
Permission on the tblSaving table, it does not find the table.

I have tried to change the name as:
dbo.tblSaving,
dbo_tblSaving,
tblSaving (dbo) (As displayed in the database container)
None of them work.

I would really appreciate if someone could help and let me know how I
can name the table in VBA.

Thank you so much

Anne

 

[Guest]

Hi Anne,

I ran into the same problem. Here is what I found. You can use the export
option of docmd.transferspreadsheet if your data source is a table. For a
View or StoredProcedure you need to use the OutputTo command. Hope this helps!

DoCmd.OutputTo acOutputServerView, "Qry_ExportClients", acFormatXLS,
"C:\IntraFlow\ClientExport.XLS"

Rick

 

[anne.rowe]

Hi,
I am using tables already. my problems is more on the name of the table
that I use, than the object type. I am afraid that I would run into the
same problem if I use a query and docmd.output, it will work for me as
the dbowner but not for other users.

Anne