none
Connecting to MS Access database RRS feed

  • Question

  • Hi,

    I'm trying to connect to a database on my network. When I make a copy of the Access file to a local machine it works fine. But in my network I get the message that no OLE DB-provider is set as provider to the connectionstring. But there is as you can notice in my code. Any idea what this problem can couse and of course do you have a solution?

    Kind regards,

    Rob

    Imports System.Data.OleDb
    Public Class Form1
        Public con As New OleDb.OleDbConnection
        Public cm As New OleDb.OleDbCommand
        Public dr As OleDbDataReader
        Dim ds As New DataSet
        Dim inc As Integer
        Dim MaxRows As Integer
        Private mdwfile As String
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            'LADEN
            Dim dbProvider As String
            Dim dbSource As String
            Dim mdb As String
            Dim da As New OleDbDataAdapter
    
            dbProvider = "Microsoft.ACE.OLEDB.12.0;"
            dbSource = "SQLOLEDB;Data Source = G:\Systeembeheer\Projecten\STP Offerte\Prosfora.accdb;Persist Security Info=False;"
            con.ConnectionString = dbProvider & dbSource
            con.Open()
    
            If con.State = ConnectionState.Open Then
                MsgBox("Connected")
            Else
                MsgBox("Not Connected!")
            End If
    
            Try
                mdb = "SELECT * FROM nawbes where NAW_RELNR = '" + TextBox1.Text + "'"
                da = New OleDb.OleDbDataAdapter(mdb, con)
                da.Fill(ds, "ProsforaDataset")
                con.Close()
                inc = -1
    
                If ("NAW_RELNR").ToString = TextBox1.Text Then
                    TextBox2.Text = ("NAW_VOORLETTERS").ToString() + " " + ("NAW_VOORVOEGSELS").ToString() + " " + ("NAW_NAAM").ToString()
                    TextBox3.Text = ("NAW_STRAAT").ToString() + " " + ("NAW_HUISNUMMER").ToString() + " " + ("NAW_ADRES_TOEVOEGING").ToString()
                    TextBox4.Text = ("NAW_POSTCODE").ToString()
                    TextBox5.Text = ("NAW_PLAATS").ToString()
                End If
                con.Open()
    
            Catch ex As Exception
                MsgBox(ex.ToString())
            Finally
                MsgBox("Dit nummer bestaat niet")
            End Try
    
            con.Close()
        End Sub


    Friday, May 29, 2020 1:54 PM

Answers

  • Try changing your connection string as it looks odd.

    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Systeembeheer\Projecten\STP Offerte\Prosfora.accdb"

    Basic example found here.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 29, 2020 2:13 PM
    Moderator
  • Looks like you are after a single record, in that case you should do something like the following which of course is a different database than yours.

    Container for results back to the form

    Public Class Customer
        Public Property Identifier() As Integer
        Public Property CompanyName() As String
        Public Property ContactTitleId() As Integer
        Public Property Address() As String
        Public Property Title() As String
        Public Property City() As String
        Public Property PostalCode() As String
        Public Property Country() As String
    End Class

    Class to get a customer by id (could be any column in the table)

    Public Class DatabaseOperations
        Public Shared Function LoadCustomer(identifier As Integer) As Customer
    
            Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb")
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = <SQL>
                        SELECT 
                            C.Identifier, 
                            C.CompanyName, 
                            CT.ContactTitleId, 
                            CT.Title, C.Address, 
                            C.City, C.PostalCode, 
                            C.Country
                        FROM 
                            ContactTitle AS CT 
                        INNER JOIN 
                            Customers AS C ON CT.ContactTitleId = C.ContactTitleId
                        WHERE C.Identifier = @Identifier
                        </SQL>.Value
    
    
    
                    Try
                        cmd.Parameters.AddWithValue("@Identifier", identifier)
                        cn.Open()
    
                        Dim reader = cmd.ExecuteReader()
                        If reader.HasRows Then
                            Dim customer As New Customer
                            reader.Read()
                            customer.Identifier = identifier
                            customer.CompanyName = reader.GetString(1)
                            customer.ContactTitleId = reader.GetInt32(2)
                            customer.Title = reader.GetString(3)
                            customer.Address = reader.GetString(4)
                            customer.City = reader.GetString(5)
                            customer.PostalCode = reader.GetString(6)
                            customer.Country = reader.GetString(7)
                            Return customer
                        Else
                            Return Nothing
                        End If
    
                    Catch ex As Exception
                        Return Nothing
                    End Try
                End Using
            End Using
        End Function
    End Class

    Get a customer

    Private Sub GetCustomerByIdentifierButton_Click(sender As Object, e As EventArgs) _
        Handles GetCustomerByIdentifierButton.Click
    
        If Not String.IsNullOrWhiteSpace(identifierTextBox.Text) Then
            Dim id As Integer
            If Integer.TryParse(identifierTextBox.Text, id) Then
                Dim customer = DatabaseOperations.LoadCustomer(id)
                If customer IsNot Nothing Then
                    ' we have a customer
                Else
                    ' customer not found
                End If
            End If
        End If
    
    End Sub
    Total time to execute is a millisecond.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, June 2, 2020 3:22 PM
    Moderator

