Query syntax in VBA headbanger (1 Viewer)

Lol999

Registered User.
Local time
Today, 08:21
Joined
May 28, 2017
Messages
184
I'm trying to execute a query, or at least this point a string of sql on an Access table from within Excel vba.
A forum member kindly gave me a code to copy some data from excel to access to a temporary table.
Now I would like to copy that data into it's permanent home table within the db.
I'm trying to execute an SQLstring but keep getting a syntax error. I know vba can be fussy with brackets etc and despite the original being created in an Access query builder it was still chucking an error (syntax).

I've stripped away a lot of the what I have found in the past to be superfluous brackets etc but I'm still getting the error.
if someone could point out my idiot error that would be great!
code below, the sql I am trying to execute is strSQL2
Many thanks, lol

Code:
Private Sub Button2_Click()
Dim intStartRow As Integer
Dim intEndRow As Integer
Dim i As Integer
Dim adoCon
Dim adoRs
Dim strSQL As String
Dim strSQL2 As String

Dim strDBName As String
Dim strMyPath As String
Dim strDB As String

strDBName = "Data Export Trial.accdb"
strMyPath = "c:\Users\Lol\Desktop\"
strDB = strMyPath & "" & strDBName

Set adoCon = CreateObject("ADODB.Connection")
adoCon.Open "Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = " & strDB & ";" & _
"Persist Security Info = False;"

strSQL = "SELECT * FROM Tbl_Trial;"
strSQL2 = "INSERT INTO Tbl_Costing ([Job Code], EmpName, HoursWorked, WeekEnding)" & _
"SELECT Tbl_Trial.[Job Code], Tbl_Trial.EmpName, Tbl_Trial.HoursWorked, Tbl_Trial.WeekEnding" & _
"FROM Tbl_Trial INNER JOIN (Tbl_Job_Code ON Tbl_Trial.[Job Code] = Tbl_Job_Code.[Job Code])" & _
"WHERE (Tbl_Trial.[Job Code] > "")" & _
"GROUP BY Tbl_Trial.[Job Code], Tbl_Trial.EmpName, Tbl_Trial.HoursWorked, Tbl_Trial.WeekEnding" & _
"HAVING (Tbl_Trial.EmpName)>"";"

Set adoRs = CreateObject("ADODB.Recordset")

'Set the cursor type we are using so we can navigate through the recordset
adoRs.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
adoRs.LockType = 3

'Open the tblComments table using the SQL query held in the strSQL varaiable
adoRs.Open strSQL, adoCon


'This is your starting row and ending row from
'the code you posted
intStartRow = 5
intEndRow = 58

For i = intStartRow To intEndRow
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!
' WARNING
' be sure not to include AutoNumber field in your update
'
' In my example I exclude adoRS(0), I was thinking
' maybe this is AutoNumber field, so I started
' with adoRS(1), the second field.
' adjust as necessary!

'Tell the recordset we are adding a new record to it
adoRs.AddNew
adoRs(0).Value = Cells(i, 1) 'Column 1=A
adoRs(1).Value = Cells(i, 3) 'Column 3=C
adoRs(2).Value = Cells(i, 11) 'Column 11=K
adoRs(3).Value = Cells(i, 12) 'Column 12=L
adoRs.Update
Next

adoCon.Execute strSQL2
adoRs.Close
adoCon.Close
Set adoRs = Nothing
Set adoCon = Nothing

End Sub
 

MarkK

bit cruncher
Local time
Today, 08:21
Joined
Mar 17, 2004
Messages
8,178
Print this variable to the immediate pane, copy the SQL printed there into a new query, and see what the query designer tells you about error...
Code:
strSQL2 = "INSERT INTO Tbl_Costing ([Job Code], EmpName, HoursWorked, WeekEnding)" & _
"SELECT Tbl_Trial.[Job Code], Tbl_Trial.EmpName, Tbl_Trial.HoursWorked, Tbl_Trial.WeekEnding" & _
"FROM Tbl_Trial INNER JOIN (Tbl_Job_Code ON Tbl_Trial.[Job Code] = Tbl_Job_Code.[Job Code])" & _
"WHERE (Tbl_Trial.[Job Code] > "")" & _
"GROUP BY Tbl_Trial.[Job Code], Tbl_Trial.EmpName, Tbl_Trial.HoursWorked, Tbl_Trial.WeekEnding" & _
"HAVING (Tbl_Trial.EmpName)>"";"
[COLOR="Blue"][B]debug.print strSQL2[/B][/COLOR]
...but one glaring error is lack of spaces at the line continuations. Also, these quotes look wrong...
Code:
"WHERE (Tbl_Trial.[Job Code] > [B][COLOR="Blue"]"")"[/COLOR][/B]
...but in general, when you construct SQL on the fly, print it and test it in the query designer.
hth
Mark
 

