Querying Access From Excel

Ken Puls

Active Member
Joined
Jun 9, 2003
Messages
482
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"

Code:
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,
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Ken Puls

Active Member
Joined
Jun 9, 2003
Messages
482
Thanks for the link Andrew. That looks like exactly what I'm looking for!

Cheers,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top