• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

Changing DataSource - MS to SQLAccess

With reference to my previous thread on this:

http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21191780.html

Is it also possible to change from MSSQL to Access2000 in code ? I've tried this using code below but get JOIN error which were all okay when running against MSSQL.

        .DllName = "crdb_ado.dll"
        .ConnectionProperties.DeleteAll
        .ConnectionProperties.Add "Provider", "Microsoft.Jet.OLEDB.4.0"
        .ConnectionProperties.Add "Data source", "c:\data\test.mdb"
        .ConnectionProperties.Add "User ID", "Admin"
        .ConnectionProperties.Add "Password", ""
        .ConnectionProperties.Add "Database Type", "Access"

Thanks

(500 points as I've searched the Internet myself and experimented but still no success and it is driving me mad!)
0
CliffordEvans
Asked:
CliffordEvans
  • 4
  • 3
1 Solution
 
mlmccCommented:
To do what you are trying will require you to change the database driver which I don't think is done through the connection properties.

What versiion of Crystal?

Is this a permanent change or do you need to use it just some of the time?

mlmcc
0
 
vidruCommented:
mlmcc:
They didn't change much in the RDC's object model after CR 8.5, but they did change a lot of the connectivity.  Changing the driver in that manner is fully acceptable.  If you look on page 12 of this document, you'll see that Clifford's code is exactly what (according to them) he should be doing:
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_rdc9_connectionproperties.pdf.asp

Clifford:
What happens when you try to change from SQL Server to Access from the Crystal Designer?  Does it give you an error as well when you set up the same links?  Can you describe the table linking?

-dave
0
 
CliffordEvansAuthor Commented:
Works okay changing to Access in the designer.
I think problem is in table location. The access database was imported from MSSQL and when looking at the resulting SQLQueryString, tables in the FROM clause still refer to <server>.dbo.<table>. Hence I get the 'Syntax ERROR in JOIN expression'. However, crxTable.location shows the location as <table> so nothing to change. When trying to set crxTable.location=<server>.dbo.<table> states that the table cannot be found.
Cliff
0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
CliffordEvansAuthor Commented:
Previous post should read <database>.dbo.<table> NOT <server>.dbo.<table>
Thanks
0
 
vidruCommented:
Try setting the Location to the table's Name.  That should get rid of the schema:

With crxTable
        .DllName = "crdb_ado.dll"
        .ConnectionProperties.DeleteAll
        .ConnectionProperties.Add "Provider", "Microsoft.Jet.OLEDB.4.0"
        .ConnectionProperties.Add "Data source", "c:\data\test.mdb"
        .ConnectionProperties.Add "User ID", "Admin"
        .ConnectionProperties.Add "Password", ""
        .ConnectionProperties.Add "Database Type", "Access"  
        .Table = .Name
End With

-dave
0
 
vidruCommented:
Sorry, that last line before the 'End With' should read::

        .Location= .Name

-dave
0
 
CliffordEvansAuthor Commented:
Works! Strange because the pre-change value is the same as the value it is being changed to. Gives the impression that it is not worth changing.
Thanks for your help.
0
 
vidruCommented:
Glad it worked out.

-dave
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now