Solved

Run-time error 9: Subscript out of range when running object over Access database

Posted on 2008-10-17
9
1,669 Views
Last Modified: 2008-11-11
Hi
Getting a run time error when running VB code (Run-time error 9: Subscript out of range)
I have created an ODBC connection to an Access Database  From within crystal reports the crystal report runs fine over the access database.
I then create the executable object.
However when I try and run the executable object I keep getting the Run-time error 9: Subscript out of range.

thanks
Mike

The following is my VB code

Private Sub Form_Load()
Dim CRapp As CRAXDRT.Application
Dim ConnectionInfo As CRAXDRT.ConnectionProperties
Dim con As CRAXDRT.ConnectionProperties
Dim rpt As CRAXDRT.Report
Set CRapp = New CRAXDRT.Application
Set rpt = CRapp.OpenReport("V:\Crystal Reports\Operating Statement\Accidents\Accidents for Operating Statement - v1.rpt")
Set ConnectionInfo = rpt.Database.Tables(1).ConnectionProperties
ConnectionInfo.Item("DSN") = "MS Access -Accidents"
ConnectionInfo.Item("Database") = "Accidents01.mdb"
ConnectionInfo.Item("User ID") = ""
ConnectionInfo.Item("Password") = ""
Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = rpt
CRViewer1.ViewReport
Screen.MousePointer = vbDefault
End Sub
0
Comment
Question by:MECR123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 43

Assisted Solution

by:TimCottee
TimCottee earned 50 total points
ID: 22739617
Hello MECR123,

Is the object collection zero based? Should it be:

Set ConnectionInfo = rpt.Database.Tables(0).ConnectionProperties

Regards,

TimCottee
0
 
LVL 6

Expert Comment

by:mirzas
ID: 22739618

' CHANGE
 
Set ConnectionInfo = rpt.Database.Tables(1).ConnectionProperties
 
 
' TO
 
 
Set ConnectionInfo = rpt.Database.Tables(0).ConnectionProperties

Open in new window

0
 

Author Comment

by:MECR123
ID: 22739718

Tried that change to the connection properties - Still the same error
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 6

Expert Comment

by:mirzas
ID: 22741118
Is this all the code you have?

The error might be occurring somewhere else.
0
 

Author Comment

by:MECR123
ID: 22741596
Hi Mirzas

Yes this is the complete list of the code that I use to create the executable objects  - This code works perfectly over our ERP system (different database etc) - But now I have created an ACCESS database - configured in ODBC - The crystal repot works fine within the crystal application but the executable object will not run  


Regards
Mike

0
 
LVL 6

Assisted Solution

by:mirzas
mirzas earned 50 total points
ID: 22755987
The error can be somewhere inside this block.

Use the debugger and check the values inside ConnectionInfo.Item
ConnectionInfo.Item("DSN") = "MS Access -Accidents"
ConnectionInfo.Item("Database") = "Accidents01.mdb"
ConnectionInfo.Item("User ID") = ""
ConnectionInfo.Item("Password") = ""

Open in new window

0
 

Author Comment

by:MECR123
ID: 22758280
Hello  - I think I have resolved the issue with a work around - As I said previously this code works over our ERP database - so what I did was create a Crystal report over our ERP database - This report has a sub-report which is the crystal report over the access database  - This seems to be a work around for this issue

thanks for your help
regards
Mike
0
 

Accepted Solution

by:
MECR123 earned 0 total points
ID: 22795374
Please close question as I got a work around myself for the issues - thanks Mike
0
 

Author Comment

by:MECR123
ID: 22894192
Hello  - I think I have resolved the issue with a work around - As I said previously this code works over our ERP database - so what I did was create a Crystal report over our ERP database - This report has a sub-report which is the crystal report over the access database  - This seems to be a work around for this issue
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

732 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