Results 1 to 8 of 8

Thread: [RESOLVED] Getting Data from MS Access located in Remote Server

  1. #1

    Thread Starter
    Member
    Join Date
    Jan 2014
    Posts
    62

    Resolved [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:
    1. Sub datapopulate()
    2.  
    3.     Dim connection As OleDbConnection
    4.     Dim command As OleDbCommand
    5.     Dim data_reader As OleDbDataReader
    6.     'setup listview
    7.  
    8.  
    9.     connection = New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=s:\dbfile\test.accdb")
    10.     connection.Open()
    11.     'reading data from  table
    12.     command = New OleDbCommand("SELECT * FROM tbllist", connection)
    13.     data_reader = command.ExecuteReader
    14.  
    15.     'add data to listview
    16.     If data_reader.HasRows Then
    17.         While data_reader.Read
    18.  
    19.             Dim newitem As New ListViewItem()
    20.             newitem.Text = data_reader.GetValue(2) 'first column
    21.             newitem.SubItems.Add(data_reader.GetValue(3)) 'second column
    22.             newitem.SubItems.Add(data_reader.GetValue(5)) 'second column
    23.             newitem.SubItems.Add(data_reader.GetValue(6)) 'second column
    24.  
    25.  
    26.             ListView1.Items.Add(newitem)
    27.  
    28.         End While
    29.     End If
    30.  
    31. End Sub

    I also tried the below connection string. But still no luck.

    vb.net Code:
    1. connection = New OleDbConnection("Provider=MS Remote;" &
    2.                    "Remote Server=http://111.11.100.11;" &
    3.                    "Remote Provider=Microsoft.Jet.OLEDB.4.0;" &
    4.                    "Data Source=/shared/Helpdesk/Quality\dbfile\test.accdb;Persist Security Info=False")

    Does any one has idea why the data is not populating?

  2. #2
    New Member TheSilkCode's Avatar
    Join Date
    Jan 2017
    Posts
    3

    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

  3. #3

    Thread Starter
    Member
    Join Date
    Jan 2014
    Posts
    62

    Re: Getting Data from MS Access located in Remote Server

    Quote Originally Posted by TheSilkCode View Post
    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.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Member
    Join Date
    Jan 2014
    Posts
    62

    Re: Getting Data from MS Access located in Remote Server

    Quote Originally Posted by techgnome View Post
    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.

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Getting Data from MS Access located in Remote Server

    Quote Originally Posted by TheSilkCode View Post
    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.

  7. #7

    Thread Starter
    Member
    Join Date
    Jan 2014
    Posts
    62

    Re: Getting Data from MS Access located in Remote Server

    Quote Originally Posted by techgnome View Post
    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:
    1. Dim newitem As New ListViewItem()
    2.             newitem.Text = data_reader.GetValue(2) 'first column
    3.             newitem.SubItems.Add(data_reader.GetValue(3)) 'second column
    4.             newitem.SubItems.Add(data_reader.GetValue(5)) 'second column
    5.             newitem.SubItems.Add(data_reader.GetValue(6)) 'second column

    to

    vb.net Code:
    1. Dim newitem As New ListViewItem()
    2.             newitem.Text = data_reader.GetValue(2).tostring() 'first column
    3.             newitem.SubItems.Add(data_reader.GetValue(3).tostring()) 'second column
    4.             newitem.SubItems.Add(data_reader.GetValue(5).tostring()) 'second column
    5.             newitem.SubItems.Add(data_reader.GetValue(6).tostring()) 'second column

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width