ADO Connect Access

[gti_jobert]

Hi all,

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

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

.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 current [Date] is in format '24/03/2006' - how do I modify this statement?

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

[Martin]

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

"gti_jobert" wrote:

>
> Hi all,
>
> I'm not sure if this is the right forum to post in as my Q may be
> MSAccess related. I am using an ADO connection to retieve data from an
> Access database using Excel VBA - this seems to be functioning properly
> (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 current
> [Date] is in format '24/03/2006' - how do I modify this statement?
>
> TIA, any ideas appreciated......and again someone here may know (bob??)
>
>
> --
> gti_jobert
> ------------------------------------------------------------------------
> gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
> View this thread: http://www.excelforum.com/showthread...hreadid=529538
>
>

[gti_jobert]

I've been triyng that;

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

.Open "SELECT [Part No], VBAWeekNum([Date], 1) AS [Week] FROM [" & TableName & "] " & _
            " WHERE [Part No] = '01801-00408'", cn, , , adCmdText

The code comes up with an error, the coding is wrong - anyone know the syntax to add a VBA function into an SQL Access Statement?

[gti_jobert]

This is the entire Function;

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

[Svensson_59]

Hi gti_jobert

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

Good luck

"gti_jobert" wrote:

>
> 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
>
> --------------------
>
>
> --
> gti_jobert
> ------------------------------------------------------------------------
> gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
> View this thread: http://www.excelforum.com/showthread...hreadid=529538
>
>

[AA2e72E]

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.