Solved

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

Posted on 2011-03-22
15
710 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
Industry Leaders: 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!

 

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

756 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