Solved

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

Posted on 2008-10-17
9
1,633 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
 
LVL 6

Expert Comment

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

The error might be occurring somewhere else.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article surveys and compares options for encoding and decoding base64 data.  It includes source code in C++ as well as examples of how to use standard Windows API functions for these tasks. We'll look at the algorithms — how encoding and decodi…
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 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

13 Experts available now in Live!

Get 1:1 Help Now