Question on Updating Access Tables with SQL Table Data

[JoeM]

I usually create an ODBC connection to my SQL database like this: http://www.depts.ttu.edu/ithelpcentr.../odbcsetup.php

Then, following the following steps:
1. Create a new query.
2. Close the "Show Table" box without selecting any Tables or Queries.
3. In the Query Type ribbon, select Pass-Through.
4. Paste your SQL Code in the blank window
5. In the Show/Hide ribbon, select Property Sheet
6. Click on the ODBC Connect Str property, and a Select Data Source window should pop-up
7. Browse to the name of the ODBC connection you set up.


8. If it prompts you to Save the password in the connection string, I usually say Yes so you do not have to supply it every time you run the query.
9. Save and close

Now you just run this query like any other query (i.e. use OpenQuery command in VBA).

As for capturing the parameters from the Form and applying them to the SQL code you are building, and then in turn applying that SQL code to your pass-through query, see the link I provided up in post #9.

[Scyclonic]

Yeah from ODBC to step 9. I've got that part of it working properly.

I am not quite sure how to follow your link for post #9 though.

Maybe it is because I am missing what the query is looking like.

But I cannot for the life of me see where you wouldn't need a connection string of some sort to run that.

==========

If I had a pass through with

Select *
From dbo.Table
Where dbo.Table.UniqueID = XXXXX

Then in VBA, how do I define what that XXXXX is and pass it.

---

In your link, you are creating an entire query there, instead of just declaring XXXXX as '1' and then passing it somehow.

That is where I am getting a bit lost on it

[JoeM]

Let's say the criteria line of your query looks something like your example:

Select *
From dbo.Table
Where dbo.Table.UniqueID = XXXXX

If you were to create this code in VBA, it would look like:

Dim mySQL as String
mySQL = "Select * From dbo.Table Where dbo.Table.UniqueID = XXXXX"

But you want this UniqueID to be variable, based on what you enter into your Selection Form. So, let's say that you have a text box named "txtID" on your Selection Form, where you input the name you want the query to return. So, in the VBA code that is building your SQL string, when you come to building this criteria part, you incorporate the value from your text box into the code, i.e.

Dim mySQL as String
mySQL = "Select * From dbo.Table Where dbo.Table.UniqueID = " & Me!txtID

If you want to see if you have the syntax all correct, an easy way to check is after you create the string, return it in a Message Box, i.e.

MsgBox mySQL

Then, once you see you have the syntax correct, you assign it to your pre-existing pass-through query using QueryDefs like I did in the link, and then do whatever you want from there (i.e. open the query, open a report or form based on the query, etc).

[Scyclonic]

Yeah, it is the querydefs portion that has me all confused .

I know in VBA that I can have a text box store a value, then pull it up as a temp var and call it in similar fashion to what you wrote above. My concern is what to do with it once the string is ready to go. I am not quite sure how you use QueryDefs and call an existing query.

Also, on the SQL query itself (the pass through) would I write it as:

Select *
From dbo.Table
Where dbo.Table.UniqueID = @UniqueID

or do I write it as

Select *
From dbo.Table
Where dbo.Table.UniqueID = ________ (I am not sure what to enter here if I am using QueryDef to bring things over)

[JoeM]

Also, on the SQL query itself (the pass through) would I write it as:

I already showed you exactly how to write it in my previous post! The only thing that might change is the name of your text box on your form.

Yeah, it is the querydefs portion that has me all confused

I think you are overthinking this. Every "query" has a "definition". It is just the SQL code behind the query.
So all we are doing is taking our pre-existing query, and replacing the definition (i.e. SQL Code) with the SQL string we just built.
So look at that line of code from the link (I also copied it here):

' Assign SQL code to a pre-defined query
CurrentDb.QueryDefs("MyQueryName").SQL = mySQL

If the SQL string your just built is named "mySQL", then all you have to change in this line is "MyQueryName" to whatever name you are using for your pass-through query.

That is all you have to do. Now, if you open that query (or any object based on the query), it will reflect the SQL code you just created on the fly.

[Scyclonic]

Wait... so the SQL code is actually only in one spot.

That makes sense for me, I was thinking the query had SQL listed in it, and the VBA had SQL listed in it as well. For some reason, I was thinking it almost looked like a double query of some sort.

