Selasa, 05 April 2016

[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;

Adam

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

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.

 



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.




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.






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: runuphillracing@yahoo.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (9)

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:

Poskan Komentar