ODBC Polling

Okay, here's the skinny... I want to be able to use Access to generate reports that are accessible through my company intranet.  I have a 2003 SBS server and I would like to setup Access to poll data using ODBC.  How do I go about writing a script to auto poll through ODBC?  Is there a way to have access run the report automically?  Is it best to import the data to Access?  Anything else a beginner should know?  Thanks
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
ODBC can only be used to connect to data, namely tables and queries, and not reports.   To pull this off, you'll have to run the report and save it as an external file such as snapshot .snp or Adobe .pdf, then provide the file/path reference of these files to your intranet.

Hope this helps.
rommelitAuthor Commented:

Is there a script that can be written so that the report will generate itself ?
Jim HornSQL Server Data DudeCommented:
You could write code within the Access app to kick out reports to certain file locations, yes.  Create a table tbl_reports that has Access report locations, path/file, adn you could loop through the table and DoCmd.OpenReport YourRecordsetObject!AccessReportName, acPreview to print out.
rommelitAuthor Commented:
What is a good resource for this type of work?
Jim HornSQL Server Data DudeCommented:
My personal favorite is the Litwin/Getz 'Access Developer's Handbook' at http://www.amazon.com/exec/obidos/tg/detail/-/0782140114/qid=1120764676/sr=8-1/ref=pd_bbs_ur_1/104-8239672-1313501?v=glance&s=books&n=507846.  There are multiple chapters dealing with ADO Recordsets, and one on reports and how to print them out.  Keep in mind that what you are trying to accomplish will involve multiple areas of the book.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.