Visual Basic 2010 - Check if Access Database Exists upon program launch

I have created a program that uses an Access database located in a shared folder.  I want to:

1) Make the program check if the Access database exists.

2) If it does, then the program loads. If not, then the user can change the current connection string to reflect where it is.  If it exists, the program loads.

I have seen countless forums and guides that show how checking the connection state is done with SQL, but not with Access.  The PROVIDER is Microsoft.ACE.OLEDB.12.0.

How can I accomplish all of this?
getekeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
gamarrojgqConnect With a Mentor Commented:
ok, my bad there is a problem with the previous attached code, if the user set the right DB Path, even if the Try Catch does not throw an exceptio still will ask for the path, here is the Fix
Dim strBaseStringConnection As String
        Dim strStringConnection As String
        Dim olcConnection As New OleDbConnection
        Dim blnAskForDBPath As Boolean = False
        Dim strDBPath As String

        'The Default MDB path
        strDBPath = "C:\Access.MDB"

        'The Default String Connection to try the first time
        strBaseStringConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<MDB File>;Persist Security Info=False;"

        Do While True

            'Assing the MDB Path
            strStringConnection = strBaseStringConnection.Replace("<MDB File>", strDBPath)
            olcConnection.ConnectionString = strStringConnection

            Try
                'Try to open the DB
                olcConnection.Open()
                blnAskForDBPath = False
            Catch ex As Exception
                'If there is a problem with the Connection String, Ask for the DBPath to the user
                blnAskForDBPath = True
            End Try

            If blnAskForDBPath Then
                strDBPath = InputBox("Invalid DB. Plase type the DB Path:", "DB Path", "")
                If strDBPath.Trim.Length = 0 Then
                    'If the User Close the Inputbox or press Cancel, Finish the Application
                    End
                End If
            Else
                Exit Do
            End If

        Loop

Open in new window

0
 
gamarrojgqCommented:
Hi,

You can use System.IO.File.Exists () to verify that the MDB file exist. something like this

If System.IO.File.Exists ("YOURMDBPATH") Then
    'Your code here
End if
0
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
RickCommented:
How do you connect to your Access database?

Just use a Try Catch block:

Try
  cnn.open()
  ' Successfully connected... do whatever...
Catch
  ' Connection unsuccessful... do whatever...
End Try
0
 
getekeAuthor Commented:
That is good, but I am more or less looking for verifying that the connection string is correct as well, and can be changed as needed to whatever the user specifies.  I want the program to be able to adapt to wherever I place it by having it: (1) check if it exists where the connection string has it (2) allow the user to change the connection string as needed.
0
 
gamarrojgqCommented:
You can do something like the attached code (assuming you are usin System.Data.OleDB
Dim strBaseStringConnection As String
        Dim strStringConnection As String
        Dim olcConnection As New OleDbConnection
        Dim blnAskForDBPath As Boolean = False
        Dim strDBPath As String

        'The Default MDB path
        strDBPath = "C:\Access.MDB"

        'The Default String Connection to try the first time
        strBaseStringConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<MDB File>;Persist Security Info=False;"

        Do While True

            'Assing the MDB Path
            strStringConnection = strBaseStringConnection.Replace("<MDB File>", strDBPath)
            olcConnection.ConnectionString = strStringConnection

            Try
                'Try to open the DB
                olcConnection.Open()
            Catch ex As Exception
                'If there is a problem with the Connection String, Ask for the DBPath to the user
                blnAskForDBPath = True
            End Try

            If blnAskForDBPath Then
                strDBPath = InputBox("Invalid DB. Plase type the DB Path:", "DB Path", "")
                If strDBPath.Trim.Length = 0 Then
                    'If the User Close the Inputbox or press Cancel, Finish the Application
                    End
                End If
            Else
                Exit Do
            End If

        Loop

Open in new window

0
 
getekeAuthor Commented:
Thanks for the information. I will try it out and see what happens.
0
 
getekeAuthor Commented:
@gamarrojgq  The code worked. Thanks.

Now how do I save the connection string using that same code that you showed?
0
 
gamarrojgqCommented:
ok, great, glad to help, dont forget  to accep the solution

Where do you want to save it? in a file? from where are you reading the first one?
0
 
getekeAuthor Commented:
@gamarrojgq I will accept once I get this last thing resolved.

How do I save the connection string into the app.config so that everytime after this (using your code), the program will use the new connection string?
0
 
gamarrojgqCommented:
What Framework are you using? 3.5? 4?
0
 
getekeAuthor Commented:
.NET Framework 4
0
 
getekeConnect With a Mentor Author Commented:
I have finally figured out how to save the connection strings into the configuration file correctly.
 
'Opens the configuration file
Dim config As Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
'Sets the new connection string from a variable I set earlier
Dim NewConnectionString As String = ReplacementConnectionString

'Changes the connection string to reflect the new one
config.ConnectionStrings.ConnectionStrings("connection_string_name").ConnectionString = NewConnectionString
'Saves the changes
config.Save(ConfigurationSaveMode.Minimal)
'Refreshes the changes
ConfigurationManager.RefreshSection("connectionStrings")

Open in new window


Thanks for all your help guys.  Points go to gamarrojgq for leading me to the answer.
0
 
gamarrojgqCommented:
ok, great you can figured out!! sorry if I did not answer early
0
 
getekeAuthor Commented:
I was able to find the last part of my question, but full points go to gamarrojgq since he pointed me towards the right answer.
0
All Courses

From novice to tech pro — start learning today.