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!)
CliffordEvansAsked:
Who is Participating?
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:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

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
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
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
Crystal Reports

From novice to tech pro — start learning today.

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.