-
Jan 17th, 2017, 03:35 AM
#1
Thread Starter
Member
[RESOLVED] Getting Data from MS Access located in Remote Server
Currently, I am successfully getting data from MS Access into listboxview from the local LAN.
We have another Access file in remote Server which we mapped as S drive i.e. S:\dbfile\test.accdb. I am trying the below string to get the data from Remote Server. However, the data is not populating and no errors throwing.
below is the code using for populating listview
vb.net Code:
Sub datapopulate()
Dim connection As OleDbConnection
Dim command As OleDbCommand
Dim data_reader As OleDbDataReader
'setup listview
connection = New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=s:\dbfile\test.accdb")
connection.Open()
'reading data from table
command = New OleDbCommand("SELECT * FROM tbllist", connection)
data_reader = command.ExecuteReader
'add data to listview
If data_reader.HasRows Then
While data_reader.Read
Dim newitem As New ListViewItem()
newitem.Text = data_reader.GetValue(2) 'first column
newitem.SubItems.Add(data_reader.GetValue(3)) 'second column
newitem.SubItems.Add(data_reader.GetValue(5)) 'second column
newitem.SubItems.Add(data_reader.GetValue(6)) 'second column
ListView1.Items.Add(newitem)
End While
End If
End Sub
I also tried the below connection string. But still no luck.
vb.net Code:
connection = New OleDbConnection("Provider=MS Remote;" &
"Remote Server=http://111.11.100.11;" &
"Remote Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source=/shared/Helpdesk/Quality\dbfile\test.accdb;Persist Security Info=False")
Does any one has idea why the data is not populating?
-
Jan 17th, 2017, 07:13 AM
#2
New Member
Re: Getting Data from MS Access located in Remote Server
Is there a reason you are using Ole objects to get the Access data? I thought that DAO and ADO were the two still supported connection methods- perhaps this is just legacy code but unless theres another reason why Ole I would recommend switching to one of the newer connection types. I use DAO quite frequently and connecting to a database and pulling data is quite straightforward...
Let me know if interested in switching to DAO and I can help with some of the code.
Regards,
TheSilkCode
-
Jan 17th, 2017, 10:17 AM
#3
Thread Starter
Member
Re: Getting Data from MS Access located in Remote Server
Originally Posted by TheSilkCode
Is there a reason you are using Ole objects to get the Access data? I thought that DAO and ADO were the two still supported connection methods- perhaps this is just legacy code but unless theres another reason why Ole I would recommend switching to one of the newer connection types. I use DAO quite frequently and connecting to a database and pulling data is quite straightforward...
Let me know if interested in switching to DAO and I can help with some of the code.
Regards,
TheSilkCode
Hi TheSkilcode, thank you for your reply. I don't have restrictions to use OLE. Yes. I can switch to DAO.
-
Jan 17th, 2017, 11:19 AM
#4
Re: Getting Data from MS Access located in Remote Server
Since this is .NET code, using OleDB Namespace is the preferred method here...
Don't switch ADO or DAO or anything legacy at this point. Yes, you could use either one but it may not be worth the effort an the changes. Also... I don't think old ADO/DAO can handle the ACCDB format... I could be wrong though.
As for the problem... put a break point and step through the code to see what is going on. It might be that something isn't what you expect it to be.
Also... where are you calling this code from? Is it in the Form Load event? And is it running on a 64-bit Windows environment by chance? If the answer to both of thsoe is "yes" there maybe an exception, but because of a difference of opinion with the Windows group... Windows simply swallows errors that happen on the initial Form Load event. You're best bet is to wrap the whole thing in a Try/catch and have it explicitly tell you the problem... or move the call to outside the form load event and run it from a button click or some other event to see what exception you're getting. I'm fairly certain you are getting one, but because Windows eats the exception, you're not finding out about it.
-tg
-
Jan 17th, 2017, 12:26 PM
#5
Thread Starter
Member
Re: Getting Data from MS Access located in Remote Server
Originally Posted by techgnome
Since this is .NET code, using OleDB Namespace is the preferred method here...
Don't switch ADO or DAO or anything legacy at this point. Yes, you could use either one but it may not be worth the effort an the changes. Also... I don't think old ADO/DAO can handle the ACCDB format... I could be wrong though.
As for the problem... put a break point and step through the code to see what is going on. It might be that something isn't what you expect it to be.
Also... where are you calling this code from? Is it in the Form Load event? And is it running on a 64-bit Windows environment by chance? If the answer to both of thsoe is "yes" there maybe an exception, but because of a difference of opinion with the Windows group... Windows simply swallows errors that happen on the initial Form Load event. You're best bet is to wrap the whole thing in a Try/catch and have it explicitly tell you the problem... or move the call to outside the form load event and run it from a button click or some other event to see what exception you're getting. I'm fairly certain you are getting one, but because Windows eats the exception, you're not finding out about it.
-tg
Thank you @techgnome
yes. The sub is calling at Form Load event. I will put the code outside and call from a button and will let you know the results. Thanks for the thoughts.
-
Jan 17th, 2017, 03:11 PM
#6
Re: Getting Data from MS Access located in Remote Server
Originally Posted by TheSilkCode
I thought that DAO and ADO were the two still supported connection methods
I'm afraid not, DAO is seriously out of date.
We have been told by Microsoft since 1998 (or maybe earlier) to not use it, and for several years (since 2003?) it has been officially obsolete (which is much worse than "unsupported"), and is very likely to fail in certain situations.
ADO was the replacement for DAO, so is slightly better... but it has been superseded by ADO.Net (which includes OleDbConnection and SQLConnection etc), so that is what should generally be used.
-
Jan 18th, 2017, 04:10 AM
#7
Thread Starter
Member
Re: Getting Data from MS Access located in Remote Server
Originally Posted by techgnome
Since this is .NET code, using OleDB Namespace is the preferred method here...
Don't switch ADO or DAO or anything legacy at this point. Yes, you could use either one but it may not be worth the effort an the changes. Also... I don't think old ADO/DAO can handle the ACCDB format... I could be wrong though.
As for the problem... put a break point and step through the code to see what is going on. It might be that something isn't what you expect it to be.
Also... where are you calling this code from? Is it in the Form Load event? And is it running on a 64-bit Windows environment by chance? If the answer to both of thsoe is "yes" there maybe an exception, but because of a difference of opinion with the Windows group... Windows simply swallows errors that happen on the initial Form Load event. You're best bet is to wrap the whole thing in a Try/catch and have it explicitly tell you the problem... or move the call to outside the form load event and run it from a button click or some other event to see what exception you're getting. I'm fairly certain you are getting one, but because Windows eats the exception, you're not finding out about it.
-tg
update:
I have just modified the below lines of code and it seems working.
from
vb.net Code:
Dim newitem As New ListViewItem() newitem.Text = data_reader.GetValue(2) 'first column newitem.SubItems.Add(data_reader.GetValue(3)) 'second column newitem.SubItems.Add(data_reader.GetValue(5)) 'second column newitem.SubItems.Add(data_reader.GetValue(6)) 'second column
to
vb.net Code:
Dim newitem As New ListViewItem() newitem.Text = data_reader.GetValue(2).tostring() 'first column newitem.SubItems.Add(data_reader.GetValue(3).tostring()) 'second column newitem.SubItems.Add(data_reader.GetValue(5).tostring()) 'second column newitem.SubItems.Add(data_reader.GetValue(6).tostring()) 'second column
-
Jan 18th, 2017, 08:18 AM
#8
Re: [RESOLVED] Getting Data from MS Access located in Remote Server
If they're all strings... use GetString instead of GetValue and ToString:
Code:
Dim newitem As New ListViewItem()
newitem.Text = data_reader.GetString(2) 'first column
newitem.SubItems.Add(data_reader.GetString(3)) 'second column
newitem.SubItems.Add(data_reader.GetString(5)) 'second column
newitem.SubItems.Add(data_reader.GetString(6)) 'second column
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|