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

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

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

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