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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
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)?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
I think you may have to link inside Access to both MS SQL and to Oracle.

mlmcc
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
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?
chaverlyAuthor Commented:
I did what you suggested and received the message DllRegisterServer in SACommonControls.dll succeeded.  Thanks, but now what?
chaverlyAuthor Commented:
To answer my own last question I am still getting the fatal error.  Thanks guys!
chaverlyAuthor Commented:
I am convinced we will have to use Oracle and incorporate the SQL table there as a View.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.