Access SQL Sting - copy into an Excel VBA format?

[djspod]

Hi,



I have created an Excel connection to my Access Database.

I'm in the middle of learning VBA for both programs and so far the connection seems to work well and connect in Excel to my database by a basic SQL query.

SELECT Training comp,Training ref,Name; FROM Training comp;" This works fine.

I get the above by making a Select query in design view then switching to SQL view and making a copy.

However when I make the query more advanced with Where and Inner joins etc, I can't seem to get VBA to behave, the text goes Red and complains of various of syntax errors etc etc

What do I need to do with the below to make VBA accept it as a valid usable string? The SQL example below.

Thanks in advance

SELECT Training.Name, Training.[Training ref], Training.Date, staff.Eastwood, staff.[Despatch Op]
FROM staff INNER JOIN Training ON staff.Name = Training.Name
WHERE (((staff.Eastwood)=True) AND ((staff.[Despatch Op])=True));

	
	
Const RANGE_REP_CLEAR As String = "A1:AD500"
Const FOOD_HEADER_CELL_START As String = "A1"
Const QUERY_FOOD_TYPE_CELL As String = "F3"
Const QUERY_FOOD_TYPE_ALL As String = "ALL"
Const constrAccess As String = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\darre\Documents\Training Test\Sales Training Database.accdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False"


Const constrSQL As String = "SELECT Training,Name,Training ref,Date,staff,Eastwood,Despatch Op &_"
FROM staff INNER JOIN Training ON staff.Name = Training.Name &_
WHERE staff, Eastwood = True AND staff,Despatch Op = True"_






'"SELECT Training comp,Training ref,Name; FROM Training comp;"


Public Sub ReadFoodsFromDB()


''Turn off functionality such as auto calculations,events and screen updating


 TurnOffFunctionality
 
 'Clear report area
  ClearReportArea
  
  'Read Database Data
  ReadDBData ThisWorkbook.Path & "\S1 Test.accdb"
  
  'cnFood Activate
  cnFood.Activate
  
  'Display success message
     
  MsgBox "Read DB Data"
 
 ''Turn on functionality such as auto calculations,events and screen updating
 TurnOnFunctionality
End Sub


Private Sub ReadDBData(ByVal sDatabase As String, Optional ByVal sPassword As String = "")
Dim Connection As ADODB.Connection
Set Connection = ConnectToDB(sDatabase, sPassword)


Dim sFoodType As String
sFoodType = cnQuery.Range(QUERY_FOOD_TYPE_CELL)


Dim sQuery As String
sQuery = " SELECT Training.Name, Training.[Training ref]FROM Training"








If StrComp(sFoodType, QUERY_FOOD_TYPE_ALL, vbTextCompare) <> 0 Then
sQuery = sQuery & " WHERE Name = """ & sFoodType & """"


End If


Dim Recordset As New ADODB.Recordset
Recordset.Open sQuery, Connection
cnFood.Range("A1").Offset(1).CopyFromRecordset Recordset


    Dim col As Long
    For col = 0 To Recordset.Fields.Count - 1
    cnFood.Range("A1").Offset(0, col) = Recordset.Fields(col).Name
    Next
    


Connection.Close




End Sub
Public Function ConnectToDB(ByVal sDatabase As String, Optional ByVal sPassword As String = "") As ADODB.Connection
On Error GoTo eh
Dim Connection As New ADODB.Connection
Dim sConnectionString As String
If Dir(sDatabase) = "" Then
MsgBox "Cannot find the database file"
Exit Function
End If
If sPassword = "" Then
sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & sDatabase & "; Persist Security Info=False;"
Else
sConnectionString = "Provider =Microsoft.ACE.OLEDB.12.0;  & Data Source =  & sDatabase" & "; Jet OLEDB:Database Password=" & sPassword & ";"
                             
    End If
    Connection.Open sConnectionString
    Set ConnectToDB = Connection
Done:
    Exit Function
eh:
MsgBox Err.Description & "Database.ConnectToDB"
End Function

[June7]

So many errors, hope I caught all. Note there is a space between & and _. Each continued line between quote marks with a space at the end.

Const constrSQL As String = "SELECT Training.[Name], Training.refDate, staff.Eastwood, [Despatch Op] " & _
"FROM staff INNER JOIN Training ON staff.[Name] = Training.[Name] " & _
"WHERE staff.Eastwood = True AND staff.[Despatch Op] = True;"

Name is a reserved word and should avoid reserved words as names. Also, advise no spaces or punctuation/special characters (underscore only exception) in naming convention.

[djspod]

Hi June,

Thanks for the reply, thats exactly what I was trying to acheive and your format and help works much better in VBA.

However I still get an error which could be related to my access query I'm not sure?

[June7]

Names with spaces or punctuation or are reserved words need []. Date is also a reserved word.

SELECT Training.[Name], [Training ref], [Date], staff.Eastwood, [Despatch Op]

[pbaldy]

Here's a valuable tool to debug SQL problems:

http://www.baldyweb.com/ImmediateWindow.htm

[Missinglinq]

You need Square Brackets around Training ref in the statement:

[Training ref]

Linq ;0)>

[djspod]

Hi Both,

many thanks for your help.

I'm gradually getting there, just the end of the SQL string that is failing now as below but I'm not sure why?

"SELECT Training.[Name],[Training ref],[Date], staff.[Eastwood],[Despatch Op] " & _"FROM staff INNER JOIN Training ON staff.[Name] = Training.[Name] " & _
"WHERE staff.[Eastwood] = True AND staff.[Despatch Op] = True"

[djspod]

Also thank you Pbaldy, checking the link out now.

Thanks

[pbaldy]

In the code for Name, you want AND not WHERE.

[djspod]

I'm not at this point?

[pbaldy]

I said in the code for Name, in the If/Then block.

[djspod]

JOIN expression not supported??

Thanks for the help so far ALL

Dim sFoodType As StringsFoodType = cnQuery.Range(QUERY_FOOD_TYPE_CELL)


Dim sQuery As String
sQuery = "SELECT staff.StaffName, Training.[Training ref], Training.TrainingDate, staff.Eastwood, staff.[Despatch Op]" & _
"FROM staff INNER JOIN Training ON staff.StaffName = Training.TrainingName" & _
"WHERE (((staff.Eastwood)=True) AND ((staff.[Despatch Op])=True))"


Debug.Print sQuery


If StrComp(sFoodType, QUERY_FOOD_TYPE_ALL, vbTextCompare) <> 0 Then
sQuery = sQuery & " AND Name = """ & sFoodType & """"


End If


Dim Recordset As New ADODB.Recordset
Recordset.Open sQuery, Connection
cnFood.Range("A1").Offset(1).CopyFromRecordset Recordset


    Dim col As Long
    For col = 0 To Recordset.Fields.Count - 1
    cnFood.Range("A1").Offset(0, col) = Recordset.Fields(col).Name
    Next
    


Connection.Close




End Sub

[pbaldy]

Does the debug show a space before WHERE?

[June7]

Need a space before the quote at the end of each continued line, as shown in post 2. The spaces were in earlier versions of the statement but somehow lost them in the last. Also, put a semi-colon at the end of the statement. It was also there in earlier version.

[pbaldy]

Also, put a semi-colon at the end of the statement.

In my experience, the semi-colon is optional. I never include it in my SQL statements.