Function Question

[jamesmor]

Use 1 function, or multiple functions?

Hey Guys and Gals,

I got a noob question for you. I have an access database that connects to an SQL server. Now I need to pull a file from the server, add information to it, then slice and dice it for some reports.

I was left with about 12 queries that do this process currently, and as much fun as running the macro for this is, I'd rather do it programatically.

I'm going to be using SQL coding rather than calling the queries from my module as I just work better that way.

My big question is:

Should I create 1 big function with definitions for each SQL statement, or should I create 12 small functions each doing their own thing?

My next question is:

What way would be most efficient for hitting the SQL server and while keeping processing time on my end relatively low?

Thanks in advance for all input

Nathan

 

[sportsguy]

Just my opinion

Without the information of the size of the server table, your network configuration, and the amount of data you are accessing, and not sure what type of data torture strategies you are typing about using,
(legal caveats!)

the fewer queries that hit the database the better off the performance of the database will be if others are also hitting it at the same time.

I would suggest writing one query and apply the torture techniques all at once, and time how long it takes for the prisoners to crack, versus the divide and conquer time. . . .

sportsguy

 

[jamesmor]

function questions

I'm looking at ~ 600k records.

I'm not the only person who hits this database, there's hundreds of people that do. Here's my current process.

Create master file (making it a local file so I'm not hitting the server anymore) append invoice information (for some reason if I do it when I pull the initial file, things get screwy)

from the master file, pull out 3 separate files, we call them active/inactive/prospects, these are based on purchase dates

then I run counts of those 3 files - based on which division product was purchased from.

finally, I put those counts back into 1 file.

so I hit my master file (which again is local) a bunch of different times, no one uses the master while or after I'm using it.