Please help me retrieve data from Access using Excel VBA 2

[beetlebailey]

I am trying to retrieve information from an Access database using the following SQL statement from within Excel VBA. This works fine:

Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\temp\scelestus.mdb" & ";" & _
"Persist Security Info=False"
conn.Open
SQLc = "Select [scheduled qty] from [tbl TTR Data] where [time slot]=1 and [TTRN]=1"
Set rs = conn.Execute(SQLc, , adCmdText)
H1 = rs.Fields("scheduled qty").Value: .Range("H3").Value = H1
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

This does not, I need to sum the various values from the scheduled qty column first, any ideas:

Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\temp\scelestus.mdb" & ";" & _
"Persist Security Info=False"
conn.Open
SQLc = "Select Sum[scheduled qty] as 'sq' from [tbl TTR Data] where [time slot]=1 and [TTRN]=1"
Set rs = conn.Execute(SQLc, , adCmdText)
H1 = rs.Fields("sq").Value: .Range("H3").Value = H1
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

Thank you very much

 

[GlennUK]

You'd be better off to post this in the VBA Visual Basic for Applications (Microsoft) forum. Will look at it if I get time today.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

well - for starters, SUM needs brackets:
I would also put some round your WHERE clause as well for good practice

SQLc = "Select Sum([scheduled qty]) as 'sq' from [tbl TTR Data] where ([time slot]=1 and [TTRN]=1")


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RoyVidar]

Try:
[tt]"Select Sum([scheduled qty]) as sq from [tbl TTR Data] where [time slot]=1 and TTRN=1"[/tt]

No need for any quotes on the alias, in fact, they may have produced the/one error - and should you also visit one of the seven dedicated Access fora, you'll also hear something about avoiding spaces and special characters in table/fields (and other object) names ;-)

[tt]"select sum(ScheduledQty) from tblTTRData where TimeSlot = 1 and TTRN = 1"[/tt]

Roy-Vidar