This is a useful tip if you are using SQL Server as your database and Accessas your front-end application. Use the following code to dynamically change theAccess passthrough query definition, which helps topull necessary data from the server while avoiding redundantnetwork traffic.
Private Sub ChangeQueryDefinition() Dim dbs as Database Dim strSQL as String Dim strQuery as String Dim strPassthroughQuery as String Dim qDef as QueryDefinition Set dbs=Currentdb() strQuery="Your Access query name" srrPassthroughQuery="Your SQL Server view name" '*** Build your SQL statement *** strSQL="SELECT * FROM " & strPassthroughQuery & " WHERE condition" Set qDef=dbs.Querydefs(strQuery) qDef.Sql=strSQL Set qDef=Nothing Set dbs=NothingEnd SubThe resulting passthrough query can be used to connect to any list box or combo box.
Call ChangequeryDefinitionDim strSQL as StringstrSQL="SELECT * FROM Access Passthrough query"Me.lstContacts.Rowsource=strSQL
