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

ora-00907 error in ms access vba

Hello...  
I am using an access database and connecting to oracle with odbc.  I have a problem where I get an error "ora-00907."  The problem is that it works in some places and not in others.  the vba is as follows

 Dim cnt As ADODB.Connection
    'Define the recordset
    Dim rst As ADODB.Recordset
    'Define the SQL String
    Dim stDB As String, stSQL As String, stConn As String
   
    Dim prst As DAO.Recordset
   
    Set prst = CurrentDb.OpenRecordset("tblStartDate")
    proj = prst("Project")
    serv = prst("Server")
   
   
   
   stConn = "Provider=OraOLEDB.Oracle;" & _
    "Data Source=" & serv & ";" & _
    "User Id=" & proj & ";" & _
    "Password=xxxxxxx;"
         
   
    'Set the connection and the recordset
    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
   
    'Open the connection
    cnt.Open stConn
   
   
    'list the SQL Needed
    sql2 = "select sg.segment_description, "
    sql3 = "sum(decode(result_type,'CC',1,'CP',1,'IN',1,'XX',1)) TotalRecords, "
    sql4 = "sum(decode(substr(result_type,1,1),'C',1,0)) Completes, "
    sql5 = "sum(decode(result_type,'CC',1,0)) Contacts, "
    sql6 = "sum(decode(pc.last_result,'Spec Pldg',1,0)) Pledges, "
    sql7 = "sum(bp.pledge_amount) SpecDollars, "
    sql8 = "sum(bp.total_amount) TotalDollars, "
    sql9 = "(select sum(decode(substr(result_type,1,1),'C',1,0)) from prospect_contact pc,prospect_base pb, base_pledge bp,segment_group_control sg,campus.result_codes rc where pb.id_number = pc.id_number And pb.segment_group = sg.segment_group and sg.result_group = rc.result_group and pb.id_number = bp.id_number (+) and pc.last_result = rc.result_code and pc.last_call_Date between to_date('" & Format(DLookup("Month1", "qryFY"), "mm\/dd\/yyyy") & "','mm-dd-yyyy') and to_date('" & Format(DLookup("Month1End", "qryFY"), "mm\/dd\/yyyy") & "','mm-dd-yyyy')) Month1Completes  "
    sql10 = "from prospect_contact pc,prospect_base pb, base_pledge bp,segment_group_control sg,campus.result_codes rc "
    sql11 = "where pb.id_number = pc.id_number and pb.segment_group = sg.segment_group "
    sql12 = "and sg.result_group = rc.result_group and pb.id_number = bp.id_number (+) and pc.last_result = rc.result_code "
    sql13 = "group by sg.segment_description,sg.group_level1,sg.group_level2,sg.group_level3,sg.group_level4 "
    sql14 = "order by sg.segment_description,sg.group_level1,sg.group_level2,sg.group_level3,sg.group_level4 "
         
    'set all the lines to one string
    stSQL = sql2 & sql3 & sql4 & sql5 & sql6 & sql7 & sql8 & sql9 & sql10 & sql11 & sql12 & sql13 & sql14
    'Set the recordset to the SQL statements
    rst.Open stSQL, cnt
   
   
   
'Define the local terms to write to the table

'Define the new local databse
Dim mdb As Database
'Define a new DAO recordset
Dim msrst As DAO.Recordset
'Set databse to the current access file
Set mdb = CurrentDb

'Set the new recordset to the table we plan to write our results too
Set msrst = CurrentDb.OpenRecordset("tblCurrentStats")


'Transcribe the information from the first recordset to the new recordset
Do While rst.EOF = False
    msrst.AddNew
    msrst!SegmentName = rst!segment_description
    msrst!TOTALRECORDS = rst!TOTALRECORDS
    msrst!COMPLETES = rst!COMPLETES
    msrst!CONTACTS = rst!CONTACTS
    msrst!PLEDGES = rst!PLEDGES
    msrst!SpecDollars = rst!SpecDollars
    msrst!TOTALDOLLARS = rst!TOTALDOLLARS
   
    'etc
'Write to the table
    msrst.Update

    rst.MoveNext
Loop
rst.Close
msrst.Close

 
 
   
   
    Set rst = Nothing
    'Close the Connection
    cnt.Close
    Set cnt = Nothing
   
   
   
   
   
End Function
0
shaikmonster
Asked:
shaikmonster
  • 9
  • 4
1 Solution
 
sathyagiriCommented:
I am just guessing that this is the piece of code that might be the cause of the error, depending on whether the DLOOKUP is successful or not.

Try displaying the sql string out, before opening the recordset and see where the problem is.


 sql9 = "(select sum(decode(substr(result_type,1,1),'C',1,0)) from prospect_contact pc,prospect_base pb, base_pledge bp,segment_group_control sg,campus.result_codes rc where pb.id_number = pc.id_number And pb.segment_group = sg.segment_group and sg.result_group = rc.result_group and pb.id_number = bp.id_number (+) and pc.last_result = rc.result_code and pc.last_call_Date between to_date('" & Format(DLookup("Month1", "qryFY"), "mm\/dd\/yyyy") & "','mm-dd-yyyy') and to_date('" & Format(DLookup("Month1End", "qryFY"), "mm\/dd\/yyyy") & "','mm-dd-yyyy')) Month1Completes
0
 
shaikmonsterAuthor Commented:
I'm sorry...  Not following your message...  Me dumb...
0
 
sathyagiriCommented:
Try displaying the string "stSQL" using some sort of a message box or a print statement to see what's missing in the query.

As I mentioned I am guessing that sometimes your DLookup("Month1", "qryFY") is failing and hence the query being passed to over Oracle is incomplete?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
shaikmonsterAuthor Commented:
looks like it is in either line 9 or line 11 (sql11) after pb.id_number =

I used some old school joins... I wonder if I need to chnage those to the "left join...on"  format
0
 
shaikmonsterAuthor Commented:
Actuall I think that is just where the text limit was...
0
 
shaikmonsterAuthor Commented:
the error actually say missing right parentheses.  I have looked through it, and I don't see one missing.  Once again the db actuall works at some of our sites, so it is odd (to me) that there would be a coding error that is actually only an error at some places... Unless it is an oracle version difference, which I don't think is the case.
0
 
shaikmonsterAuthor Commented:
Any help???
0
 
sathyagiriCommented:
Could you print out the string when the error occurs..

Some thing like

MSGBox Stsql;

and paste the string here
0
 
shaikmonsterAuthor Commented:
having a little trouble ...  I have to run it on a remote system (because I don't get any errors on my system)...
0
 
shaikmonsterAuthor Commented:
I think you are right about that line in the sql (sql9).  I think it might have something to do with the quotes.
0
 
shaikmonsterAuthor Commented:
can't seem to get the string to print out... is this a dead end for me...
0
 
shaikmonsterAuthor Commented:
wow... not even worth 500 ppints....
0
 
sathyagiriCommented:
Why don't try changing the code that does the lookup to make sure that I would always return some kind of data.. Something like if no record found in the lookup, provide a default date like 01-01-9999 or something like that
Or
 In the m/c where it's failing, can you modify the code with hard coded dates and isolate if date is what is really causing the problem?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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