ADO Connect Access

[gti_jobert]

Hi all,

I'm not sure if this is the right forum to post in as my Q may b
MSAccess related. I am using an ADO connection to retieve data from a
Access database using Excel VBA - this seems to be functioning properl
(I am able to get data as required).

My problem is modifying my SQL statement - currently I have;


Code
-------------------

.Open "SELECT [Part No], [Batch Qty] FROM [" & TableName & "] " & _
" WHERE [Part No] = '01801-00408'", cn, , , adCmdText

-------------------


The above is just a test to see weather or not it worked (and it does)
I want to be able to select the data based on week numbers. The curren
[Date] is in format '24/03/2006' - how do I modify this statement?

TIA, any ideas appreciated......and again someone here may know (bob??

 

[Guest]

You could use the VBA DatePart function: DatePart("ww", your date)

 

[gti_jobert]

I've been triyng that;


Code:
--------------------

Function VBAWeekNum(D As Date, FW As Integer) As Integer
VBAWeekNum = CInt(Format(D, "ww", FW))
End Function

 

[gti_jobert]

This is the entire Function;


Code
-------------------

Sub ADOImportFromAccessTable(DBFullName As String, _
TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
"TableName", Range("C1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
'SQL select data
*.Open "SELECT [Part No], " & VBAWeekNum( & "[Date]" &, 1) & " As [Week] FROM [" & TableName & "] " & _
" WHERE [Part No] = '01801-00408'", cn, , , adCmdText*
For intColIndex = 0 To rs.Fields.count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
TargetRange.Offset(0, 0).CopyFromRecordset rs ' the recordset data

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[Guest]

Hi gti_jobert

My tip is like this
DATEPART(ww, your_date_field) AS WEEKNUMBER

Good luck

 

[Guest]

The problem is this line:
"SELECT [Part No], " & VBAWeekNum( & "[Date]" &, 1) & " As [Week] ... etc

The SQL statement will be executed by the driver but it contains VBAWeekNum
which is a UDF and therefore inaccessible by the driver.

Try:

"SELECT [Part No], DATEPART('WW',[Date]) As Week ... etc

DATEPART is part of the driver'w dialect.