Goofy requirement

  • Thread starter Thread starter Kevin3NF
  • Start date Start date
K

Kevin3NF

Client has an Access database being converted to MDB FE, SQL Server 2000 BE. Fine...no prob.

Now, they want a hosted SQL Server for customers to enter info, and the local SQL Server to go get that info periodically. Cool...can do.

NOW, they want Access to fire an existing macro that prints a report whenever new information is pulled from the hosted SQL Server to the local one. Basically, a trigger that runs a macro. Don't know how to pull that off....timer maybe?

Thoughts are appreciated as always....:-)

(Next they are going to want to send info back up to the hosted one...just you watch...LOL)

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
Kevin3NF said:
Client has an Access database being converted to MDB FE, SQL Server 2000 BE. Fine...no prob.

Now, they want a hosted SQL Server for customers to enter info, and the local SQL Server to go get that info periodically. Cool...can do.

NOW, they want Access to fire an existing macro that prints a report whenever new information is pulled from the hosted SQL Server to the local one. Basically, a trigger that runs a macro. Don't know how to pull that off....timer maybe?
Click to expand...

A timer would certainly work. But as far as the trigger goes I'd ask
the SQL Server folks. Can triggers send emails? You'd still need
something checking for the existence of an email. Can a trigger send
an IP broadcast? Something which I know is possible but have never
checked it out.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Kevin,

The only way I can think of is to have a hidden form that runs a timer event to check a database field periodically, say once a minute. The SQL Server trigger can set that field's value.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Client has an Access database being converted to MDB FE, SQL Server 2000 BE. Fine...no prob.

Now, they want a hosted SQL Server for customers to enter info, and the local SQL Server to go get that info periodically. Cool...can do.

NOW, they want Access to fire an existing macro that prints a report whenever new information is pulled from the hosted SQL Server to the local one. Basically, a trigger that runs a macro. Don't know how to pull that off....timer maybe?

Thoughts are appreciated as always....:-)

(Next they are going to want to send info back up to the hosted one...just you watch...LOL)

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
Tony...we are on the same wavelength...I am one of the SQL Server guys :-)

I had the idea of an email as well, which satifies the result if not the
direct way they intended. Triggers can run virtually any T-SQL code,
including DOS commands from a specific stored procedure. Email should work,
since the server will have a mail client installed on it (Small Business
Server w/Exchange)

Thanks!

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

whenever new information is pulled from the hosted SQL Server to the local
one. Basically, a trigger that runs a macro. Don't know how to pull that
off....timer maybe?
 
I thought maybe so, but I've never really had occasion to fool with timers. That also requires that at least one person have the FE open, yes? Back-end is all SQL Server....no local tables in Access MDB

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

Kevin,

The only way I can think of is to have a hidden form that runs a timer event to check a database field periodically, say once a minute. The SQL Server trigger can set that field's value.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Client has an Access database being converted to MDB FE, SQL Server 2000 BE. Fine...no prob.

Now, they want a hosted SQL Server for customers to enter info, and the local SQL Server to go get that info periodically. Cool...can do.

NOW, they want Access to fire an existing macro that prints a report whenever new information is pulled from the hosted SQL Server to the local one. Basically, a trigger that runs a macro. Don't know how to pull that off....timer maybe?

Thoughts are appreciated as always....:-)

(Next they are going to want to send info back up to the hosted one...just you watch...LOL)

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
Kevin,

Supposing that Access is also installed on the SQL Server....

1. You'd schedule your import from the hosted database...this could be
accomplished using a variety of means
2. You might have a trigger on the local table receiving the data...or code
within your import code to...
3. Run Access with the MDB and the macro on the command line, i.e., "path
to msaccess.exe" "path to mdb" /x macronamehere

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Client has an Access database being converted to MDB FE, SQL Server 2000 BE.
Fine...no prob.

Now, they want a hosted SQL Server for customers to enter info, and the
local SQL Server to go get that info periodically. Cool...can do.

NOW, they want Access to fire an existing macro that prints a report
whenever new information is pulled from the hosted SQL Server to the local
one. Basically, a trigger that runs a macro. Don't know how to pull that
off....timer maybe?

Thoughts are appreciated as always....:-)

(Next they are going to want to send info back up to the hosted one...just
you watch...LOL)

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
Kevin3NF said:
I thought maybe so, but I've never really had occasion to fool with timers. That also requires that at least one person have the FE open, yes? Back-end is all SQL Server....no local tables in Access MDB
Click to expand...

Something will have to be running to print out this report. Maybe
it's a service on a server but likely not. Maybe it's a little VB
program on some ones computer in the task bar.

Maybe the trigger in SQL Server runs a console only VB program which
takes a parameter and prints a report. that's probably the simplest
approach with a minimum of other settings to muck with.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Access or the Access runtime on an SBS Server is not a big deal....just make
sure the server is updated regularly (like you would do anyway?).

An alternative might be to create a DCOM object that would run Access on
user(s) PC. But this has its drawbacks...user' PC has to be up/running, and
they may be surprised by Access running a report unexpectedly.
 
Back
Top