Answered by:
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
-
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.
- 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.
- Marked as answer by Rob Slabbekoorn Wednesday, June 3, 2020 8:11 AM
Tuesday, June 2, 2020 3:22 PM
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.
- 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 -
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 ZhaoMSDN 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 -
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 -
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
CorTuesday, 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.
- 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 -
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