Remove From My Forums

Connecting to MS Access database

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

Edited by Rob Slabbekoorn Friday, May 29, 2020 1:58 PM

Friday, May 29, 2020 1:54 PM

Answers

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
- Proposed as answer by Xingyu ZhaoMicrosoft contingent staff Monday, June 1, 2020 1:26 AM
- Marked as answer by Rob Slabbekoorn Wednesday, June 3, 2020 8:10 AM
Friday, May 29, 2020 2:13 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

Marked as answer by Rob Slabbekoorn Wednesday, June 3, 2020 8:11 AM

Tuesday, June 2, 2020 3:22 PM

All replies

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
- Proposed as answer by Xingyu ZhaoMicrosoft contingent staff Monday, June 1, 2020 1:26 AM
- Marked as answer by Rob Slabbekoorn Wednesday, June 3, 2020 8:10 AM
Friday, May 29, 2020 2:13 PM
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
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
- Edited by Rob Slabbekoorn Tuesday, June 2, 2020 11:56 AM
Tuesday, June 2, 2020 10:37 AM
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
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

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.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Marked as answer by Rob Slabbekoorn Wednesday, June 3, 2020 8:11 AM

Tuesday, June 2, 2020 3:22 PM

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

Edited by Cor Ligthert Tuesday, June 2, 2020 5:34 PM

Tuesday, June 2, 2020 5:33 PM

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

Answered by:

Connecting to MS Access database

Question

Answers

All replies