asked on

Changing DataSource - MS to SQLAccess

With reference to my previous thread on this:

https://www.experts-exchange.com/questions/21191780/Changing-DataSource.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!)

Mike McCracken

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

vidru

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

CliffordEvans

ASKER

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

CliffordEvans

ASKER

Previous post should read <database>.dbo.<table> NOT <server>.dbo.<table>
Thanks

ASKER CERTIFIED SOLUTION

vidru

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vidru

Sorry, that last line before the 'End With' should read::

.Location= .Name

-dave

CliffordEvans

ASKER

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.

vidru

Glad it worked out.

-dave