Querying Access From Excel

[Ken Puls]

Hi there,

I picked up some code orginally posted by Lasw10 from this site, and modified it to suit my needs. It worked fine when I was working on Office 2003, but I'm having some issues with it on 97...

Here's the situation:

I have a point of sale database which uses an Access 2000 database as its back end. (Front end is written in VB6). I'm trying to pull an SQL query from that database via Excel, since the SWL query is too complex for MSQuery to handle. (It can't fit paramaters into it for some reason.)

On my PC, I use Office 2003 on Windows XP, but on the machine that will be running it, we use Office 97 on Windows 2000 server.

The code below runs fine on my machine, but bombs on the very last line with an Automation Error when run on the Windows 2000 machine. The full error is "Run time Error '430': Class doesn't suport automation"

Private Sub cmdQueryFore_Click()
Dim sql
Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='\\Fw_citrix\4RESERVE\Training\4reserve.mdb'"
Dim startdate As String, enddate As String

startdate = "#" & Format(Worksheets(1).Range("B1"), "mm/dd/yyyy") & "#"
enddate = "#" & Format(Worksheets(1).Range("B2"), "mm/dd/yyyy") & "#"

Worksheets("Sheet1").Range("A5:D" & Worksheets("Sheet1").Range("C65536").End(xlUp).Row).ClearContents

sql = "SELECT DISTINCT Schedule.ReservationDate, (select count(a.reservationdate)" & _
" from schedule a where a.reservationdate = schedule.reservationdate and" & _
" isnull(a.ghin1))+(select count(a.reservationdate) from schedule a where" & _
" a.reservationdate = schedule.reservationdate and isnull(a.ghin2))+(select" & _
" count(a.reservationdate) from schedule a where a.reservationdate =" & _
" schedule.reservationdate and isnull(a.ghin3))+(select " & _
" count(a.reservationdate) from schedule a where a.reservationdate = " & _
" schedule.reservationdate and isnull(a.ghin4)) AS Available, (select " & _
" count(a.reservationdate) from schedule a where a.reservationdate = " & _
" schedule.reservationdate and isnull(a.ghin1)=false)+(select " & _
" count(a.reservationdate) from schedule a where a.reservationdate = " & _
" schedule.reservationdate and isnull(a.ghin2)=false)+(select " & _
" count(a.reservationdate) from schedule a where a.reservationdate = " & _
" schedule.reservationdate and isnull(a.ghin3)=false)+(select " & _
" count(a.reservationdate) from schedule a where a.reservationdate = " & _
" schedule.reservationdate and isnull(a.ghin4)=false) AS Booked, [available]+[booked] AS Total " & _
" from Schedule " & _
" WHERE (((Schedule.ReservationDate)>=" & startdate & " And (Schedule.ReservationDate)<=" & enddate & "));"

Set RS = Conn.Execute(sql)

Worksheets("Sheet1").Cells(5, 1).CopyFromRecordset RS

End Sub

Is this a compatibility issue using 97 on a 2000 database? Or is there another way to get it out?

Thanks,

 

[Andrew Poulsom]

Look here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;246335&Product=xlw

 

[Ken Puls]

Thanks for the link Andrew. That looks like exactly what I'm looking for!

Cheers,