Using Passthrough query in MASC

Sep 12
2012

We have recently converted a database from Access to SQL and needed to increase the speed of the reports when working with a large recordset. We amended this code from the web which significantly increased the speed on 2 reports but reduced the speed on another – so its a case of testing to get the best results.

Public Sub PassThrough(strSQL As String, tmpQry)
On Error GoTo ErrHandler
 
Dim obj As QueryDef
Dim dbsCurrent As Database
Dim qdfPassThrough As QueryDef
 
Set dbsCurrent = CurrentDb()
 
'delete the existing query
For Each obj In dbsCurrent.QueryDefs
     If obj.Name = tmpQry Then dbsCurrent.QueryDefs.Delete tmpQry
Next
 
 
Set qdfPassThrough = dbsCurrent.CreateQueryDef(tmpQry)
qdfPassThrough.Connect = "ODBC;DSN=MASCSql;DATABASE=mascdataSQL;UID=xxxx;PWD=yyyy" ' replace with your details
qdfPassThrough.SQL = strSQL ' this is generated from the form taking the parameters and changing the table names to add the dbo. 
qdfPassThrough.ReturnsRecords = True 
 
dbsCurrent.Close
 
Exit_ErrHandler:
Exit Sub
 
ErrHandler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_ErrHandler
End Sub

Sales Analysis for MASC

Apr 10
2009

This addon program for MASC allows customers to view the sales Quantities or values by week , month quarter and year compared to the same time period last year.  When you have installed the program you will have to relink to your mascdata. The demonstartion below shows how to populate the fields and demonstrates the reports.

Please leave you questions in the comments section – to download click here  and run the program once the file has downloaded

salesanalysis

MASC Delivery Dockets

Feb 10
2009

We added a new feature to MASC to allow for faster docket entry

 To enter manual delivery dockets select Sales, Docket Entry. The screen below will loop through the selection and entry process. The main points of the screen are detailed.

  • To start select a route and the customers for that route will be displayed
  • The customer details are displayed when your press next or double click on the customer code
  • Products in the price matrix are show on the docket
  • The user must enter a docket number and date before the docket can be posted
  • The user has the option to post a payment for this docket
  • Only line with an Quantity entered in the Qty column will be posted
  • The user can add additional items to the docket using the dropdown product list
  • The delivery docket entry history for the session is stored for your reference

docketentry

If you need some help on a project drop leave a comment on the post and I will reply.