Solved

opening ADODB.recordset on a crosstab query

Posted on 2003-12-03
17
601 Views
Last Modified: 2008-02-26
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
Comment
Question by:gabba_the_hutt
  • 9
  • 7
17 Comments
 
LVL 1

Expert Comment

by:MsLim
ID: 9872697
WHAT IS THE REPORT THAT YOU ARE USING ?
or are you using sql query to list out all ?
0
 

Author Comment

by:gabba_the_hutt
ID: 9872707
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
 
LVL 15

Expert Comment

by:Raisor
ID: 9877424
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
 

Author Comment

by:gabba_the_hutt
ID: 9893344
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
 
LVL 15

Expert Comment

by:Raisor
ID: 9893483
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
 

Author Comment

by:gabba_the_hutt
ID: 9893500
My issue is with the record set. Its not opening to start with.
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9893764
Hi,

OK ... then again:

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


Best regards, Raisor
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9893770
-> I'd like to see the declarations please!
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:gabba_the_hutt
ID: 9893799
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
 
LVL 15

Expert Comment

by:Raisor
ID: 9894108
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
 

Author Comment

by:gabba_the_hutt
ID: 9894567
Check.
0
 

Author Comment

by:gabba_the_hutt
ID: 9894809
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
 
LVL 15

Expert Comment

by:Raisor
ID: 9895668
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
 

Author Comment

by:gabba_the_hutt
ID: 9900882
I'm not using an ODBC manager. Everything else is running fine apart from this
0
 
LVL 15

Accepted Solution

by:
Raisor earned 303 total points
ID: 9900978
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
 

Author Comment

by:gabba_the_hutt
ID: 9902149
Thanks for your help Raisor. I have worked out an alternative process. Sorry for the confusion I was just desperate and frustrated.
0
 

Author Comment

by:gabba_the_hutt
ID: 9902161
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now