• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 679
  • Last Modified:

opening ADODB.recordset on a crosstab query

I am doing a report on a crosstab query and am using some code to sort things out
I am using the following snippet to create a record set. It gives me an error saying " You don't have enough parameters" so if I fill out the rest of the parameters it gives me another error about  SQL requires UPDATE, SELECT, etc etc. The record source works fine by itself.

    Set rst = New ADODB.Recordset
    rst.Open _
        Source:=Me.RecordSource, _
        ActiveConnection:=CurrentProject.Connection, _
        Options:=adCmdTable
0
gabba_the_hutt
Asked:
gabba_the_hutt
  • 9
  • 7
1 Solution
 
MsLimCommented:
WHAT IS THE REPORT THAT YOU ARE USING ?
or are you using sql query to list out all ?
0
 
gabba_the_huttAuthor Commented:
The record source looks like this:

TRANSFORM Count(CTBaseQuery_ReviewGrid.quizID) AS CountOfquizID
SELECT CTBaseQuery_ReviewGrid.questionNo, CTBaseQuery_ReviewGrid.questionDesc
FROM CTBaseQuery_ReviewGrid
GROUP BY CTBaseQuery_ReviewGrid.questionNo, CTBaseQuery_ReviewGrid.questionDesc
PIVOT CTBaseQuery_ReviewGrid.score;

I want to open a record set based on this so I can determine the number of fields. I got this example from "ACCESS2000 developer's Handbook Volume 1: Desktop edition" There is an example on page 728 but even it doesn't seem to work
0
 
Ralf KlattPrincipal ConsultantCommented:
Hi,

Where and how:
... is the RecordSource defined?
... is CurrentProject.Connection defined?

Any way to see a little more of your access module?


Best regards, Raisor
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
gabba_the_huttAuthor Commented:
Private Sub Report_Open(Cancel As Integer)
    Dim intColCount As Integer
    Dim intControlCount As Integer
    Dim i As Integer
    Dim strName As String
   
    Dim rst As ADODB.Recordset
   
    Set rst = New ADODB.Recordset
    rst.Open _
        Source:=Me.RecordSource, _
        ActiveConnection:=CurrentProject.Connection, _
        Options:=adCmdTable
       
    intColCount = rst.Fields.Count
    intControlCount = Me.Detail.Controls.Count
   
    If intControlCount < intColCount Then
        intColCount = intControlCount
    End If
   
    'Fill in the information for the necessary controls
    For i = 1 To intColCount
        strName = rst.Fields(i - 1).Name
        Me.Controls("lblHeader" & i).Caption = strName
        Me.Controls("txtData" & i).ControlSource = strName
    Next i
   
    rst.Close
       
End Sub
0
 
Ralf KlattPrincipal ConsultantCommented:
Hi,

Could you please try this:

    For i = 0 To intColCount -1
        strName = rst.Fields(i).Name
        Me.Controls("lblHeader" & i).Caption = strName
        Me.Controls("txtData" & i).ControlSource = strName
    Next
0
 
gabba_the_huttAuthor Commented:
My issue is with the record set. Its not opening to start with.
0
 
Ralf KlattPrincipal ConsultantCommented:
Hi,

OK ... then again:

Where and how:
... is the RecordSource defined?
... is CurrentProject.Connection defined?


Best regards, Raisor
0
 
Ralf KlattPrincipal ConsultantCommented:
-> I'd like to see the declarations please!
0
 
gabba_the_huttAuthor Commented:
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.Open Source:=Me.RecordSource       <-Error here

I haven't explicitly declared them myself.  But this is what I get from the debug window

?CurrentProject.Connection
Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\dev\360FeedBack\360_DataFerret.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\Documents and Settings\user\Application Data\Microsoft\Access\System.mdw;Jet OLEDB:Registry Path=SOFTWARE\Microsoft\Office\10.0\Access\Jet\4.0;Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

?Me.RecordSource
ReviewGrid              <- My cross tab query
0
 
Ralf KlattPrincipal ConsultantCommented:
Hi,

Have you checked already that you have the appropriate permissions set on D:\dev\360FeedBack\360_DataFerret.mdb (file & directory)?
And ... are you using the correct account and password to access this MDB?

Please check on ANY syntax errors or typos first!


Best regards, Raisor
0
 
gabba_the_huttAuthor Commented:
Check.
0
 
gabba_the_huttAuthor Commented:
I have re-written the example in the book word for word and I understand every line of it. It just seems as though it doesn't want to create a record set based on a query - if I give the open command a table name instead its quite happy to do that its just won't take my crosstab query
0
 
Ralf KlattPrincipal ConsultantCommented:
Hi,

Are you used to working with ODBC Manager?

If yes I would like you to buid an ODBC-Connection to your DB -> just for testing that you're really getting through to your DB!


Best regards, Raisor
0
 
gabba_the_huttAuthor Commented:
I'm not using an ODBC manager. Everything else is running fine apart from this
0
 
Ralf KlattPrincipal ConsultantCommented:
Hi,

Isn't the ODBC Manager a part of any Microsoft Windows based program?

Unfortunately I'm not aware of what "everything else" is running fine.

---------->       rst.Open Source:=Me.RecordSource       <-Error here        <-----------

Assuming that a record source is part of a database -> and there obviously is a connection problem ...

... I'm defintely sorry, but I'll have to take myself off at this point ... I do not really understand what this is about -> and the measures I suggest are commented but not followed ...


Best regards, Raisor
0
 
gabba_the_huttAuthor Commented:
Thanks for your help Raisor. I have worked out an alternative process. Sorry for the confusion I was just desperate and frustrated.
0
 
gabba_the_huttAuthor Commented:
And just to clarify

--------->       rst.Open Source:=Me.RecordSource       <-Error here        <-----------

this is where the code would throw the error
"everything else" is the several other queries and operations running in the system.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now