please help crystal report 10 logon failed when SQL three tables

[worldapart]

I am working on VB6 for crystal reports 10 Develop Edition , the database is MYSQL. i need three tables to create a report .it works fine in local LAN (any of the computers,windows),but when connecting by remote access through VPN ,an weird error comes out :Logon Failed Details:01000:[Microsoft][ODBC SQL Sever Driver][DBNETLIB]connectionopen(connect()).
but if i use only one table to create it no matter in LAN or VPN works perfectly .after googling.knowing that each table need User ID ,Password,DSN to login .but it didn't work for me .could it be possible that the SQL string is not so appropriate ?
anyone who can give me some hints or codes i would be greatly appreciate. thanks a lot in advance
here is my code:

Option Explicit

Const CONSTR = "Provider=SQLOLEDB;data Source=***;Initial Catalog=northwind; " & _
"User Id=***;Password=***"

Dim strSQL As String
Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
Dim crystal As CRAXDRT.Application 'LOADS REPORT FROM FILE
Dim report As CRAXDRT.report 'HOLDS REPORT
Dim CRXDATABASETABLE As CRAXDRT.DatabaseTable
Private Sub Form_Load()
'THIS CODE IS USED TO DISPLAY A CRYSTAL REPORTS 9 REPORT USING
'DATA PULLED FROM A MYSQL DATABASE USING ADO AND VISUAL BASIC 6
'YOU NEED REFERENCES TO: Microsoft ActiveX Data Objects 2.7 Library
' Crystal Reports 9 ActiveX Designer Run Time Library
'YOU NEED THE COMPONENT: Crystal Reports Viewer Control 9
'ADD THE CRVIEWER91 COMPONENT TO YOUR FORM AND NAME IT CRViewer.

CRViewer.EnableExportButton = True
CRViewer.DisplayBorder = False 'MAKES REPORT FILL ENTIRE FORM
CRViewer.DisplayTabs = False 'THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
CRViewer.EnableDrillDown = False 'REPORT DOES NOT SUPPORT DRILL-DOWN
CRViewer.EnableRefreshButton = False 'ADO RECORDSET WILL NOT CHANGE, NOT NEEDED
Dim strsql1 As String

Me.WindowState = 2

strsql1 = "select customer.country,customer.name,supplier.country,supplier.name,order.id,order.product from customer,supplier,order where customer.id='" & Trim(form.id1.Text) & "' and supplier.id='" & Trim(form.id2.Text) & "' and order.id='" & Trim(form.id3.Text) & "'"

If conn.State = adStateClosed Then _
conn.Open CONSTR

If rs.State = adStateOpen Then rs.Close
Set rs.ActiveConnection = conn
rs.Open strsql1, conn, adOpenKeyset, adLockOptimistic

Set crystal = New CRAXDRT.Application 'MANAGES REPORTS
Set report = crystal.OpenReport(App.Path & "\order Report.rpt") 'OPEN OUR REPORT

For Each CRXDATABASETABLE In report.Database.Tables
CRXDATABASETABLE.ConnectionProperties.DeleteAll
CRXDATABASETABLE.ConnectionProperties.Add "DSN", "***"
CRXDATABASETABLE.ConnectionProperties.Add "user id", "***"
CRXDATABASETABLE.ConnectionProperties.Add "password", "***"
Next CRXDATABASETABLE


report.DiscardSavedData 'CLEARS REPORT SO WE WORK FROM RECORDSET
report.Database.SetDataSource rs 'LINK REPORT TO RECORDSET
CRViewer.ReportSource = report 'LINK VIEWER TO REPORT
CRViewer.ViewReport 'SHOW REPORT
Do While CRViewer.IsBusy 'ZOOM METHOD DOES NOT WORK WHILE
DoEvents 'REPORT IS LOADING, SO WE MUST PAUSE
Loop 'WHILE REPORT LOADS.

CRViewer.Zoom 100

rs.Close 'ALL BELOW HERE IS CLEANUP
conn.Close
Set conn = Nothing

Set crystal = Nothing
Set report = Nothing
End Sub

Private Sub Form_Resize() 'MAKE SURE REPORT FILLS FORM
CRViewer.Top = 0 'WHEN FORM IS RESIZED
CRViewer.Left = 0
CRViewer.Height = ScaleHeight
CRViewer.Width = ScaleWidth

 

[Pilekes]

Hi, I stumbled upon your problem because I'm having similar problems... Except mine is connecting to different databases with the same report...

This might help you:
For Each CRXDATABASETABLE In Report.Database.Tables
CRXDATABASETABLE.ConnectionProperties.DeleteAll
CRXDATABASETABLE.ConnectionProperties.Add "Provider", "sqloledb"
CRXDATABASETABLE.ConnectionProperties.Add "Database Type", "OLEDB (ADO)"
CRXDATABASETABLE.ConnectionProperties.Add "Data source", "Servername"
CRXDATABASETABLE.ConnectionProperties.Add "Initial Catalog", "database"
CRXDATABASETABLE.ConnectionProperties.Add "user id", "***"
CRXDATABASETABLE.ConnectionProperties.Add "password", "***"
Next CRXDATABASETABLE

I discarded the DSN because I don't know how to use that

 

[worldapart]

HI Pilekes i finally solved the weird problem but in different way(stupid) here is what i done:
as it can't work out when make a report from three tables, even though i have sent the username & password to the report every time the tables were opened.i used the SQL Profile to test what the VB program is doing.i found that the username & password didn't work i mean they have been ignored when opening the table B and C .that is why comes out the error :logon failed.maybe my program is not so appropriate.so i throught that if i combined those three tables together before they were sent to the report.and it worked not only in local LAN but also from remote access.but it not perfect in some situation such as after combining the result which has more than one (not only has Info A but also has Info B) so the solution is temporarily,i still need the codes in perfect way.so please help or give me any hints that would be greate helpful here is what i done:first of all join the three tables A,B,C into a new table-->Mytemptable,so in table Mytemptable has the result that i need .then create a report from table Mytemptable.here is the code :
Private Sub report_Click()
Dim cmd As New ADODB.Command
Dim sqsql As String
sqsql = " A,B,C where A.number='" & Trim(form.number.Text) & "' and B.number='" & Trim(form.number.Text) & "' and C.number='" & Trim(form.number.Text) & "'"

With cmd
.ActiveConnection = cn
.CommandText = "DROP TABLE mytemptable EXEC sp_dboption 'Databasename', 'select into/bulkcopy', 'true' select A.customer,A.provider,B.date,B.number,C.total,C.INFO into Mytemptable from " & sqsql
.Execute
End With
Report.Show
End Sub
after the report was showed the rest of the codes is the same as the first issued

 

[Pilekes]

Hmmm can't really help you on that one... Since I have a multiuser application I can't let everyone create and drop tables when printing a report

But maybe when I get an answer in this topic

http://www.tek-tips.com/viewthread.cfm?qid=1234377&page=1

you'll have your answer as well