Solved

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

Posted on 2011-03-22
15
732 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 19

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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
 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

734 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