Call a MS Access 2003 Report from SQL Server 2000

[Guest]

Hi all
Can any please advise if this can be done and if so were do i start to look.

We have an inventory system that is a MS Access 2003 ADP front end and SQL
Server 2000 Back End we also use scanning software for some business
processes which uses SQL Replication to Push Pull the data between mobile
device and main server. We need to be able to call a MS Access Report from
either a SP or Function can anyone help.

Many thanks in advance

 

[Sylvain Lafontaine]

What do you mean exactly with "We need to be able to call a MS Access Report
from either a SP or Function can anyone help" ?

Do you mean that you want to use either a SP or an User Defined Function
(UDF) as the record source for a report?

 

[Guest]

Hi Sylvain

Thankyou for getting back to me.

No we need to right some code that will call ms access or in particular a
report in msaccess we dont need it to be in view state just need it to fire
and so it goes off to the printer.

The mobile device when it syncs with main server triggers are fired that
updates information, an one of the values that gets gets set back by
replication is PintLabels value can be either False or true if true a value
is placed in the quantity feild, inturn when i am processing this record that
i have recieve i need to be able to ignite or call or make active or send to
printer a predefined report name.

You see if i have a Field Called Printlabels and I have a QTY and i also
have a product code i can look up a report name in a seperate products table
which has a field for the report name. I can do this in VBA very easy but not
shore how to do this in backend SQL .

I have in the past been able to call web methods using sp_oacreate etc. But
this is not a C# project they are using Access 2003 as there GUI.

I hope this explains a bit better what im tring to do. Im not even shore if
i can do it.

Or may be i need to shell it out if MS access has API to its .exe

Im just not sure.........

 

[Sylvain Lafontaine]

Oh boy! Big question here.

It's a certainty that while it's theoritically possible to open a report by
using Access' Automation (with maybe a combination of SendKeys), doing so
directly from a SQL-Server procedure is probably a good recipe for trouble
or even disaster. The report features of ADP are very similar to those of
MDB files; so probably you can ask in the m.p.a.reports newsgroup how to do
this; however, this is path that personally, I would never travel.

IMHO, you would be in a much better position to use any other method of
generating a report than calling Access using Automation directly from
SQL-Server.