Execution speed

[Shell]

In Access 2000 and Access 2003 I have vba code which creates a pass-thru
query to SQL SVR. I use code to set up the criteria for the query.

I then have a make-table query which uses the Pass-thru query as input.

Is this efficient? Would an update query be faster? Is there a better,
quicker way to get the data?

Thanks

 

[John W. Vinson]

In Access 2000 and Access 2003 I have vba code which creates a pass-thru
query to SQL SVR. I use code to set up the criteria for the query.

I then have a make-table query which uses the Pass-thru query as input.

Is this efficient? Would an update query be faster? Is there a better,
quicker way to get the data?

Thanks

MakeTable queries have some real disadvantages: they have a lot of overhead
since they must create a table, create indexes for it, update the system
tables with that information, etc. etc. Worse, they make arbitrary
assumptions about field sizes (all Text fields are 255 bytes for example), and
don't allow you to control formats or relationships.

Consider instead having a local permanent table with appropriate fieldnames,
sizes, indexes, relationships, etc. When you need to migrate data from the SQL
table, run a Delete query to empty the local table, and then an Append query
based on the pass-through.

You should also see if you can "unask" the question - do you in fact need a
local copy of the data, or could a simple select query (based on a link to a
SQL View for example) provide the information you need?

 

[Shell]

Thank you John.

I cannot "unask" the question as once I get the data in Access I must
modify the results as per user specs.

I will, however, look at the update query suggestion.

Thanks

 

[John W. Vinson]

I will, however, look at the update query suggestion.

Actually I suggested an *append* query, not an update query...

 

[Shell]

Yes, thanks for the catch. I meant append but I wrote update