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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anne.rowe@ozemail.com.au - 22 Nov 2006 23:06 GMT

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

Reply to this Message

Rick Roberts - 24 Nov 2006 18:35 GMT

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

> Hi,
> I have an ADP database connected to a SQL Server database.
[quoted text clipped - 3 lines]
> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
> "tblSaving", strFile, True, "Saving".

Reply to this Message

anne.rowe@ozemail.com.au - 25 Nov 2006 19:59 GMT

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

> Hi Anne,
>
[quoted text clipped - 14 lines]
> > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
> > "tblSaving", strFile, True, "Saving".

Reply to this Message