Pass-Through variable

[AceBK]

Hello, I am delving into the world of Pass-Through Queries. I have bee growing in my experience with Access 2010, but this is a whole new world for me. We have an Access Database that stores all of our production information but we transfer all the accounting information to Macola. What I am trying to create is that when a user pulls up a company in my access database, I would like a pop up box to show some information from my Macola database. I have created a script on my SQL server to run and I even have the information populating when I put in the t-sql code in my pass-through Query, the challenge I am having is, how do I get the query to look for the account number on my current Database form.

The name of my Access form = JobsList
The name of the field where the account number = vfaccountnumber
The SQL I am using in my Pass-Through Query =
select [DATA].dbo.quickAging(70274,getdate())

where 70274 is the account number that I would like to come off of my JobsList Form.

Once I get this figured out, I need to find a way to populate the results in a form that can show up in a pop up window.

Any help on this would be greatly appreciated.
AceBK

 

[ozinm]

So you're trying to run a passthrough query and want to send it a parameter?

I'm guessing everyone who is using the Access front end has their own copy?

If so, you can create a generic passthrough query that gets altered on the fly.

All you'd need to do is generate the text for the query.

Have a look here for an example:
https://support.microsoft.com/en-us/kb/131534

 

[AceBK]

I am not sure, but that does not appear to be what I am looking for. All I need is to run the query using the account number on my open form. I think it might be easier to have button that creates a new query that will pull in the account number from the open form. I guess what I still need is the finishing touches on making this PassThroughQuery. Any thoughts?

Dim strConnection As String
Dim strSQL As String
Dim strQueryName As String

strConnection = "ODBC;DSN=######;Description=Aging Database;UID=user;PWD=pass;DATABASE=####"
strSQL = "select [DATA].dbo.quickAging(" & Me.VFAccountNumber & ",getdate())"
strQueryName = "AgingTest"


The strSQL line does work to add the account number for the job I am currently on, it's just getting this sql line into the sql line of the pass through query

 

[Galaxiom]

CurrentDb.Querydefs("AgingTest").SQL = strSQL

 

[AceBK]

I am still quite lost. I have done a fair bit of research on this and I am not getting any further ahead. I have tried making a function to create a new pass-through query but I can't seem to get the function to run. I have the Pass-Through query made manually, all I need is to put in a new .sql string before it runs. Can this be done with VBA? I am looking for the complete code to do this as I tried adding the recommendation from Galaxiom, but I assume I am needing more parts. I am really hitting a brick wall. Let me know if I need to pass on more information.

 

[Galaxiom]

No need to make a new query each time. It is easier to modify one that already has the connection information. The SQL can be modified using the line of code I provided.

How are you attempting to run the sub/function? Show the code you are using.

 

[AceBK]

I discovered I needed to run a function, so everytime I select a company on my main form, I run this function which first deletes the query than creates a new one with the following code. I tried running this function without deleting the previous made query, but it won't overwrite it, it will just not make the query. This actually seems to be working. Is there a better way?

 Function CreateSPT(SPTQueryName As String, SQLString As String, _
                  ConnectString As String)
      '-----------------------------------------------
      ' FUNCTION: CreateSPT()
      ' PURPOSE:
      '   Creates an SQL pass-through query using the supplied arguments:
      '      SPTQueryName: the name of the query to create
            SQLString = "Select [DATA].dbo.quickAging(" & Forms.JobsList.VFAccountNumber & ",getdate())"
            ConnectString = "ODBC;DSN=PPLSQL;Description=Aging Database;UID=cruser;PWD=crpass;DATABASE=DATA"
      '-----------------------------------------------
         Dim mydatabase As Database, myquerydef As QueryDef
          Set mydatabase = DBEngine.Workspaces(0).Databases(0)
         Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
          myquerydef.Connect = ConnectString
         myquerydef.SQL = SQLString
         'myquerydef.Close
      End Function

 

[weeblebiker]

may be oversimplified but what about
an unbound field for input and running re query on lost focus?

http://www.access-programmers.co.uk/forums/showthread.php?t=244719