Setting SQL Database on the fly in VB

[Rothweld]

Hi all

I have a problem moving reports between servers. I need to be able to tell a report which SQL server and database to log onto through code. I know that I can set the loaction while writing the report but I need to be able to send these reports to people who have absolutely no technical ability. I am using VB6, Crystal 7 and MS SQL7. I can't use DSN. My attempt is going something like this and while it doesn't generate errors it always goes back to the original database

My report is based on an SQL stored proceedure and works fine on local test machines.

CrystalReport1.Connect = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=ID;PWD=PWD;Initial Catalog=DATABASE1;Data source=SQLSERVER1"

CrystalReport1.ParameterFields(0) = "@ord_no;" & sTemp & ";True"
CrystalReport1.ParameterFields(1) = "@ext;" & sTemp1 & ";True"
CrystalReport1.ParameterFields(2) = "@inv_type;" & stemp2 & ";True"
CrystalReport1.ParameterFields(3) = "@comp_details;" & sTemp3 & ";True"

CrystalReport1.Action = 1

 

[Rothweld]

Sorry Folks I cracked it shortly after. Isn't that always the way. The solution for those who need it is

CrystalReport1.Connect = "DSN=NewServer; UID=UID; PWD=PWD; DSQ=NewTable;"

I thought that DSN= was only for ODBC but it also works with SQL. I hope this helps somebody else

aidanmac@hotmail.com