Results 1 to 10 of 10

Thread: [RESOLVED] Access database failing to connect

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2015
    Posts
    25

    Resolved [RESOLVED] Access database failing to connect

    I haven't really done a lot of coding, with vb and access so this could potentially be a simple problem to fix.
    Code:
    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim Conn As System.Data.OleDb.OleDbConnection
            Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\William\Desktop\Project latest backup\WillC.accdb; Userid =admin;Password=password;"
            Conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
            Try
                If Conn.State = ConnectionState.Open Then Conn.Close()
                Conn.Open()
                Dim sql As String = "insert into tblUserDetails ([UserName], Username, Password) values ('" & TextBox1.Text & "','" & TextBox2.Text & "')"
    
                Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql, Conn)
                sqlCom.Connection = Conn
                Dim result As Integer = sqlCom.ExecuteNonQuery()
                sqlCom.Dispose()
                Conn.Close()
                If result > 0 Then
                    MessageBox.Show("Successfully created.")
                Else
                    MessageBox.Show("Failure to create.")
                End If
                TextBox1.Text = ""
                TextBox2.Text = ""
            
            Catch ex As Exception
                MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    When I try to run the program the message box of the failure to connect comes up.

  2. #2
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,623

    Re: Access database failing to connect

    Why dont you put the actual exception raised in your message box instead, that way you will get a message telling you why it errored -
    Code:
    MessageBox.Show(ex.Message)
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  3. #3
    Addicted Member thetimmer's Avatar
    Join Date
    Jan 2014
    Location
    Plano, Texas
    Posts
    243

    Re: Access database failing to connect

    this line is suspicious...
    Code:
    Dim sql As String = "insert into tblUserDetails ([UserName], Username, Password) values ('" & TextBox1.Text & "','" & TextBox2.Text & "')"
    You have 3 column names, ([UserName], Username, Password), but are only supplying 2 values, values ('" & TextBox1.Text & "','" & TextBox2.Text & "')".
    _____________
    Tim

    If anyone's answer has helped you, please show your appreciation by rating that answer.
    When you get a solution to your issue remember to mark the thread Resolved.


    reference links

  4. #4
    Addicted Member thetimmer's Avatar
    Join Date
    Jan 2014
    Location
    Plano, Texas
    Posts
    243

    Re: Access database failing to connect

    that being said, definitely do what NeedSomeAnswers said because that will help you overall as a programmer to solve things on your own.
    _____________
    Tim

    If anyone's answer has helped you, please show your appreciation by rating that answer.
    When you get a solution to your issue remember to mark the thread Resolved.


    reference links

  5. #5
    Addicted Member thetimmer's Avatar
    Join Date
    Jan 2014
    Location
    Plano, Texas
    Posts
    243

    Re: Access database failing to connect

    Also, I don't think connection string paths can have spaces.
    _____________
    Tim

    If anyone's answer has helped you, please show your appreciation by rating that answer.
    When you get a solution to your issue remember to mark the thread Resolved.


    reference links

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,963

    Re: Access database failing to connect

    Quote Originally Posted by thetimmer View Post
    Also, I don't think connection string paths can have spaces.
    Actually, in connectionstrings, you can... since the field delimieter is the semicolon, as long as you don't have one of those in your path, you're fine.

    Quote Originally Posted by thetimmer View Post
    this line is suspicious...
    Code:
    Dim sql As String = "insert into tblUserDetails ([UserName], Username, Password) values ('" & TextBox1.Text & "','" & TextBox2.Text & "')"
    You have 3 column names, ([UserName], Username, Password), but are only supplying 2 values, values ('" & TextBox1.Text & "','" & TextBox2.Text & "')".
    Yeah... that's definately going to cause an error.

    Quote Originally Posted by NeedSomeAnswers View Post
    Why dont you put the actual exception raised in your message box instead, that way you will get a message telling you why it errored -
    Code:
    MessageBox.Show(ex.Message)
    And when you do, you'll find out that the error ISN'T from the connection, but the SQL. You wrapped everything in the same error handler, so no matter where the error is, it will be reported as a "connection error."

    Best practice would be to trap for specific errors, then more generic errors.

    Code:
            
            Catch exCon As DbConnectionError
                MessageBox.Show("Failed to connect to Database." & ex.Message, "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Catch exSql As DbCommandError
                MessageBox.Show("Failed to execute command. " & ex.Message, "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Catch ex As Exception
                MessageBox.Show("Unknown error. " & ex.Message, "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    the DbConnectionError and DbCommandError aren't correct, but are simply there to illustrate how to capture specific errors.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jan 2015
    Posts
    25

    Re: Access database failing to connect

    Okay, thanks for all the help. I implemented the MessageBox.Show(ex.Message) and the error I received was 'Cannot start your application. The workgroup file is missing or opened exclusively by another user'. I assumed this meant that it couldn't find my database so I moved the location. I also deleted the admin part of the code to see if that made any difference as there is no admin on my database, only a password
    Code:
    Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\William\Documents\WillC.accdb;Userid;Password=password;"
    Because of this, the error changed to 'Could not find installable ISAM' Have I made things worse by doing this?

  8. #8
    Addicted Member thetimmer's Avatar
    Join Date
    Jan 2014
    Location
    Plano, Texas
    Posts
    243

    Re: Access database failing to connect

    I'm not sure but looking at http://www.connectionstrings.com/access/

    Code:
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
    Jet OLEDB:Database Password=MyDbPassword;
    they have that Jet OLEDB piece in there and no UserID at all. Try that and see.



    With database password
    This is the connection string to use when you have an Access 2007 - 2013 database protected with a password using the "Set Database Password" function in Access.
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
    Jet OLEDBatabase Password=MyDbPassword;
    Some reports of problems with password longer than 14 characters. Also that some characters might cause trouble. If you are having problems, try change password to a short one with normal characters.Note! Reports say that a database encrypted using Access 2010 - 2013 default encryption scheme does not work with this connection string. In Access; try options and choose 2007 encryption method instead. That should make it work. We do not know of any other solution. Please get in touch if other solutions is available!
    _____________
    Tim

    If anyone's answer has helped you, please show your appreciation by rating that answer.
    When you get a solution to your issue remember to mark the thread Resolved.


    reference links

  9. #9
    Addicted Member thetimmer's Avatar
    Join Date
    Jan 2014
    Location
    Plano, Texas
    Posts
    243

    Re: Access database failing to connect

    Did you ever get this resolved? I had another idea for your issue but if you have it I won't waste your time.
    _____________
    Tim

    If anyone's answer has helped you, please show your appreciation by rating that answer.
    When you get a solution to your issue remember to mark the thread Resolved.


    reference links

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jan 2015
    Posts
    25

    Re: Access database failing to connect

    I did yes, I did it like this
    Code:
     Private dtUsers As DataTable
    
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
    
            Dim Builder As New OleDb.OleDbConnectionStringBuilder With
                {
                    .Provider = "Microsoft.ACE.OLEDB.12.0",
                    .DataSource = IO.Path.Combine(Application.StartupPath, "WillCDatabase.accdb")
                }
    
            '
            ' defines the password of the database
            '
            Builder.Add("Jet OLEDB:Database Password", "password")
    
            Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                '
                ' Where tblUserDetails contains user names and passwords
                '
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT UserName,Password FROM tblUserDetails"
                    }
    
                    cn.Open()
                    dtUsers = New DataTable
                    dtUsers.Load(cmd.ExecuteReader)
                End Using
            End Using
    
        End Sub
        Private Sub cmdLogin_Click(ByVal sender As Object, ByVal e As EventArgs) Handles cmdlogin.Click
            If txtUserName.Text = "" Or txtPassword.Text = "" Then 'checks that the text boxes aren't blank and gives an error message if they are
                MsgBox("Please enter all login details")
            ElseIf Not String.IsNullOrWhiteSpace(txtUserName.Text) AndAlso Not String.IsNullOrWhiteSpace(txtPassword.Text) Then
                Dim Result = dtUsers.Select(String.Format("UserName='{0}' AND Password = '{1}'", txtUserName.Text, txtPassword.Text))
                If Result.Count = 1 Then
                    Me.Hide()
                    Form2.Show()
    
                ElseIf PasswordAttempts = 3 Then
                    MsgBox("Maximum number of attempts reached, you've been logged out")
                    Close()
    
                Else
                    MsgBox("Incorrect login, please try again")
                    PasswordAttempts = PasswordAttempts + 1
                    txtUserName.Clear()
                    txtPassword.Clear()
    
                End If
    
            End If
        End Sub
    Thanks for the help though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width