Set Database Location

Hi all i have question.I use Access and CR 8.5.
I have produced many reports.. i used a data environment to retrieve a database.I fixed the location in partition C.I never think to try to put my system in other partition before this..can someone tell me the way where should i retrieve a Database so that its flexible to run anywhere..

Thanks in advance

I normally use ADODB commands only for all DB work. I allow the user to install the app and DB where ever the wish. I then use the registry to store the database location and on app startup I retrieve that registry setting into a global variable that I use whe I call to open database and pass that location to CR to set the location of all tables in the report.

Quote:

---

Originally Posted by GaryMazzone

I normally use ADODB commands only for all DB work. I allow the user to install the app and DB where ever the wish. I then use the registry to store the database location and on app startup I retrieve that registry setting into a global variable that I use whe I call to open database and pass that location to CR to set the location of all tables in the report.

---

Thanks a lot...but can u tell me more details how to do that..

Are you using VB to connect to the database? or is this all Access?

Quote:

---

Originally Posted by GaryMazzone

Are you using VB to connect to the database? or is this all Access?

---

Im using a vb6

This is how I do this:

gstrLoc and gDBName are global variables that are declared in a module

This runs from Sub Main:

VB Code:

'Get the DBData
gstrLoc = GetSetting(App.Title, "Settings", "DBPath")
gDBName = GetSetting(App.Title, "Settings", "DBName")

This is a sub called Open_DB that is called from Sub Main (gDBConn is a global var that is defined as and ADODB.Connection):

VB Code:

Public Sub Open_DB()
Dim FSys As FileSystemObject
Dim intDot As Integer
 
On Error GoTo DBOpenError
Set FSys = New FileSystemObject
If gDBName <> "" And FSys.FileExists(gstrLoc & "\" & gDBName) Then
    Set gDBConn = New ADODB.Connection
    If Not gDBConn.State Then
        With gDBConn
            .Provider = "Microsoft.Jet.Oledb.4.0"
            .Properties("Jet OLEDB:System Database") = gstrLoc & "\RSM.mdw"
            .Properties("Password") = "Sam23!BigFoot"
            .Properties("User ID") = "RSMAdmin"
            .ConnectionString = gstrLoc & "\" & gDBName
            .Open
        End With
    End If
Else
    gstrLoc = ""
    gDBName = ""
    frmOptions.Show
    frmOptions.cmdBrowse.Value = True
End If
intDot = InStr(1, gDBName, ".")
fMainForm.Caption = "Radiation Safety Manager - " & Left(gDBName, intDot - 1)
Exit Sub
DBOpenError:
    gstrLoc = ""
    gDBName = ""
    frmOptions.Show
    frmOptions.cmdBrowse.Value = True
    
End Sub

When the app closes I write the DB data back to the registry (incase the user chages the DB Name or Location). This is when the main form closes (unloads)

VB Code:

SaveSetting App.Title, "Settings", "DBName", gDBName
        SaveSetting App.Title, "Settings", "DBPath", gstrLoc

For the CR I have a sub that set the DB Name and Location:

VB Code:

Public Sub Set_Crystal_Datatables(ByVal intNum As Integer)
Dim i As Integer
 
For i = 0 To intNum
    fMainForm.crRep.DataFiles(i) = gstrLoc & "\" & gDBName
Next i
 
End Sub

Thanks a lot..but its look very complicated..im not expert..very difficult for me to understand...

ok let me try it out first then i will tell u..Thanks a lot

Crystal Report

if you want to install the database into the default directory where your app is installed in other systems, then you can use App.Path to get your application's installation path. and you can refer your database path with this, suppose, you have installed in D:\SomeDir\SomeSubDir then your App.Path will return this, You can use in your project App.Path & "\YourDatabaseFile.Mdb"
And for Crystal if you design the report with a dsn name then in your app you can create the dsn whenever the app is loaded.

To create DSN add these lines into your projects loading code...

VB Code:

'Setting the Database to the DSN for Crystal Report use
    Dim strAttribs As String
    strAttribs = "Description=Your ODBC Driver Description" & Chr$(13) & "DBQ=" & DBName
    rdoEngine.rdoRegisterDataSource "YourDSNName", "Microsoft Access Driver (*.Mdb)", True, strAttribs

Quote:

---

Originally Posted by ganeshmoorthy

for Crystal if you design the report with a dsn name then in your app you can create the dsn whenever the app is loaded.

---

can u tell me details how to create the dsn

i edited my previous post with the create dsn code

The only suitable solution according to ur answer is that u create DSN and call all the database tables trough that dsn in reports.

It will be helpful as when u change the database location or put it on network then u have nothing do by ur self , the report will update its database location itself.

Automatically setting DSN in ODBC :thumb:
for more detail to create run time dsn you can clink on the above link and check the post 2
;)

It's quite simple to change the database location in a Crystal Report, see this post... http://www.vbforums.com/showthread.php?t=423542
If you're using the Crystal OCX it's just as easy...

VB Code:

Report.DataFiles(0) = "path & name of your database"

I've never needed to use a DSN. In my opinion they're cumbersome.