Access error says it can''t find object in VBA cose

Question

Access error says it can''t find object in VBA cose

macrocharlie

Ten Centuries

Points: 1055
More actions
August 29, 2006 at 12:36 pm

#170004

I have an Access Project that I need to export a table's data to an Excel file. When I populate the table I use an Insert statement with a Select statement that has an Order By clause. Apparently Order By only works on Select queries and does not Insert the data into the table in the Order By clause order. So I changed to using a Stord Procedure but I am getting an error. I am using:
"DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "usp_PrintLabels", "C:\Label\" & Me.comboRunSheet.Value & " Labels.xls", True"
method. The "usp_PrintLabels" is the name of a stored procedure in the SQL Server. Access help says this can be a table name or a query name. When I run the procedure I get a message that says:
"The database can't find the object 'usp_PrintLabels'."
The object was created by the dbo so I have also tried listing it as "dbo.usp_PrintLabels" but I get the same error message only changing the sp name to 'dbo.usp_PrintLabels'. Does anyone know what's going on here?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Stupid question... Are you sure that you are connecting to the right DB (sure you are)... and are you sure that this usp is in that DB?

macrocharlie Ten Centuries Points: 1055 More actions · Answer 2

Of course. I've checked out that spelling of the sp is correct and everything.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Sorry but this is as far as my experience with this code goes .

I'm sure somebody else will help you figure it out.

Last stupid question, I promess... does the user running the command on the server has execute permission on that usp?

macrocharlie Ten Centuries Points: 1055 More actions · Answer 4

macrocharlie

Ten Centuries

Points: 1055

August 29, 2006 at 12:53 pm

#657347

The user is me. I'm the dbo.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Sorry I couldn't help... Did you run the command with the profiler to see what was really going on. That was my last resort tool when I was working as a single programmer. Maybe that'll give you something to go on untill somebody else picks this thread up.

Good luck.

Carl Carter-348574 Mr or Mrs. 500 Points: 571 More actions · Answer 6

Hi macrocharlie,

Try the below

DoCmd.OutputTo acOutputStoredProcedure, strProcName, acFormatXLS, FilePath, False

Cheers

Carl.

Dick Schroth Right there with Babe Points: 725 More actions · Answer 7

I was going to suggest that if your stored procedure populates a table, use DoCmd.RunSQL to execute the stored procedure then you could export to Excel from the table.

Dick

macrocharlie Ten Centuries Points: 1055 More actions · Answer 8

Thanks Carl and Dick

The DoCmd.OutputTo acOutputStoredProcedure, strProcName, acFormatXLS, FilePath, False worked great. I didn't get a chance to try the RunSQL, but I try to keep all my queries as SP's in the SQL Server.

Charlie

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Dick's idea is great if you can't make the initial code work... but since you can, I have to agree with you that you're better off with a single proc .