Run time Error 3709

[hsk]

Sub getdata()
cur_mon = Date - (Weekday(Date, 2) - 1)
cur_fri = Date + (5 - Weekday(Date, 2))

mondt = Year(cur_mon) & "-" & Day(cur_mon) & "-" & Month(cur_mon) & " 00:00:00"
fridt = Year(cur_fri) & "-" & Day(cur_fri) & "-" & Month(cur_fri) & " 00:00:00"

mydb = ............... (my sheet name)
mypath = ...... (path C:\ ...etc) & mydb

' Connet to database
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
    .Provider = "MSDASQL"
    .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & mypath & "; ReadOnly=False;"
    .Open
End With

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM [Sheet1$]"
cn.Close
Set cn = Nothing
End Sub

I am trying to query a sheet , getting run time error 3709 on the line rs.Open.
any help ?

 

[Richard Schollar]

You need to tell it which connection to use:

rs.Open "SELECT * FROM [Sheet1$]", cn

 

[Andrew Poulsom]

This may help you:

http://support.microsoft.com/kb/257819#RetrieveExcel

 

[hsk]

oohh, thanks .....

It's working now ....

When I display rs.RecordCount it displays -1 ...

My sheet is having records ...

When i tried -
rs.Open "SELECT * FROM [Sheet1$]", cn, adOpenStatic, adLockReadOnly

rs.RecordCount is zero ....

Is it not selecting any records?

 

[Richard Schollar]

You don't appear to be doing anything with the recordset returned? If you want to copy it to a sheet the easiest way is to use the CopyFromRecordset method:

Activesheet.Range("A2").CopyFromRecordset rs

 

[hsk]

Thanks Richard,

Actually my requirement is .....

- Fetch Records from DB with diff criteria
(want to select few fields not select *)
- Reformat Record and put the values in sheet
(I already have a template, just want to put the values from cursor into it)
I would want to handle each field in each record individually, as i may add few more validations before putting it in template.

Also
I want to add WHERE clause to this query on some Date Fields.

e.g. I am having a column as Planned Date (in mydb)-
value is 16th Nov.
The cell is in Custom format "m\d\yyyy h:mm"
Hence the date appears as "11/16/2007 0:00"
While the Formula bar shows "11/16/2007 12:00:00 AM"

Now how can i put sql query which will fetch record for planned date = 16 Nov.

 

[Richard Schollar]

You need to write the SQl as:

"SELECT * FROM [Sheet1$] WHERE DateField = #11/16/2007#"

Amend the DateField for whatever the column header name is that contains the date you want to filter on.

 

[hsk]

Finally ...... I get that working !!!

Thanks Richard, thanks a lot !!