Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    6

    Unanswered: New ADODB.Connection

    Hi, Please help...

    I am trying to write a connection in vb that pulls data via SQL from Access to Excel.

    I have written the sQRY to start with TRANSFORM as I am building the data as a Crosstab query

    When I start this as a SELECT query then it works. I dont understand why when I change it then it doesnt!

    How can I get it to work?

    Thanks,
    Jez

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi sticksboy

    I think some more information would be helpful !

    ie.
    What is the error message/what happens.
    Can you post the two SQL strings (1 that works and 1 that doesn't).



    MTB

  3. #3
    Join Date
    Oct 2006
    Posts
    6
    MTB,

    This is the code I have written, the error message comes back is through the msgbox in my code. It says Syntax Error in TRANSFORM Statement.

    Option Explicit

    Dim cnnDW As ADODB.Connection
    Dim rsDW As ADODB.Recordset
    'Dim fso As FileSystemObject
    'Dim oFile As TextStream
    Dim sQRY As String
    Dim strDWFilePath, strCSVFilePath, strDestFilePath, strDestFileName As String
    'Dim strTextLine, strReasonCode As String

    Sub GetData()

    On Error GoTo Err:

    'strDWFilePath = "C:\Documents and Settings\Jez\My Documents\db1.mdb"
    strDWFilePath = "H:\NCHO\Housing Services\Data Warehouse\HSG Data Warehouse.mdb"

    Set cnnDW = New ADODB.Connection
    Set rsDW = New ADODB.Recordset

    Sheet4.Range("A2:BH25").ClearContents

    cnnDW.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strDWFilePath & ";"

    sQRY = "TRANSFORM Count(tblNoAccessbyAppt.WRNumber) AS CountOfWRNumber" & _
    "SELECT tblNoAccessbyAppt.CouncilName" & _
    "FROM tblNoAccessbyAppt" & _
    "WHERE (((tblNoAccessbyAppt.BANumber) <> ""HSG0008 20"") And ((tblNoAccessbyAppt.AppointmentOutcomeID) = ""N"") And ((tblNoAccessbyAppt.ActionTypeID) = ""AS""))" & _
    "GROUP BY tblNoAccessbyAppt.CouncilName" & _
    "PIVOT tblNoAccessbyAppt.Week"

    rsDW.CursorLocation = adUseClient
    rsDW.Open sQRY, cnnDW, adOpenStatic, adLockReadOnly

    Application.ScreenUpdating = False
    Sheet4.Range("A2").CopyFromRecordset rsDW

    UserForm1.Hide

    rsDW.Close
    cnnDW.Close

    Set rsDW = Nothing
    Set cnnDW = Nothing

    Exit Sub

    Err:
    MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error, vbCritical, "Jez"
    MsgBox VBA.Err

    End Sub

    The same code with this SQL in will work

    sQRY = "SELECT tbl3rdParty.WRNumber, tbl3rdParty.ACVStatus " & _
    "From tbl3rdParty " & _
    "WHERE tbl3rdParty.ActionTypeID = 'AS' " & _
    "AND tbl3rdParty.VisitDate = IIF(DatePart('w', Date()-1)=1, Date()-3, Date()-1)"

    I am at a bit of a loss.

    Jez

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Try this

    sQRY = "TRANSFORM Count(tblNoAccessbyAppt.WRNumber) AS CountOfWRNumber " & _
    "SELECT tblNoAccessbyAppt.CouncilName " & _
    "FROM tblNoAccessbyAppt " & _
    "WHERE (((tblNoAccessbyAppt.BANumber) <> 'HSG0008 20') And ((tblNoAccessbyAppt.AppointmentOutcomeID) = 'N') And ((tblNoAccessbyAppt.ActionTypeID) = 'AS')) " & _
    "GROUP BY tblNoAccessbyAppt.CouncilName " & _
    "PIVOT tblNoAccessbyAppt.Week"

    I have moded it by adding a space before each " & _
    and changed each double quote "" to an apostrophy.

    You need a space between each part of the sql statement otherwise it doesn't know where one part ends and trhe next begins !!

    You need to watch this when you past statements from Access query designer !!??

    This is also in line with your query that works !!

    That should do for a start !!

    HTH

    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •