Solved

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

Posted on 2008-10-17
9
1,662 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
  • 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

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.

Question has a verified solution.

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

This article shows how to make a Windows 7 gadget that accepts files dropped from the Windows Explorer.  It also illustrates how to give your gadget a non-rectangular shape and how to add some nifty visual effects to text displayed in a your gadget.…
This article describes a technique for converting RTF (Rich Text Format) data to HTML and provides C++ source that does it all in just a few lines of code. Although RTF is coming to be considered a "legacy" format, it is still in common use... po…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

820 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