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
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