Access SQL Sting - copy into an Excel VBA format?

[June7]

I always knew it's not needed for action (DELETE, INSERT, UPDATE) statements but I always played it safe for SELECT because thought I had encountered issues with omitting it in the past. But just tested and can't cause an error without it, so agreed, it is not needed even for SELECT.

[djspod]

Thanks for the help and suggestions, I'm really learning loads via a trial and error method, changing the code and query etc. and also using your suggestions.

I've added a space at the end of each statement, If I add the semi colon it generates a character at the end of the statement error.

I removed them but left the space, I now get the error below.

Debug stops on the

The query works fine in Access.



[CODEOption Explicit

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="""";U ser ID=Admin;Data Source=C:\Users\darre\Documents\Training Test\S1 Test.accdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=6;Jet OLEDBatabase 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 OLEDBon'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 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.[Training ref], Training.[Date], Training.[Name], staff.[Eastwood], staff.[Despatch Op] " & _
"FROM staff Inner Join Training ON staff.Name = Training.Name " & _
"WHERE (((staff.Eastwood)=True) AND ((staff.[Despatch Op])=True)); "




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 OLEDBatabase Password=" & sPassword & ";"

End If
Connection.Open sConnectionString
Set ConnectToDB = Connection
Done:
Exit Function
eh:
MsgBox Err.Description & "Database.ConnectToDB"
End Function








Public Sub WriteFoodTypesToDB()


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


TurnOffFunctionality


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

TurnOnFunctionality


Worksheets.Add
'Range("A2").CopyFromRecordset MyDBData
' MyDBData.Close




' MyDB.Close
End Sub








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


Private Sub TurnOffFunctionality()
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.ScreenUpdating = False
End Sub


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


Private Sub TurnOnFunctionality()
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


'Clear and existing report or data from the report range
Public Sub ClearReportArea()
cnFood.Range(RANGE_REP_CLEAR).ClearContents
cnFood.Range(RANGE_REP_CLEAR).ClearFormats
End Sub




Public Sub ReadFromDB()


Dim MyDB As ADODB.Connection
Dim MyDBData As ADODB.Recordset
Set MyDB = New ADODB.Connection
Set MyDBData = New ADODB.Recordset


MyDB.ConnectionString = constrAccess


MyDB.Open


With MyDBData


.ActiveConnection = MyDB
.Source = "Foods"
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly


End With


End Sub


























][/CODE]

[pbaldy]

If you use the debug method I posted, you'll see what I'm talking about. You have 2 WHERE; the second should be AND. You can't have 2 WHERE.

[ssanfu]

For the bottom error:
In Private Sub ReadDBData, there is this code:

    sQuery = " SELECT Training.[Training ref], Training.[Date], Training.[Name], staff.[Eastwood], staff.[Despatch Op] " & _
             "FROM staff Inner Join Training ON staff.Name = Training.Name " & _
             "WHERE (((staff.Eastwood)=True) AND ((staff.[Despatch Op])=True));"

There is a semicolon at the end of the SQL for "sQuery". This ends the SQL!
Then there is this code immediately following the above code:

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

NO SQL string can have 2 WHERE clauses!!

Add a debug statement below the code to see the SQL and set a breakpoint:

[djspod]

Hi All,

Sorry for the late reply as I have been working nights the last 4 nights.

Many thanks to all your collective answers and support, I have learnt a lot and used some of the suggestions to get a solution.

The code is now working fine!

Thanks

Darren