Setting a relative path to a database

[Ebisu]

Hey i have so far set up a form to save data into a database and i am using:

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0.;" & _
    "Data Source = F:\Database1.mdb"
        con.Open()

I tried saving the database as i did with images in the resource folder and used my.resources but this didnt work is there any way i can save the database into the project folder and link to it no matter whan machine i am on?

[Teme64]

I suggest using either Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) for roaming profile, or Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) for local profile to store any data.

When you use file paths in this way, your application will work in any Windows version (from XP to Vista and 7). Also, you're not relying the user to have any specific drive (F: -drive in your example).

HTH

[Ebisu]

Hi, thanks for those suggestions, i got it to work about 20 mins ago using

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0.; " & "Data Source=" & Application.StartupPath & "\" & "Booking.mdb"

This will work on any windows OS too right?

[Teme64]

Yes and no :)

It works with XP but in Vista/7 (also Server 2008) you are not supposed to save any data files to the same folder(s) where the program files are. If you do so, your application may not work if the user is a "standard user". And since the elevated privileges should be avoided, I still suggest using those special folders (they are UAC "friendly").

And one more folder is Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData) which saves app data to all users.

In your code:

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0.; " & "Data Source=" & Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData) & "\" & "Booking.mdb"

would do the trick. I mean, it works with any Windows version and with (almost) any user privileges.

HTH

[Ebisu]

i Get the error 'Could not find file 'C:\ProgramData\Booking.mdb'.' when using that

[Teme64]

You have to copy/create that db file first.

This checks if the Booking.mdb exists and opens the connection. If it doesn't, copy or create a new Booking.mdb file that you'll be using

If My.Computer.FileSystem.FileExists(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData) & "\" & "Booking.mdb") Then
      ' Use Access file
      con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0.; " & "Data Source=" & _
        Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData) & "\" & "Booking.mdb"
    Else
      ' Create a new database file or copy (template)
      My.Computer.FileSystem.CopyFile("C:\InstallationPath\Booking.mdb", _
        Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData) & "\" & "Booking.mdb")
    End If