static

Registered User.
Local time
Today, 15:21
Joined
Nov 2, 2015
Messages
823
I don't think you need a WHERE and a HAVING.
If you group records you use HAVING. Remove the WHERE.

This line doesn't make sense.
"HAVING (Tbl_Trial.EmpName)>"";"
> is usually used with numbers not text. I guess you want <>. Also if you use double quotes in a string you need to double them up.

If EmpName is NULL it wont be '' so you also need to convert it to string.

HAVING nz(Tbl_Trial.EmpName,'')<>''
 

plog

Banishment Pending
Local time
Today, 10:21
Joined
May 11, 2011
Messages
11,613
I don't think you need a WHERE and a HAVING.
If you group records you use HAVING. Remove the WHERE.

In general you can have both in an SQL statement. In this specific instance you only should have a WHERE.

HAVING works on the aggregate data (SUM, COUNT, MAX, etc.), WHERE works on individual records. Since both your criteria are on individual records, all your criteria should be in WHERE.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:21
Joined
Feb 19, 2013
Messages
16,555
in addition to other comments, also looks like you are missing some spaces between lines

Code:
strSQL2 = "INSERT INTO Tbl_Costing ([Job Code], EmpName, HoursWorked, [COLOR=red]WeekEnding)" & _
"SELECT[/COLOR] Tbl_Trial.[Job Code], Tbl_Trial.EmpName, Tbl_Trial.HoursWorked, [COLOR=red]Tbl_Trial.WeekEnding" & _
"FROM[/COLOR] Tbl_Trial INNER JOIN (Tbl_Job_Code ON Tbl_Trial.[Job Code] = Tbl_Job_Code.[[COLOR=red]Job Code])" & _
"WHERE[/COLOR] (Tbl_Trial.[Job [COLOR=red]Code] > "")" & _
"GROUP[/COLOR] BY Tbl_Trial.[Job Code], Tbl_Trial.EmpName, Tbl_Trial.HoursWorked, [COLOR=red]Tbl_Trial.WeekEnding" & _
"HAVING[/COLOR] (Tbl_Trial.EmpName)>"";"
use

debug.print strSQL2 to see what the string looks like - and copy paste to a new query to see if it runs
 

Lol999

Registered User.
Local time
Today, 08:21
Joined
May 28, 2017
Messages
184
Okay folks I got it sorted, and it was CJ London who provided the clue: I forgot to add spaces into the string when breaking it down into chunks :banghead:

I would like to make a few points though:

The sql as it stands was generated by the query builder in Access, and not being over proficient at sql I have to rely on it like a lot of others do so my sql etiquette or grammar is not always "Queen's English" but hey, blame MS because they built the damned thing.

As ever I'm grateful for the help, many thanks, Lol
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:21
Joined
Feb 19, 2013
Messages
16,555
The sql as it stands was generated by the query builder in Access
which builds as you tell it.

WHERE (Tbl_Trial.[Job Code] > "")"

tells my you have put the criteria in a separate column with WHERE instead of GROUP BY which is correct, you use the WHERE to limit the number of records to be grouped, makes for a faster query

HAVING (Tbl_Trial.EmpName)>"";"

tells me you have put this in a column which is grouped - so you are returning all records, then grouping them then using criteria to limit the number of grouped records returned - which makes for a slower query. As plog says, HAVING should only be used when you need to apply a criteria to a sum, count etc
 

Lol999

Registered User.
Local time
Today, 08:21
Joined
May 28, 2017
Messages
184
Can you suggest a good book or resource for learning "proper" sql please?
I always used the Dummies books in the past and found them quite good for other things.
 

isladogs

MVP / VIP
Local time
Today, 15:21
Joined
Jan 14, 2017
Messages
18,186
Not a book - but here's a utility which you may find useful when trying to change a query into its SQL equivalent (or vice versa)
SQL to VBA and back again
 
Last edited:

Lol999

Registered User.
Local time
Today, 08:21
Joined
May 28, 2017
Messages
184
HAVING nz(Tbl_Trial.EmpName,'')<>''

Just learned the hard way that Excel, from where I am driving this thing, doesn't like the Nz function. Might save you some grief in the future :D
 

Users who are viewing this thread

Top Bottom