Solved

MS Access Pass through Query from Sage Line 50

Posted on 2011-09-14
12
801 Views
Last Modified: 2012-08-13
Hi there,

I have managed to borrow a piece of code from the internet for running a pass through query via VBA as below. As I have read that a pass thorugh query would be faster than using a query from within the [INVOICE] linked table I would like to use the 'temporary' queries results in a pop up form of which has criteria. However this code needs to be run regularly with different criteria but once the querydef has been created it say that 'this query already exists'.

How can I write the code so it allows to create a temporary query or overwrite an existing saved query. I really am trying to shave off the current time it takes to load the results from the Sage database but I am sure you all know that Sage isn't the fastest when querying from it. Any tips on how I can speed up my query from Sage would be much appreciated.

Thank you in advance
Dim strCompany As String
Dim strYearStart As String

strCompany = Me.Combo3
strYearStart = "'2011-1-1'"

Dim dabs As DAO.Database
Dim qdef As DAO.QueryDef
Dim recs As DAO.Recordset
Set dabs = CurrentDb
Set qdef = dabs.CreateQueryDef("qry3Pass")
qdef.Connect = "ODBC;DSN=SageLine50v17;UID=***;PWD=***;"
qdef.SQL = "SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, INVOICE.ITEMS_NET " & vbCrLf & _
            "FROM INVOICE " & vbCrLf & _
            "WHERE INVOICE.ACCOUNT_REF=" & strCompany & "AND INVOICE.INVOICE_DATE > '2011-1-1'"

qdef.ReturnsRecords = True
'qdef.Execute
'Set recs = qdef.OpenRecordset(dbOpenSnapshot)



Me.frm2Pass.Form.RecordSource = "qry3Pass"

Open in new window

0
Comment
Question by:databarracks
  • 7
  • 3
  • 2
12 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 36535107
Don't use CreateQueryDef - just build the query and then set the SQL as needed:
Dim qdef As DAO.QueryDef
Set qdef = Currentdb.QueryDefs("qry3Pass")
qdef.SQL = "SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, INVOICE.ITEMS_NET " & vbCrLf & _
            "FROM INVOICE " & vbCrLf & _
            "WHERE INVOICE.ACCOUNT_REF=" & strCompany & "AND INVOICE.INVOICE_DATE > '2011-1-1'"

Open in new window

That should get you where you need to be.
0
 

Author Comment

by:databarracks
ID: 36535210
You are an absolute legend and it works really well. I noticed that once the query has been run once it loads much faster thereafter, but if the form has been inactive for a while it seems to take a bit longer. Do you have any other tips as how I could keep my load times consistent and failr quick. Ideally I am looking for a 3 to 5 sec waiting time?
0
 

Author Comment

by:databarracks
ID: 36535259
Looking at your code again, this means that the query is actually working off the linked table that I have established and therefore isn't a pass thorugh query in essence or am I wrong?
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 28

Expert Comment

by:omgang
ID: 36536206
databarracks, here's the SQL for a pass-through query I have in Access to Sage Mas90.  Note the specification of the SOTAMAS90 DSN

SELECT AP1_VendorMaster.Division, AP1_VendorMaster.VendorNumber, AP1_VendorMaster.VendorName
FROM AP1_VendorMaster IN '' [ODBC;DSN=SOTAMAS90;UID=XXX|COM;PWD=password;Directory=T:\Best\Mas90;CacheSize=4;DirtyReads=1;BurstMode=1;SERVER=NotTheServer];

Where UID=XXX|COM is user ID pipe Company Code

The Sage Mas90 installation created the SOTAMAS90 DSN.  I'm thinking you should have something similar for Line50?

OM Gang
0
 

Author Comment

by:databarracks
ID: 36536258
Hi there,

I am not sure if this was created upon Line 50 installation but I have the below code:

ODBC;DSN=SageLine50v17;UID=XXXr;PWD=;

I am guessing that all I need to do is add the rest of your code from ";Directory" onwards with the appropriate path to my ACCDATA file. Would there be any performance benefits using this approach?
0
 
LVL 28

Expert Comment

by:omgang
ID: 36536315
By definition a pass-though query uses the database engine of the host to run the SQL, e.g. the query will be executed by Line50 and not Access.  I saw a performance increase using pass-through queries instaed of linked tables to Mas90.

A quick Google for 'Sage Line 50 pass-through query' resulted in
http://www.about-access-databases.com/pass-through-query.html