So...
Access Query = Blank, but with the Pass-Through
VBA Function = Combining into the SQL string, then CurrentDb.QueryDefs("PassThroughQuery").SQL = (the SQL String I just made in the function)

Alright now I think I am understanding it... not sure why I thought there were two seperate SQL queries going on for some reason.

[JoeM]

Every existing query has SQL code (that is what every query really is). You really can't have a Query with no code behind it.
Think of the SQL code as the query itself, if it helps, and the Query name is really just the "alias" for the code.

So all we are doing is building the SQL code we want in VBA, and then replacing the SQL code for an existing query with the new SQL code we just built.
So every time we run the process with new Form selections, the name of the query never changes, just the SQL code assigned to it changes.

[Scyclonic]

So I may as well just name the Query "SQL Connection" or something to that effect, huh?

[Scyclonic]

Hmm... just put things together and am getting a wierd error when running a macro.

Compile Error: Wrong number of arguments or invalid property assignment.

=======================

VBA CODE

Public Function WhyTerminate(ByVal StudyID As Integer, ByVal StudyWaveID As Integer)
Dim stringSQL As String
stringSQL = "SELECT dbo.Respondent.code1 AS VendorID, " & _
"Max(dbo.Respondent.RespondentID) AS MaxOfRespondentID, " & _
"Max(dbo.Respondent.DateCreated) AS MaxOfDateCreated, " & _
"Max((dbo.Respondent.Complete+0)) AS Complete, " & _
"(Case " & _
" When Max(dbo.StatusCode.StatusCodeID)=12 then 12 When Max(dbo.StatusCode.StatusCodeID)=11 then 11 When Max(dbo.StatusCode.StatusCodeID)=10 then 10 When Max(dbo.StatusCode.StatusCodeID)=9 then 9 When Max(dbo.StatusCode.StatusCodeID)=8 then 8 When Max(dbo.StatusCode.StatusCodeID)=6 then 6 " & _
" When Max(dbo.StatusCode.StatusCodeID)=5 then 5 When Max(dbo.StatusCode.StatusCodeID)=4 then 4 When Max(dbo.StatusCode.StatusCodeID)=3 then 3 When Max(dbo.StatusCode.StatusCodeID)=2 then 2 When Max(dbo.StatusCode.StatusCodeID)=7 then 1 When Max(dbo.StatusCode.StatusCodeID)=1 then 1 " & _
"End) " & _
"AS StatusCodeList, " & _
"'' As Description, " & _
"'' As DescFull, " & _
"Max(dbo.Respondent.QHispanic) As MaxOfQHispanic " & _
"INTO tmp_WhyTerm " & _
"FROM (dbo.Respondent INNER JOIN dbo.RespondentStatus ON dbo.Respondent.RespondentID = dbo.RespondentStatus.RespondentID) INNER JOIN dbo.StatusCode ON dbo.RespondentStatus.StatusCodeID = dbo.StatusCode.StatusCodeID " & _
"WHERE (((dbo.Respondent.StudyID)='" + StudyID + "') AND ((dbo.Respondent.StudyWaveID)='" + StudyWaveID + "')) " & _
"GROUP BY dbo.Respondent.code1 " & _
"ORDER BY Max((dbo.Respondent.Complete+0)) DESC"
CurrentDb.QueryDefs("Query1").SQL = stringSQL
DoCmd.OpenQuery "Query1", acNormal, , , acFormEdit

End Function

===================================

I set the two temp variables in the macro and do the following step:

RunCode
Function Name WhyTerminate([TempVars]![StudyID],[TempVars]![StudyWaveID])

=======================

Any thoughts?

[JoeM]

After you create stringSQL in your code, do what I suggested and return it to a MessageBox, i.e.

MsgBox stringSQL

Run it and inspect the code that comes up in your Message Box, particularly the parts where you are trying to put your selections.

Note, I do not think you have set up the arguments in your Function correctly.
The function declaration should probably look something like this:

Function Name WhyTerminate(StudyID,StudyWaveID)
...
End Function

Those arguments can actually be named anything, it doesn't matter. Those are generic variables. You don't pass in the Table Name references until you call the Function from wherever you are calling it.

By the way, I have never done this as a function before, I have always done it as a Procedure (Functions usually return a value or something). So if that doesn't work, try changing it from a Function to a Procedure.

[Scyclonic]

Hmm...

Now running into a type mismatch error... though nothing seems out of place on it. What a day... lol