3

I am attempting to execute a SQL query inside of VBA Code. The query works in MS Access and asks the user to input a value for Customer_Name and Part_Number

What I have done is written the VBA Code in outlook so we can run the macro to execute the query from Outlook. The code I have currently works until the very bottom line on the DoCmd.RunSQL portion. I think I have this syntax incorrect. I need to tell it to run the string of SQL listed above: 

Public Sub AppendAllTables()
Part_Number = InputBox("Enter Part Number")
Customer_Name = InputBox("Enter Customer Name")
Dim strsqlQuery As String
Dim Y As String
Y = "YES, Exact Match"
Dim P As String
P = "Possible Match - Base 6"
Dim X As String
X = "*"

    strsqlQuery = "SELECT Append_All_Tables.Customer, 
    Append_All_Tables.CustomerCode, Append_All_Tables.PartNumber, 
    Append_All_Tables.Description, Append_All_Tables.Vehicle, SWITCH" & 
    Customer_Name & " = Append_All_Tables.PartNumber, " & Y & ", LEFT(" & 
    Part_Number & ",12) = LEFT(Append_All_Tables.PartNumber,12)," & Y & ", 
    LEFT(" & Part_Number & ",6) = LEFT(Append_All_Tables.PartNumber,6)," & P 
    & ") AS Interchangeability FROM Append_All_Tables WHERE" & Customer_Name 
    & "Like " & X & Customer_Name & X & "AND 
    LEFT(Append_All_Tables.PartNumber,6) = LEFT(" & Part_Number & ",6);"

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "path.accdb"
appAccess.DoCmd.RunSQL "strsqlQuery"

End Sub

Please note, the path has been changed for privacy. The SQL code already works in Access. I am only needing the last line to be evaluated.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
ClaireLandis
  • 325
  • 3
  • 18
  • 2
    That's a `SELECT` statement. What do you expect to happen when you run it? – Erik A May 09 '18 at 19:08
  • I am attempting to have the datasheet view (list of all records meeting these requirements) appear in access. The code is supposed to open access, and run the query based on the sql in the vba code – ClaireLandis May 09 '18 at 19:17
  • 1
    Well... You can't just show a datasheet filled from an SQL statement. You can create a new query, and open it in datasheet view, or edit an existing query, and open that. Or, if you want to do really fancy things, you can have a form that can display any SQL statement in datasheet view (something like I shared [here](https://stackoverflow.com/a/49551221/7296893)), but that's quite complex. – Erik A May 09 '18 at 19:21

3 Answers3

2

If you want to have a datasheet form view show these records you can use 

DoCmd.OpenForm

First create a query with the data you want to see, then bind that to your form using the Record Source property, then when you call DoCmd.OpenForm pass in the filter you want.

I'm not following what you're trying to do with SWITCH in your query (is that supposed to be the switch() function? it has no parentheses). But you'll need to adjust that to join to use a Where statement instead.

Brad
  • 11,934
  • 4
  • 45
  • 73
2

I agree with a couple of the above posts.

  1. You need to do a Debug.Print of the strsqlQuery variable BEFORE YOU DO ANYTHING! Then evaluate that statement. Does it look right? As Matt says, it doesn't look like you have line continuations, which would make your SQL statement incomplete (and thus, the computer doesn't think its a query at all).

My personal preference is to define the SQL like you have, then create the actual query using that SQL (create query def), and then call that query, because it will now be an actual object in the database. The QUERY can show up as a datasheet without any form requirement, but a pure SQL Statement cannot.

Michael

The Special
  • 41
  • 7
1

Remove the quotes.

appAccess.DoCmd.RunSQL "strsqlQuery" to appAccess.DoCmd.RunSQL strsqlQuery

ccarpenter32
  • 1,058
  • 2
  • 9
  • 17
  • I am still getting the error. the debugger has highlighted this line again even with the change.. The error says, "A runSQL action requires an argument consisting of an SQL statement." Do you know if I am not allowed to declare it as a string above and call it down here? – ClaireLandis May 09 '18 at 19:09
  • 1
    @Analyst123456789 if your string is assigned exactly the way you have it in your post, then I don't see how your code can even compile; it's missing line continuations ` _`. – Mathieu Guindon May 09 '18 at 20:09
  • It does contain line continuations, but I took them out of this so that I could write it continually on one line. The string itself is working, its just the last line that is having the issue. I have also attempted to use appAccess.DoCmd.Execute with an update sql query, but that did not work either. – ClaireLandis May 09 '18 at 21:39
  • 1
    Your SQL string as posted will not work. [How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) . Please post working code and the result of `Debug.Print strsqlQuery` @Analyst123456789 – Andre May 10 '18 at 07:24