Also, look at this PAQ describing how to find/determine the connect string to use in your Pass-Through query
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26179598.html

OM Gang
0
 

Author Comment

by:databarracks
ID: 36536433
I have determined the connection string, which was the one that I had sent a short while ago and have already created a pass through as per the google approach.

My current code as per LSMConsulting (ID:36535107) works fine but is not fast enough. THe code below is a slight change of LSMConsultings's and is more like yours but I keep getting the error 'Invalid table specification (#0)' ??
Dim strCompany As String
Dim strYearStart As String

strCompany = "'" & Me.Combo3 & "'"
strYearStart = "'2011-1-1'"

Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.QueryDefs("qry3Pass")
qdef.SQL = "SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, INVOICE.ITEMS_NET " & vbCrLf & _
            "FROM INVOICE " & vbCrLf & _
            "IN [ODBC;DSN=SageLine50v17;UID=manager;PWD=;]" & _
            "WHERE INVOICE.ACCOUNT_REF=" & strCompany & " AND INVOICE.INVOICE_DATE > '2011-1-1' "

qdef.ReturnsRecords = True
'qdef.Execute
'Set recs = qdef.OpenRecordset(dbOpenSnapshot)



Me.frm2Pass.Form.RecordSource = "qry3Pass"

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
ID: 36536513
Is qry3Pass already a pass-though query to Line50?  If so, use Debug.Print qdef.SQL to get the current SQL to compare to.

Here's what I'd do:  create a pass-though query to the Line50 table using a valid company and valid date as criteria.  Make sure that executes and returns records.  Copy the SQL statement from that query and use it to build the SQL statement for your VBA routine.

The error seems to indicate an invalid table name, e.g. INVOICE is not a table in Line50.  Is it possible the linked table name in your Access db is different from the actual table name in Line50.

OM Gang
0
 

Author Comment

by:databarracks
ID: 36536606
Yes qry3Pass is already a pass-through query and works fine using a valid company and date. The table name is fine and is correct. The error comes about when I try and use the "IN" condition within the SQL. Ultimately if you don't believe that this route would improve performance considerably then, I will stick to the current approach that is working and takes about  max 10 seconds to load. The working code is below and speed is decent. It takes anout 10 seconds to return the results upon first run of query but for any concequential re-runs therafter despite the change in criteria take about 3.

All I want is to improve the initial 10 seconds to about 5 or 6. If there is no benefit in running the pass through from vba directly as opposed to the method below, then I will just leave it at that.

THank you again for your patience.
Dim strCompany As String
Dim strYearStart As String

strCompany = "'" & [Forms]![frmMain]![sfrCustomersMain].[Form]![sfrCustomerList].[Form].[account_ref] & "'"
strYearStart = Me.txtYears.value

Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.QueryDefs("qry3Pass")
qdef.SQL = "SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_OR_CREDIT, INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, INVOICE.ITEMS_NET " & vbCrLf & _
            "FROM INVOICE " & vbCrLf & _
            "WHERE INVOICE.ACCOUNT_REF=" & strCompany & " AND INVOICE.INVOICE_DATE " & strYearStart & ""

qdef.ReturnsRecords = True
'qdef.Execute
'Set recs = qdef.OpenRecordset(dbOpenSnapshot)



Me.RecordSource = "qry3Pass"

Open in new window

0
 
LVL 84
ID: 36536759
The IN keyword can be used, but if you're working with linked tables there's little reason to use that, and it can be somewhat of a maintenance headache.

I don't see how you could reduce the load time of the query, unless you fired off that query well before it's needed (which would allow Access to open and pool/maintain the connection). You could do this in a startup form when the app starts, if that's appropriate, but honestly I'd just instruct the users that there will be a bit of a delay, or show a Label or something on the form that says "Please wait ... connecting to database" or something like that.
0
 

Author Comment

by:databarracks
ID: 36536864
I could give that a go by opening the connection before it is used but maybe that will be too similar to a linked table meaning that the connection would be constant from the time the user starts using it.  I think I will do what you said and just let the user name that this may take a few seconds before it loads.

THank you for your help as always, it is much appreciated.
0
 

Author Closing Comment

by:databarracks
ID: 36536876
Very helpful as always
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MSSQL join different row from other table 14 65
MS Access, How to create variable 9 34
Convert VBA UDF to SQl SERVER UDF 4 45
Problem with MySQL query - graph 3 21
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question