Solved

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

Posted on 2011-03-22
15
767 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

623 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