Changing Database at Runtime-Urgent

[sohrabahmad]

Hi,

I am using VB 6.0(ADO Method)/MS-Access, Crystal 8.5 (through Active X Control i.e Crystl32.OCX) and create reports through DSN. As of now I have worked on 1 Database and bind all the Reports at design to DSN and DSN is connected to the Database.

Now I have 4 - 5 Database or databases may increase as per the need. Tables in all the Databases are same only Data is different but Reports for all the Database is same.

When VB Application starts users is asked to select Database. All the Data and records goes to selected Database. I want that when I run the report. Reports should retrieve the Records from selected Database Only. Whenever Database is changed Reports should retrieve the data from the selected Database.

Pls advise me How Database Can be changed at run time or how to bind reports. Actually, This practice is done in Multi-Company System. Suppose, there is Payroll Package which keeps records of Head office and 3 Branches. So, 4 Databases will be created or when any new branch open Database may increase but Reports will be same for all.

Kindly help me.

Regards,

Sohrab
[email protected]

[THEROB]

What are you using to access the data - ADO, DAO or the databases own components - If you are using ADO or DAO - just change the connect string when opening the database.

[sohrabahmad]

Mr. Therob,

I'm using ADO. Below is the code which is used to run report:

Cry1.DataFiles(0) = "D:\Sohrab\Multicompany\Dbs" & Newdatabase & ".mdb"

Cry1.ReportFileName = "D:\Sohrab\Multicompany\ Reports\ Report1.rpt"

Cry1.RetrieveDataFiles
Cry1.Action = 1

But Report is still retrieving the records from the database which was used at design time. where as here i'm passing new Database Name. but no use.
Kindly Help me.

Thanks,

[THEROB]

I think one of the lines is causing an error and hence not being executed - get rid of any on error resume next or right click on the code and choose Toggle | break on all errors.

Run the code to see where the problem is

try doing a cry1.refresh

Rob

[sohrabahmad]

Mr. Therob,

Using said code report is running but retreiving the records from the databse which was used at design time.

when using Crystal Report Control (Crystl32.Ocx) there is not
Refresh Property.
I selected Refresh button and when refresh the report nothing happens.
Kindly do something.

Thanks,
Sohrab

[THEROB]

try this:

if dir("D:\Sohrab\Multicompany\Dbs" & Newdatabase & ".mdb") = "" then 
    msgbox "there is no such file available"
    stop
end if
 
if dir("D:\Sohrab\Multicompany\ Reports\ Report1.rpt") = "" then
    msgbox "there is no such file available"
    stop
end if
 
 
Cry1.DataFiles(0) = "D:\Sohrab\Multicompany\Dbs" & Newdatabase & ".mdb"
 
Cry1.ReportFileName = "D:\Sohrab\Multicompany\ Reports\ Report1.rpt"
 
Cry1.RetrieveDataFiles
Cry1.Action = 1

If both files are available and no error occurs then the only other thing I can think of is that Datafiles(1) or another number is visible at the moment - I haven't used Crystal reports in about 2 years.

Does this help

Rob

[sohrabahmad]

Mr. Therob,
I tried this but no use. Records are still coming the database used at design time.
Both the files Database and Reports are available at their location

If Dir("D:\Sohrab\Multicompany\Dbs\" & Convar & ".mdb") = "" Then
MsgBox "there is no such file available"
Stop
End If

If Dir("D:\Sohrab\Multicompany\Reports\Report1.rpt") = "" Then
MsgBox "there is no such file available"
Stop
End If


Cry1.DataFiles(0) = "D:\Sohrab\Multicompany\Dbs\" & Convar & ".mdb"

Cry1.ReportFileName = "D:\Sohrab\Multicompany\Reports\Report1.rpt"

Cry1.RetrieveDataFiles
Cry1.Action = 1


Kindly do something.
Regards,
Sohrab

[THEROB]

Try moving the database that is referenced at design time - then starting the app - the code will break - you will be able to see where reference is made to the database - and change it there.

Hope this helps

Rob

[sohrabahmad]

Boss,

I moved Desing time database other location.
Now It display error. Unable to open Database.

Regards,
Sohrab