Selasa, 05 April 2016

RE: [MS_AccessPros] Re: pass through queries

 

Liz


Here is an example of one of my queries. It's not technically a passthrough since it uses a DNS-less connection string. Passthroughs are saved queries with an ODBC DNS file used for the connection. Because it uses a Connection object all the work is done on the SQL side thus utilizing the SQL engine and not the Access engine.

Oh, and TRUNCATE is like DELETE but it resets the IDENTITY primary key (same as an autonumber)

gconSQLServer is the name of the SQL Server Instance
gconSQLDB is the name of the database

    Dim cnn As ADODB.Connection
    Dim strsql As String



    'Use DSN-less connection so TRUNCATE will run.
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=sqloledb;Data Source=" & gconSQLServer & ";" _
        & "Initial Catalog=" & gconSQLDB & ";Integrated Security=SSPI;"
    cnn.ConnectionTimeout = 0
    cnn.Open

    'Delete any existing data for PayPeriod.
    strsql = "TRUNCATE TABLE tblImport_PMRData_Hrs"
    cnn.Execute strsql

    strsql = "TRUNCATE TABLE tblImport_PMRData_Vol"
    cnn.Execute strsql



---In MS_Access_Professionals@yahoogroups.com, <liz_ravenwood@beaerospace.com> wrote :

Brilliant.

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, April 05, 2016 12:06 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: pass through queries

 



    

Duane,

 

I don't know. I assume it would look something like:

 

stSQL = "xxx"

SQLPassThrough (stSQL)

 

Public Function SQLPassThrough(ByVal SQL As String)

Dim qdf As DAO.QueryDef

 

Set qdf = CurrentDb.CreateQueryDef

With qdf

     .Name = "qryPassThrough"

     .Connect = "ODBC;Description=FileName;DRIVER=SQL Server;SERVER=SRVxxxxx9999;Trusted_Connection=Yes;"

     .SQL = stSQL

     .ReturnsRecords = False

     .Execute

     .Close

End With

 

Thanks

Adam,


Why don't you have a saved, named query? I typically keep one or more generic saved queries and use  a little DAO code to modify the SQL property. 

 

I don't believe your SQL statement will run as a pass-through based on using IIf(). You may need to replace this with CASE syntax.

 

Duane Hookom, MVP

MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 5 Apr 2016 08:02:24 -0700
Subject: [MS_AccessPros] Re: pass through queries



Duane,

 

How do I add the connection string to run a pass through via VBA, without having to use a named query?

 

VBA: stSQL = "SELECT tblRevTranBillerActiveTopNodes.LastUpd, tblRevTranBillerActiveTopNodes.TopParentID, tblRevTranBillerActiveTopNodes.TopParentName, tblRevTranBillerActiveTopNodes.TierLevel, " & _
    "tblRevTranBillerActiveTopNodes.SID, tblRevTranBillerActiveTopNodes.ComplianceOfficer, Count(tblRevTranBillerActiveTopNodes.ClientID) AS ClientIDs, Sum(tblRevTranBillerActiveTopNodes.Rev0112) AS SumRev12, " & _
    "Sum(tblRevTranBillerActiveTopNodes.Tran0112) AS SumTran12, Sum(IIf([ProdType]=" & Chr(34) & "Cash" & Chr(34) & ",[Rev0112],0)) AS SumCashRev12, Sum(IIf([ProdType]=" & Chr(34) & "Cash" & Chr(34) & ",[Tran0112],0)) AS SumCashTran12 " & _
    "INTO tblRevTran12MoTotalsActiveTopNodes " & _
    "FROM tblRevTranBillerActiveTopNodes INNER JOIN tblProdCode ON tblRevTranBillerActiveTopNodes.Prod = tblProdCode.ProdCode " & _
    "GROUP BY tblRevTranBillerActiveTopNodes.LastUpd, tblRevTranBillerActiveTopNodes.TopParentID, tblRevTranBillerActiveTopNodes.TopParentName, " & _
    "tblRevTranBillerActiveTopNodes.TierLevel, tblRevTranBillerActiveTopNodes.SID, tblRevTranBillerActiveTopNodes.ComplianceOfficer " & _
    "ORDER BY Sum(tblRevTranBillerActiveTopNodes.Rev0112) DESC"
Db.Execute stSQL, dbFailOnError + dbSeeChanges

 

Connection String: ODBC;Description=FileName;DRIVER=SQL Server;SERVER=SRVxxxxx9999;Trusted_Connection=Yes;

 

Liz,
 
I create Access apps for lots of users so I prefer to not have to create ODBC/DSNs for them all. DSN-less connections work well for me.
 
Linked tables won't threaten your 2gb size limit.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 10 Aug 2015 22:43:30 +0000
Subject: RE: [MS_AccessPros] RE: pass through queries


Duane, So I suppose it just built it for me.  There had already been a DSN and they are screaming fast but I don't have any linked tables threatening the 2gb size of my database.

 

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, August 10, 2015 2:00 PM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] RE: pass through queries

 



Liz,
 
You might not need a DSN. I use a connection string like:
 
ODBC;Driver={SQL Server};Server=USxxxx0000,1433;Database=myDatabaseName;Uid=myUserID;Pwd=myPassword
 
The Server might require an instance and/or PORT. You can also use integrated security as opposed to a SQL Server login.
 
And yes, this is much more efficient than queries built from linked tables.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 10 Aug 2015 20:46:51 +0000
Subject: [MS_AccessPros] RE: pass through queries

Got it!  So…  the ellipses in the properties window of the query will wizard prompt for the DSN and ask if you want the password info stored.

 

Ran the PT query against a table with > 300 million rows and it was screamin fast!

 

Thanks pros.

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, August 10, 2015 12:56 PM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: [MS_AccessPros] RE: pass through queries

 

 

Looking at the web site is humbling me even more. 

 

I suppose I need to figure out how to find the right connection string for my particular server, so I'm looking at the SQL Server connection strings and man – are there many – depending on what type of setup you have and well, there's the rub…  I guess I need a LOT more knowledge.

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, August 10, 2015 12:33 PM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: [MS_AccessPros] RE: pass through queries

 

 

Thanks Darrell.  J

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, August 10, 2015 11:37 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] RE: pass through queries

 

 

Connectionstrings.com

 

 

Darrell

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, August 10, 2015 2:36 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] pass through queries

 

 

Pros, I'm going to need to try to run a passthrough query and I have the SQL needed, but am told I don't need any kind of ODBC connection but do need a connection string as part of the query (LinkedIn discussion group) and am not having any success finding a good example of how to do this.

 

I don't even know how to build a connection string from my sql server management studio.

 

Please help.  J

 

Respectfully,

Liz Ravenwood

Programmer / Analyst

B/E Aerospace | Super First Class Environments

 

1851 S Pantano Road | Tucson, Arizona 85710

Office +1.520.239.4808 | Internal 814-4808

beaerospace.com

Passion to Innovate. Power to Deliver

 



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.

 

 

 

 

The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.



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: wrmosca@comcast.net
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (14)

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? The Yahoo Mail app is fast, beautiful and intuitive. Try it today!


.

__,_._,___

Tidak ada komentar:

Posting Komentar