Rabu, 24 Agustus 2016

RE: [MS_AccessPros] Look up tables

 

Greetings,

 

I'm wondering if this is a split database or not and if there is a compact on close in place.

Delete and append operations have made my databases containing tables bloat crazy like.

 

I like the indexing bit, and what I've done is checked if it was a first time of the day load of that database and then did make tables in a back end using subqueries on sql passthrough queries.

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, August 23, 2016 10:53 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Look up tables

 




Jim-

 

It would be better to have static tables with appropriate indexes defined.  On the reload, do a DELETE * followed by an INSERT of the updated data.

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications


On Aug 23, 2016, at 09:54, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am going through the macro and finding out what I can delete of the queries that build tables. I will let you know after I get to that point. I did notice that there are some make table queries and then update and append queries for those tables. I will need to see how I can work around that.

 

At this point, I agree that a button or a separate form to run the queries instead of on open event is a much better choice.

 

Jim Wagner


 

On Tuesday, August 23, 2016 8:09 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

I agree, Duane, but if the current code runs slowly, then he may be better off loading the data once to local tables when the app opens.  I suspect the load process can be improved, but it may still not be enough to get good performance from the remote tables.

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications


On Aug 23, 2016, at 07:56, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I would use SQL statements or saved queries for the row sources. If PeopleSoft is a database server, I would use pass-through queries for greater efficiency. 

 

Duane

 



On Tue, Aug 23, 2016 at 4:49 PM +0200, "luvmymelody@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

Follow up question,

 

My boss has retired and I have inherited one of the databases they worked on. It is really slow when the database opens because of a macro that runs 15 queries on load of the opening form. most of them are queries that create lookup tables from various downloaded tables from PeopleSoft every morning. These are not like the local lookup tables I described before but created every time the form opens. It is a silly situation. These tables that are created are for drop downs on the forms in the database.

 

So I am thinking that I could get rid of these queries by using select statements on the row source instead of creating tables every time the form is open. Not very efficient I think.

 

What is the groups thoughts on using select statements for the row source which are essentially the same thing as the make table queries run in the macro.

 

Thank you for your advice

 

Jim Wagner

 

 






This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

__._,_.___

Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (16)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Tidak ada komentar:

Poskan Komentar