Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-17
9
Medium Priority
?
1,740 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 200 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 200 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

If you have ever found yourself doing a repetitive action with the mouse and keyboard, and if you have even a little programming experience, there is a good chance that you can use a text editor to whip together a sort of macro to automate the proce…
With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

971 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