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
gabba_the_huttAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 KlattEmployee in Civil ServiceCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 KlattEmployee in Civil ServiceCommented:
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 KlattEmployee in Civil ServiceCommented:
Hi,

OK ... then again:

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


Best regards, Raisor
0
Ralf KlattEmployee in Civil ServiceCommented:
-> 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 KlattEmployee in Civil ServiceCommented:
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 KlattEmployee in Civil ServiceCommented:
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 KlattEmployee in Civil ServiceCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.