VBA/Excel SQL Query

Thanks!

Okay i gave that solution a go. Gives me a new error now.

Compile Error: User-defined type not defined. Highlights this line as the problem.
Public Sub HaweraSales(objForm As Form)

Any ideas?

Alright, i think i found a solution to my above issue. As in setting a reference to "Microsoft ActiveX Data Objects 2.7.

As seen here: http://p2p.wrox.com/topic.asp?TOPIC_ID=40704

Except when i go to select that particular reference it tells me : Name Conflicts with Existing Module, Project, or Object Library.

I then renamed the project via Tools -> VBAProject Properties to something different. Then went to reselect the Microsoft ActiveX Data Objects 2.7, and it still gives me the same Name conflict error. Any ideas?

Thanks for the help

I have solved the naming conflict error. Still giving me a User Type not defined Error however. Even after i have set the reference.

The break is once again on this line.

Public Sub HaweraSales(objForm As Form)

I'll paste all my code below:

Public Sub HaweraSales(objForm As Form)

    Dim obj As Object
    Dim rec As ADODB.Recordset
    Set obj = CreateObject("ADODB.Connection")
   
    obj.Open "dsn=p615;"
   
    obj.BeginTrans
    obj.Execute ("select spddate, sphprodno, sum(spdvalue) sales, sum(spdgrossprofit) profit from salesbyperiod where sphwhseno = " & Chr(34) & "HWF" & Chr(34) & " and sphdepartment = " & Chr(34) & "50" & Chr(34) & " and spddate >=" & Chr(34) & objForm.txtDate & Chr(34) & "  and spddate <='" & Chr(34) & objForm.txtDate1 & Chr(34) & " group by 1,2 into temp a1 with no log")
    obj.Execute ("select (spddate) fdate, sum(sales) fsales, sum(profit) fprofit from a1 group by 1 into temp a2 with no log")
    obj.Execute ("select spddate, sphprodno, sum(spdvalue) sales, sum(spdgrossprofit) profit from salesbyperiod where sphwhseno = " & Chr(34) & "HWF" & Chr(34) & "   and sphdepartment != " & Chr(34) & "50" & Chr(34) & "   and spddate >='" & objForm.txtDate & Chr(34) & "  and spddate <='" & objForm.txtDate1 & Chr(34) & " group by 1,2 into temp b1 with no log")
    obj.Execute ("select (spddate) mdate, sum(sales) msales, sum(profit) mprofit from b1 group by 1 into temp b2 with no log")
    obj.Execute ("select ohorddmy, count(ohintref) invcount from ohhist where ohwhse = " & Chr(34) & "HWF" & Chr(34) & "   and ohorddmy >='" & objForm.txtDate & Chr(34) & "  and ohorddmy <='" & objForm.txtDate1 & Chr(34) & " group by 1 into temp c1 with no log")
   
   obj.CommitTrans
 
   
    Set rec = New ADODB.Recordset
    rec.ActiveConnection = obj
    rec.Source = "select unique spddate, fsales, fprofit, msales, mprofit, invcount from salesbyperiod, outer a2, outer b2, outer c1 where spddate = fdate   and spddate = mdate   and spddate = ohorddmy   and spddate >= " & Chr(34) & objForm.txtDate & Chr(34) & "  and spddate <= " & Chr(34) & objForm.txtDate1 & Chr(34) & "  order by 1"
    rec.CursorType = 0
    rec.CursorLocation = 2
    rec.LockType = 1
    rec.Open
   
        'Get the worksheet

    Workbooks("Hawera Daily Sales Comparison May 07.xls").Worksheets("sheet2").Activate
    'Fill in the headers
    For Count = 1 To rec.Fields.Count
        Workbooks("Hawera Daily Sales Comparison May 07.xls").Worksheets("sheet2").Cells(2, Count).Value = rec.Fields.Item(Count - 1).Name
    Next
   
    Dim current_row
    current_row = 3
    rec.MoveFirst
     Do
        For Count = 1 To rec.Fields.Count
        Workbooks("Hawera Daily Sales Comparison May 07.xls").Worksheets("sheet2").Cells(current_row, Count).Value = rec.Fields.Item(Count - 1).Value
        Next
        rec.MoveNext
        current_row = current_row + 1
     Loop While Not rec.EOF
     Sheets("sheet1").Select
End Sub

I'll Post the above as a new question.

Thanks