[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-05
10
Medium Priority
?
303 Views
Last Modified: 2012-06-27
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?  
0
Comment
Question by:chaverly
  • 5
  • 3
  • 2
10 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 36921599
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
 

Author Comment

by:chaverly
ID: 36921625
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
 
LVL 16

Expert Comment

by:kmslogic
ID: 36921714
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 36922087
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 36922093
I think you may have to link inside Access to both MS SQL and to Oracle.

mlmcc
0
 
LVL 16

Assisted Solution

by:kmslogic
kmslogic earned 1000 total points
ID: 36922150
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
 

Author Comment

by:chaverly
ID: 36926025
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
 

Author Comment

by:chaverly
ID: 36926407
I did what you suggested and received the message DllRegisterServer in SACommonControls.dll succeeded.  Thanks, but now what?
0
 

Author Comment

by:chaverly
ID: 36926648
To answer my own last question I am still getting the fatal error.  Thanks guys!
0
 

Author Closing Comment

by:chaverly
ID: 36926661
I am convinced we will have to use Oracle and incorporate the SQL table there as a View.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

873 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