var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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?
I think some more information would be helpful !
What is the error message/what happens.
Can you post the two SQL strings (1 that works and 1 that doesn't).
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.
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
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
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" & _
rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Set rsDW = Nothing
Set cnnDW = Nothing
MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error, vbCritical, "Jez"
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.
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 " & _
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 !!