Creating a link from a SQL database to an Access database using in Crystal

I have a SQL Server database that I am trying to link table to an Oracle database.  Crystal does not like this and keeps giving me a fatal error.   I would like to convert the SQL database to an Access database, but keep the data current.  I tried to create an ODBC connection so the SQL tables would link in Access.  I still cannot connect the Access source table to the Oracle table.
 
1) I opened Access 2007
2) I gave the filename as Access SQL
3) I clicked the Create button
4) I went to External Data tab and clicked More
5)I selected ODBC database
6) Picked Link to the data source by creating a linked table
7)Selected Machine Datasource tab
8) Chose Pickpro (my SQL source)
9) Said Ok
10) selected my table INVENTORY Map
11) Double clicked on dbo.INVENTORY MAP
12) Received all my rows and columns
13) Is this live data? Is it being updated when I display it?
14) Picked Save as 2002-2003 databse
15) Put it in my network folder
 
What did I do wrong?  
chaverlyAsked:
Who is Participating?
 
mlmccCommented:
Crystal can access linked tables.  I do it all the time to combine differing data sources.

Crystal can use ODBC to connect directly, the issue is it cannot have an ODBC to a MS SQL database and ODBC to an Oracle database in the same report.

mlmcc
0
 
mlmccCommented:
13 - O long as you linked to it and didn't import it, the data should be live

Are you getting an error?

At firt you are saying linking MS SQL to Access and then at the end you say Oracle to Access
Which is it?

mlmcc
0
 
chaverlyAuthor Commented:
Yes I'm getting the crw32.exe fatal error.  Here's the setup.  You helped me with it in a prior question.  I need to incorporate a SQL data source table with an Oracle table.  The suggestion was to link the SQL table in Access and then link to Oracle.  It should work, but it doesn't.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kmslogicCommented:
I'm not sure it's clear what you are ultimately trying to do...

You are using Crystal Reports to report on something right?  I don't think you can have CR use a linked table from within Access so that is a problem.  

Can't Crystal reports access ODBC data itself (in other words report directly on the Oracle data through ODBC)?
0
 
mlmccCommented:
I think you may have to link inside Access to both MS SQL and to Oracle.

mlmcc
0
 
kmslogicCommented:
Well if CR can see the linked table then you did everything right, so it looks to be a Crystal Reports issue.  I did see something that said your error could be caused by SACommonControls.dll not being registered correctly...  To test that theory locate that file and then from a command prompt change directory to the folder that contains the DLL  (cd /wherever/it/is) and then:

REGSVR32 SACommonControls.dll
0
 
chaverlyAuthor Commented:
I searched for SACommonControls.dll and found it in 2 places.

C:\Program Files\Business Objects\Common\3.5\bin       modified 9/16/2010

C:\Program Files\Common Files\Business Objects\3.0\bin    modified 7/27/2005

Where should it be?  Should I have 2 dlll's?
0
 
chaverlyAuthor Commented:
I did what you suggested and received the message DllRegisterServer in SACommonControls.dll succeeded.  Thanks, but now what?
0
 
chaverlyAuthor Commented:
To answer my own last question I am still getting the fatal error.  Thanks guys!
0
 
chaverlyAuthor Commented:
I am convinced we will have to use Oracle and incorporate the SQL table there as a View.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.