Printable View
Can I open it using the recordset object? If so, how?
(been a while since I've used ado - been coding in Accesss dao and got used to extra stuff ado doesn't support....)
I vaguely recall that I'd need a command object masquerading as a recordset object to use parameters...?
I'd sure like to know what that might be. :confused:Quote:
Originally Posted by Ecniv
Anyway, what kind of parameters are you referring to? Command parameters for parametized queries maybe?
If you can give me an example of what you are looking for I can probably give you an example of how to do it.
Yeah parameters for predefined queries.
I meant that within access dao you can use the function calls (month, year plus custom calls) whereas Excel to Access via ADO seems to error saying something wrong with my from clause.
The I found i cannot put the parameters in (sigh).
Any ideas?
Quote:
-2147217900 Syntax error in FROM clause.
Note : I have cut n pasted the sql above into access and it runs... apart from changing the % to * :)Code:SELECT
sq.Brand , sq.TheYear, sq.TheMonth
, Sum(sq.DaysDiff) AS TotalDays, Count(sq.DaysDiff) AS NumberOfParts
FROM
(SELECT Max(TB_PartsApp_PartsEscalationDataArchive.ArchivedDate) AS MaxOfArchivedDate
, TB_PartsApp_PartsEscalationDataArchive.RefNo
, TB_PartsApp_PartsEscalationDataArchive.Brand
, Month([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]) AS TheMonth
, Year([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]) AS TheYear
, [TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]-[TB_PartsApp_PartsEscalationDataArchive].[DateIssued] AS DaysDiff
, TB_PartsApp_PartsEscalationDataArchive.DateIssued
FROM TB_PartsApp_PartsEscalationDataArchive
WHERE
((Not TB_PartsApp_PartsEscalationDataArchive.ArchivedDate Is Null)
And (TB_PartsApp_PartsEscalationDataArchive.RefNo Like 'chire%'))
GROUP BY
TB_PartsApp_PartsEscalationDataArchive.RefNo
, TB_PartsApp_PartsEscalationDataArchive.Brand
, Month([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate])
, Year([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate])
, [TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]-[TB_PartsApp_PartsEscalationDataArchive].[DateIssued]
, TB_PartsApp_PartsEscalationDataArchive.DateIssued
) AS sq
WHERE
(sq.theyear=2009 AND sq.themonth<=3)
OR (sq.theyear=2008 AND sq.themonth>3)
GROUP BY
sq.Brand, sq.TheYear, sq.TheMonth
ORDER BY
sq.Brand, sq.TheYear, sq.TheMonth
Your sql works or not? Could you elabore on the problem?
Dee-u
I open a connect (that works) from Excel via ADO.
I open the sql pasted earlier (with the %) and get the error pasted.
I open the mdb direct in Access, paste in the Sql and change the % to *
It runs.
So I thought I would pre-define a query and put in parameters to filter it. But an ADODB.Recordset doesn't have parameters...
Command does, but I would need to change a lot of code to get that working without messing up existing code.
It may not be due to those function calls, I tried this in Excel VBA and it worked.
Code:Private Sub Test()
Dim adoR As ADODB.Recordset
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= G:\Basic DataReport\db1.mdb;"
Set adoR = New ADODB.Recordset
adoR.Open "SELECT ID,a,b,c,month(d) as X FROM test", strConn, adOpenForwardOnly, adLockReadOnly
Do While Not adoR.EOF
Debug.Print adoR.Fields("X")
adoR.MoveNext
Loop
'close and clean-up
adoR.Close
Set adoR = Nothing
End Sub
Ok... hmmm
Well I tried just the middle section:
This errors the same...Code:SELECT Max(TB_PartsApp_PartsEscalationDataArchive.ArchivedDate) AS MaxOfArchivedDate
, TB_PartsApp_PartsEscalationDataArchive.RefNo
, TB_PartsApp_PartsEscalationDataArchive.Brand
, Month([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]) AS TheMonth
, Year([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]) AS TheYear
, [TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]-[TB_PartsApp_PartsEscalationDataArchive].[DateIssued] AS DaysDiff
, TB_PartsApp_PartsEscalationDataArchive.DateIssued
FROM TB_PartsApp_PartsEscalationDataArchive
WHERE
((Not TB_PartsApp_PartsEscalationDataArchive.ArchivedDate Is Null)
And (TB_PartsApp_PartsEscalationDataArchive.RefNo Like 'chire%'))
GROUP BY
TB_PartsApp_PartsEscalationDataArchive.RefNo
, TB_PartsApp_PartsEscalationDataArchive.Brand
, Month([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate])
, Year([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate])
, [TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]-[TB_PartsApp_PartsEscalationDataArchive].[DateIssued]
, TB_PartsApp_PartsEscalationDataArchive.DateIssued
In Access... (with the % replaced) it... ...runs.
:)
Could be a problem with the drivers / office / ms ;)
Hello again..
recoded the opening to change from recordset to command. However, even though this works, how do i get the number of records returned (there is no recordcount) ?
Feels like MS forgot something here... one way or the other...
How about COUNT?
Right uh...
<blank look>
I am feeling very thick... count? Is that like DCount function (I vaguely recall it as being a function not to use)?
So to recap...
- Command uses parameters, recordset doesn't
- Recorset can have a count, command you loop until EOF but dont know how long/many you will have. Without counting first. With parameters?
Doesn't sound like a solution, sorry.
Thanks for suggesting though.
Wait... When you do a Command.Execute it returns a RECORDSET..... so how are you dealing with the data if not with a recordset? Maybe you should post the code you are using to set up and execute the command.
-tg
Due to network issues (ok problems) and lag sometimes the shared database files are locked...
So I wrote the following (but for recordsets) which is now adapted to commands plus parameters...
Now, this returns a command object holding a recordset - however the recordset options such as .recordcount do not exist on the command object and it errors if i try to use them.Code:Public Function OpenCommandWithCatch(ByVal strSql As String, ByRef con As ADODB.Connection, ByRef cmd As ADODB.Command, ByVal strDB As String, Optional blnStatic, Optional blnQuery, Optional aryParams, Optional lngParams) As Boolean
' aryParam has to be x,y ...
'lngParams holds the number of parameters
Dim lngAttempts As Long
Dim blnOpened As Boolean
Dim blnCancelled As Boolean
Dim dteToTryAgain As Date
Dim prm As ADODB.Parameter
Dim lngOpStat As Long, lngOpType As Long
Dim lngLoop As Long
'v1.0.8
'trying to fix the locking problem
On Error Resume Next
blnOpened = False
Application.Cursor = xlWait
Do Until blnOpened Or blnCancelled
Set con = New ADODB.Connection
con.ConnectionString = strDB
con.Open
lngOpStat = IIf(IsMissing(blnStatic), adOpenDynamic, adOpenStatic)
lngOpType = IIf(IsMissing(blnQuery), adCmdText, adCmdTable)
'---- if the recordset object hasnt been initialise then start it
If cmd Is Nothing Then Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandText = strSql
If Not IsMissing(lngParams) Then
For lngLoop = 0 To lngParams - 1
Set prm = New ADODB.Parameter
prm.Name = (aryParams(lngLoop, 0))
prm.Type = adInteger
prm.Value = aryParams(lngLoop, 1)
cmd.Parameters.Append prm
Next
End If
Set prm = Nothing
cmd.Execute
If Err.Number = 0 Then
blnOpened = True
Else
' If Err.Number = 3218 Then
' End If
If lngAttempts > 8 Then
Debug.Print lngAttempts, "Errored: Main Chaps gui: " & vbCrLf & Err.Number & " - " & Err.Description
End If
Err.Clear
lngAttempts = lngAttempts + 1
End If
If Not blnOpened Then
If lngAttempts > 10 Then
If MsgBox("I have tried for ten attempts to open the database (to open/save)." & vbCrLf & "Do you want me to try again?", vbYesNo + vbQuestion) = vbNo Then
Application.Cursor = xlDefault
blnCancelled = True
End If
lngAttempts = 0
dteToTryAgain = Now + CDate("0:0:5")
'wait for a bit - might clear up?
Do Until Now > dteToTryAgain
DoEvents
Loop
End If
Set con = Nothing
End If
DoEvents
Loop
Application.Cursor = xlDefault
OpenCommandWithCatch = blnOpened
On Error GoTo 0
End Function
:)
Umm..... Okay.... first... I see where you execute the command.... but you're not getting the recordset.... and now that I've read your last statement twice more, I think I see where things have gone wrong here.... command.Execute is a FUNCTION... it RETURNS a recordset... but you're not grabbing it... you're allowing VB to throw your results away...
Set myRecordset = cmd.Execute <--- THAT will grab the recordset that is returned... from there you can use myRecordset to access your data.
The command object does NOT hold a recordset... it RETURNS it... it's up to you to set it to a variable so that you can use the recordset.
-tg
Ahh I see. A small but crucial detail ;)
Right, let me put that in the code and see what happens, will post back later.
how did I not see/remember/know this...?
Right,
that partially worked. At least it ran and returned a recordset. However the recordset is empty (EOF set) when it should have some data. I think the parameters are adding correctly so this is very annoying :|
new code
Code:Public Function OpenCommandWithCatch(ByVal strSql As String, ByRef con As ADODB.Connection, ByRef rst As ADODB.Recordset, ByRef cmd As ADODB.Command, ByVal strDB As String, Optional blnStatic, Optional blnQuery, Optional aryParams, Optional lngParams) As Boolean
' aryParam has to be x,y ...
'lngParams holds the number of parameters
Dim lngAttempts As Long
Dim blnOpened As Boolean
Dim blnCancelled As Boolean
Dim dteToTryAgain As Date
Dim prm As ADODB.Parameter
Dim lngOpStat As Long, lngOpType As Long
Dim lngLoop As Long
'v1.0.8
'trying to fix the locking problem
On Error Resume Next
blnOpened = False
Application.Cursor = xlWait
Do Until blnOpened Or blnCancelled
Set con = New ADODB.Connection
con.ConnectionString = strDB
con.Open
lngOpStat = IIf(IsMissing(blnStatic), adOpenDynamic, adOpenStatic)
lngOpType = IIf(IsMissing(blnQuery), adCmdText, adCmdTable)
'---- if the recordset object hasnt been initialise then start it
If cmd Is Nothing Then Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandText = strSql
If Not IsMissing(lngParams) Then
For lngLoop = 0 To lngParams - 1
Set prm = New ADODB.Parameter
prm.Name = (aryParams(lngLoop, 0))
prm.Type = aryParams(lngLoop, 2) 'adInteger
prm.Value = aryParams(lngLoop, 1)
cmd.Parameters.Append prm
Next
End If
Set prm = Nothing
Set rst = cmd.Execute
If Err.Number = 0 Then
blnOpened = True
Else
' If Err.Number = 3218 Then
' End If
If lngAttempts > 8 Then
Debug.Print lngAttempts, "Errored: Main Chaps gui: " & vbCrLf & Err.Number & " - " & Err.Description
End If
Err.Clear
lngAttempts = lngAttempts + 1
End If
If Not blnOpened Then
If lngAttempts > 10 Then
If MsgBox("I have tried for ten attempts to open the database (to open/save)." & vbCrLf & "Do you want me to try again?", vbYesNo + vbQuestion) = vbNo Then
Application.Cursor = xlDefault
blnCancelled = True
End If
lngAttempts = 0
dteToTryAgain = Now + CDate("0:0:5")
'wait for a bit - might clear up?
Do Until Now > dteToTryAgain
DoEvents
Loop
End If
Set con = Nothing
End If
DoEvents
Loop
Application.Cursor = xlDefault
OpenCommandWithCatch = blnOpened
On Error GoTo 0
End Function
Edit:
I even amended the code and query to use only one parameter and it returns nothing. Running the query in Access and entering the same value returns records.
Are you sure the parameters are going to their correct positions?
Hi,
On the amended query there is only one filter (on the year).
The parameter is being added without an error and when executed it is not complaining about not having enough parameters/missing data fields.
There isnt a way to see what it is sending/using... at least as far as I know.
Two things:
1) On the command object, before you execute it, set the cursor location to client side
2) Right after the .Execute set the rst.ActiveConnection = Nothing - this will disconnect your recrordset and allow you to pass it back
What I think is happening is that the cursor is defaulted to Serverside, which means that the data isn't actually returned, but a pointer to it is. So each time you do a .MoveNext, it's a trip out to the server and back. When you closed the connection though, you severed the link, making the recordset invalid. But if you set the cursorlocation to Clientside, it'll actually return ALL of the data back to the client. At which point you can diconnect the recordset (by setting active connection to nothing) and close the connection (suggestion close the connection BEFORE setting it to nothing.)
-tg
For testing purposes perhaps you can try a very simple query like the following if it will return any record.
Code:Private Sub Test()
Dim adoR As ADODB.Recordset
Dim adoConn As ADODB.Connection
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = "string"
adoConn.Open
Set adoR = New ADODB.Recordset
With adoR
.Open "SELECT * FROM Table", adoConn, adOpenForwardOnly, adLockReadOnly
Do While Not .EOF
Debug.Print .Fields(0)
.MoveNext
Loop
.Close
End With
Set adoR = Nothing
MsgBox "Done"
End Sub
Hiya,
I copied the code from Excel VBA to Access VBA. Access mdb being the one I want to run the query from.
I tweaked the connection etc to do that. Had to close the mdb file and reopen it as it locked itself (thanks windoze).
Then it ran..... exactly like the excel. ie it adds the parameter, the parameter matchs so the query runs but it returns not records. However running as a query and entering the same data (ThisYear = 2009) it returns records.
I have a suspicion its the ADODB part... just wondering if the company i work for (which IT doesn't seem to be set up right) might have older drivers in...
Wouldn't surprise me.
As a test I am going to write a short bit of code that runs the parameter on a DAO connection in the same db and see if that works....
post back shortly...
Hiya,
The above code is what I used in Access VBA. It opens a recordset and has data.Code:Public Sub s()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim p As DAO.Parameter
Dim ps As DAO.Parameters
Set qdf = DBEngine(0)(0).QueryDefs("qryRptParts")
Set p = qdf.Parameters("ThisYear")
p.Value = 2009
Set rst = qdf.OpenRecordset
Debug.Print rst.EOF
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set p = Nothing
End Sub
I've changed the ADO references to v2.8 (was on v2.1). Re-ran the connection adodb function with the command and it runs but returns no records.
I can only therefore point an accusing finger at ADO ...
... or I can point at me for missing something crucial. Cant see what tho :)
I will have to do a long way around of extracting data, procesing it and putting the results into a new table. Then run reporting off that table. Longer way around, but at least the customer will be more happy...
Thanks for looking at this. If something comes to mind, please post on the bottom, or pm me :)