Sign in
Microsoft.com
 
Microsoft
 
 
 

none
Connecting to MS Access database RRS feed

 , 
 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |