I need to know how to change the dsn for odbc thru vb6 code

[bburnell]

Hi,
The project I am working on uses ODBC sources from our "test" and "production" SQL Server 7 servers. I have added a parameter to the exe file that if you add "TEST" it will pull from the test system using SQL commands hard coded in VB6. I need to know how in VB6 (SP4) and/or CR v8.0441 code how to do this "on the fly." I have tried the LogOnServer command with no success. I don't understand the "SetLogOnInfo" command at all. I have stored procedures and data on the different servers I need to point to. I use about 20 rpt files thru 1 CR viewer form and that is my biggest problem with using the examples that I have found on this forum and in Crystal Decisions website/knowledge base. Any help would be greatly appreciated!
Brett Please visit my websites!

http://www.smsforce.com

http://www.goodguyz.com

 

[balves]

You can pretty much use either SetLoginInfo or LogonServer to connect to the database. They are methods of different Crystal objects.

If you use LogonServer and you have a variable to tell you which database you want to connect to...


Dim crxApp As New CRAXDRT.Application
Dim crxRep As New CRAXDRT.Report
Dim frmViewerForm As Form2 ' your form with CRviewer control

' create instance of Form2 that has CRViewer
Set frmViewerForm = New Form2
' connect to db

if your_variable_for_db = "TEST" then
crxApp.LogOnServer "p2sodbc.dll", "test db servername goes here", "test dsn name goes here", "user id goes here", "password here"
else
crxApp.LogOnServer "p2sodbc.dll", "prod db servername goes here", "prod dsn name goes here", "user id goes here", "password here"
endif

' open the report
Set crxRep = crxApp.OpenReport("path of rpt&quot
' point the viewer to the report
frmViewerForm.CRViewer1.ReportSource = crxRep
' show the form / report
frmViewerForm.CRViewer1.ViewReport
frmViewerForm.Show Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[bburnell]

Thanks Brian,
I'll try it and let you know how it turns out.
Brett Please visit my websites!

http://www.smsforce.com

http://www.goodguyz.com

 

[bburnell]

Hi again,
Here is the EXACT error I get now and the fix(?) Which "solution" do I use in my case?

http://support.crystaldecisions.net/library/kbase/articles/c2006666.asp

Here is what I'm using:
crxApp.LogOnServer "p2sodbc.dll", systemname, "Collections", "", ""

- systemname is a variable set based on parameter passed ("S6" for test) ("DELL6300" for prod)
- "Collections" is the table name of the SQL Server (according to M$ Intellisense)
- I want the userid and password of Win NT login passed.

Any ideas on this one?
Thanks,
Brett Please visit my websites!

http://www.smsforce.com

http://www.goodguyz.com

 

[balves]

You're not connecting to the database because you're not passing a username and password.

If your application asks the user to login to the db with a username and password, then pass those. If you're using integrated NT/SQL Server security, then perhaps the easier way is to create a generic username/password that has only read access to the db tables, stored procs, etc., and use that username/password in your application. Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[bburnell]

I've already tried forcing my username/password into the statement and I still get the same error.
Brett Please visit my websites!

http://www.smsforce.com

http://www.goodguyz.com

 

[bburnell]

I got it to work!

'*** CHANGE THE ODBC/OLE DB DSN ON THE FLY! ***
'*** SystemUsed is either "test" [S6] or "prod" (DELL6300] ***
dim ConnectionStr as string
ConnectionStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Collections;Data Source=" & SystemUsed
crxrpt.Database.AddOLEDBSource connectionstr, "TABLENAME"

Brett Please visit my websites!

http://www.smsforce.com

http://www.goodguyz.com