Ho to Save password while connecting Database from MS access & how to shedule queries

[harshakusam]

Ho to Save password while connecting Database from MS access & how to shedule queries

Hi All,

Can someone pls help me..

I have a mdb file with some 110 queries in it and a made a ODBC connection to connect to my database and daily at 10:00 pm i ave to run all these queries and sent to higher level.

So my problems are...

1) Each time when i click on query it will ask password and every time i ahve to enter a password and hit enter once output comes i have save the output into excel and we have privillage(mean a button) in excel to "Analyze with excel " so all this process takes 2 hrs for me and i know iam wasting my time...

2) Second problem as iam runing this reports daily basis exactly i have to start running this reports at 10:00 PM. So is there any way to shedule this reports so that they can run at 10 daily and save these reports in certain location in excel format.


If you have any suggestions pls let me know.. if you want any detail information pls let me know....

 

[Dennisk]

Re: Ho to Save password while connecting Database from MS access & how to shedule que

1) Are your tables linked

2) with 'pass-through' queries you can specify what the server is, user and password.

 

[harshakusam]

Re: Ho to Save password while connecting Database from MS access & how to shedule que

Hi Den,

Thanks for your replay...

Iam new to MS access...

Yes i think my tables are linked.

Is there any way to shedule this reports.. so that each query will run and save the output in excel...

I dont know about 'pass-through' queries. Pls....

 

[Dennisk]

Re: Ho to Save password while connecting Database from MS access & how to shedule que

Open you query for design, then depending on your version of Access specify that the query is pass-through. On my system it is found on the query menu, a wizard will then guide you through connecting to an ODBC database.

 

[harshakusam]

Re: Ho to Save password while connecting Database from MS access & how to shedule que

Hi Den,

I tried it works.. but one more new problem...

Evrey day these queries will update.. can i make it generic what ever might be the queries that are there in mdb file can i make my password should be saved....

Is there any posibility to shedule these queries...

 

[Dennisk]

Re: Ho to Save password while connecting Database from MS access & how to shedule que

You can automatically run any code in Access by calling a function in a macro called 'autoexec' or you can do it from a hidden form opened at startup. The function will essentially consist of docmd.OpenQuery commands.

You will require the windows scheduler to run your access program.

or you can run it 24/7 and have a time check within the code to determine when to run your queries.

 

[HiTechCoach]

Re: Ho to Save password while connecting Database from MS access & how to shedule que

Pardon me for jumping in ...

This may help:
How to Start Access by Using the Windows NT Schedule Service

Hope this helps ...

 

[The_Doc_Man]

Re: Ho to Save password while connecting Database from MS access & how to shedule que

Good call, coach!

 

[HiTechCoach]

Re: Ho to Save password while connecting Database from MS access & how to shedule que

Good call, coach!

Thanks. Glad I could assist!