All replies

  • Try changing your connection string as it looks odd.

    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Systeembeheer\Projecten\STP Offerte\Prosfora.accdb"

    Basic example found here.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 29, 2020 2:13 PM
    Moderator
  • Hi Rob Slabbekoorn,

    How is the question going? If your question has been answered then please click the "Mark as Answer" Link at the bottom of the correct post(s), so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 2, 2020 9:31 AM
    Moderator
  • Hi Xingyu,

    Thank you for your answer. It helpt! now I'm connected the requested data will been shown in my form. But,.... it takes 53 seconds to recall the data and show it in my form. Do you know what may couse the delay? When I look up a specific customer ID-number in Access itself (NAW_RELNR in Dutch) the requested data will be find in a second.

    Kind regards,

    Rob


    Tuesday, June 2, 2020 10:37 AM
  • Rob,

    Don't forget the Access database you use is a file, that is not the same as a database server like SQL Server.


    Success
    Cor

    Tuesday, June 2, 2020 11:45 AM
  • Hi Cor,

    So, that means that extracting data from a file takes a lot more time then from a SQL server?

    Sorry I'm new here on databases from a network drive.

    Kind regards,

    Rob

    Tuesday, June 2, 2020 1:52 PM
  • Looks like you are after a single record, in that case you should do something like the following which of course is a different database than yours.

    Container for results back to the form

    Public Class Customer
        Public Property Identifier() As Integer
        Public Property CompanyName() As String
        Public Property ContactTitleId() As Integer
        Public Property Address() As String
        Public Property Title() As String
        Public Property City() As String
        Public Property PostalCode() As String
        Public Property Country() As String
    End Class

    Class to get a customer by id (could be any column in the table)

    Public Class DatabaseOperations
        Public Shared Function LoadCustomer(identifier As Integer) As Customer
    
            Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb")
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = <SQL>
                        SELECT 
                            C.Identifier, 
                            C.CompanyName, 
                            CT.ContactTitleId, 
                            CT.Title, C.Address, 
                            C.City, C.PostalCode, 
                            C.Country
                        FROM 
                            ContactTitle AS CT 
                        INNER JOIN 
                            Customers AS C ON CT.ContactTitleId = C.ContactTitleId
                        WHERE C.Identifier = @Identifier
                        </SQL>.Value
    
    
    
                    Try
                        cmd.Parameters.AddWithValue("@Identifier", identifier)
                        cn.Open()
    
                        Dim reader = cmd.ExecuteReader()
                        If reader.HasRows Then
                            Dim customer As New Customer
                            reader.Read()
                            customer.Identifier = identifier
                            customer.CompanyName = reader.GetString(1)
                            customer.ContactTitleId = reader.GetInt32(2)
                            customer.Title = reader.GetString(3)
                            customer.Address = reader.GetString(4)
                            customer.City = reader.GetString(5)
                            customer.PostalCode = reader.GetString(6)
                            customer.Country = reader.GetString(7)
                            Return customer
                        Else
                            Return Nothing
                        End If
    
                    Catch ex As Exception
                        Return Nothing
                    End Try
                End Using
            End Using
        End Function
    End Class

    Get a customer

    Private Sub GetCustomerByIdentifierButton_Click(sender As Object, e As EventArgs) _
        Handles GetCustomerByIdentifierButton.Click
    
        If Not String.IsNullOrWhiteSpace(identifierTextBox.Text) Then
            Dim id As Integer
            If Integer.TryParse(identifierTextBox.Text, id) Then
                Dim customer = DatabaseOperations.LoadCustomer(id)
                If customer IsNot Nothing Then
                    ' we have a customer
                Else
                    ' customer not found
                End If
            End If
        End If
    
    End Sub
    Total time to execute is a millisecond.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, June 2, 2020 3:22 PM
    Moderator
  • Hi Cor,

    So, that means that extracting data from a file takes a lot more time then from a SQL server?

    Sorry I'm new here on databases from a network drive.

    Kind regards,

    Rob

    It can, a SQL server is really a server working on the IP address, Access is just a file, that opens and closes as every file and is depending of the file system of the computer it is located on. 

    However, 

    I take most time is consumed with that messagbox which every time tells if it is open or not. A dataadapter has its own open and clolse mechanism. I made revised code where I removed everything which is overdone in your code. 

    I suggest to try it, of course it is not yet tested.

    Option Strict On
    Imports System.Data.OleDb
    Public Class Form1
        Dim dt As New DataTable
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            'LADEN'
            Dim con As New OleDb.OleDbConnection
            Dim dbProvider As String
            Dim dbSource As String
            Dim mdb As String
            dbProvider = "Microsoft.ACE.OLEDB.12.0;"
            dbSource = "SQLOLEDB;Data Source = G:\Systeembeheer\Projecten\STP Offerte\Prosfora.accdb;Persist Security Info=False;"
            con.ConnectionString = dbProvider & dbSource
            Try
                mdb = "SELECT NAW_VOORLETTERS, NAW_VOORBOEGSELS FROM nawbes where NAW_RELNR = ?"
                Dim da = New OleDb.OleDbDataAdapter(mdb, con)
                da.SelectCommand.Parameters.AddWithValue("?", TextBox1.Text)
                da.Fill(dt)
                con.Close()
                If dt IsNot Nothing Then
                    TextBox2.Text = dt(0)("NAW_VOORLETTERS").ToString & dt(0)("NAW_VOORVOEGSELS").ToString()
                    'And the rest you can do yourself'
                Else
                    MsgBox("Dit nummer bestaat niet")
                End If
            Catch ex As Exception
                MsgBox(ex.ToString())
            End Try
    
        End Sub
    End Class


    Success
    Cor


    Tuesday, June 2, 2020 5:33 PM
  • Hi Karen,

    Unfortunately I did not succeed to implement your code in my project the wright way. I believe the problem is how to interpret the table items. So I send you my table items hereby. I try to translate it for you in the hope you may alter your code with my table items.

    NAW_RELNR (customer ID), NAW_VOORLETTERS (initials forename customer), NAW_VOORVOEGSELS (middle name, like Chris the Burg), NAW_NAAM (customer name), NAW_STRAAT (Address), NAW_ADRES_TOEVOEGING  (house number level, like "a", "b" or something), NAW_POSTCODE (postalcode), NAW_HUISNUMMER (house number), NAW_PLAATS (City).

    Thank you in advance.

    Best regards,

    Rob

    Wednesday, June 3, 2020 8:09 AM