Solved

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

Posted on 2008-10-17
9
1,654 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Windows Drag & Drop Location 2 100
Redirect page to logout only when page is idle to 1 mins (or whenever) 1 54
Slow process to read Excel 15 124
Need more details 5 151
This article shows a few slightly more advanced techniques for Windows 7 gadget programming, including how to save and restore user settings for your gadget and how to populate the "details" panel that is displayed in the Windows 7 gadget gallery.  …
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 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