Solved

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

Posted on 2011-03-22
15
642 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:geteke
15 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35193591
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
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 35193618
0
 
LVL 13

Expert Comment

by:Rick
ID: 35193625
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
 

Author Comment

by:geteke
ID: 35193641
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
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35193754
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
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 500 total points
ID: 35193770
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
 

Author Comment

by:geteke
ID: 35193803
Thanks for the information. I will try it out and see what happens.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:geteke
ID: 35201860
@gamarrojgq  The code worked. Thanks.

Now how do I save the connection string using that same code that you showed?
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35202141
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
 

Author Comment

by:geteke
ID: 35202259
@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
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35202645
What Framework are you using? 3.5? 4?
0
 

Author Comment

by:geteke
ID: 35206540
.NET Framework 4
0
 

Assisted Solution

by:geteke
geteke earned 0 total points
ID: 35208900
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
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35220792
ok, great you can figured out!! sorry if I did not answer early
0
 

Author Closing Comment

by:geteke
ID: 35239101
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now