Results 1 to 19 of 19

Thread: ADO on Lan

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    2,025

    ADO on Lan

    I need a good Tips to speed the ADO conection over Lan, naturally tips to speed also a recordset operation.

    Note:
    Access database
    or
    Sql Server

  2. #2
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    421

    Re: ADO on Lan

    Reduce the amount of fields you are returning (Avoid "select *" if possible)

    Reduce the number of rows you are returning

    SQL will be faster than access

  3. #3
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,507

    Re: ADO on Lan

    I use the DAO over LANs all the time, and it works quite well.

    And yes, I don't use "select *". In fact, for the most part, I just use the regular DAO stuff (Seek, MoveFirst, MoveNext, FindFirst, FindNext, AddNew, Edit, Update). I also do use transactions (BeginTrans, CommitTrans) on virtually all updates. And, if/when changing a bunch of records, it should help to surround them in a transaction.

    Also, a huge factor will be the speed of all the switches, routers, and ethernet cables in the LAN system. Also, speed of the file servers (raw horsepower and also disk speeds) will make some difference.
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  4. #4
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,547

    Re: ADO on Lan

    Yeah, a file-based DBMS will always be slower across a network than a client-server DBMS. That's just life.

    But even then the same rule applies: "Take only what you need to survive."

    And that means things like server-side cursors when possible, not updating/inserting via a connected Recordset, and smart query design.


    Anyone remember the movie that quote comes from?

  5. #5
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,507

    Re: ADO on Lan

    Quote Originally Posted by dilettante View Post
    Yeah, a file-based DBMS will always be slower across a network than a client-server DBMS. That's just life.

    But even then the same rule applies: "Take only what you need to survive."
    Yes! I don't have any head-to-head comparisons of file-based vs SQL server speeds over a LAN, but I'd have to believe that the SQL server would be faster. Basically, that's what they're designed for from the ground up.

    And yes, I'd certainly believe all the same rules apply ... get as much bandwidth as you can, and don't needlessly waste it.
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  6. #6
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,547

    Re: ADO on Lan

    The main difference is that file-based DMBSs have to read/write the raw DB files across the network, handling file locking, etc. A client-server DBMS can keep all of that on the server... unless some dummy put its files on yet another file server.

  7. #7
    Hyperactive Member
    Join Date
    Mar 2018
    Posts
    421

    Re: ADO on Lan

    Quote Originally Posted by dilettante View Post
    Anyone remember the movie that quote comes from?
    even in the future nothing works

  8. #8
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,010

    Re: ADO on Lan

    Quote Originally Posted by dilettante View Post
    "Take only what you need to survive."

    Anyone remember the movie that quote comes from?
    Quote Originally Posted by DllHell View Post
    even in the future nothing works
    Did I miss something? When did we get to Disneyland?

    -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??? *

  9. #9
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,547

    Re: ADO on Lan

    Quote Originally Posted by luca90 View Post
    I need a good Tips to speed the ADO conection over Lan, naturally tips to speed also a recordset operation.
    Keep in mind that ADO doesn't connect to anything, the Provider does.

    If you are using the shim Provider ("MSDASQL") then it defers the "connecting" to the associated ODBC Driver. Doing that with MDBs drops you back to the old Jet SQL syntax and loses quite a number of features. Always Use the Jet 4.0 Provider, don't shim down to ODBC.


    There are a lot of possible tweaks available in the Jet 4.0 Provider that might apply when you need performance. For example record-level locking.

    And firehose cursors (server-side, forward-only, read-only cursors) can also help performance where they apply. For updating you may want to consider using transactions too.

    We could list tips all day and overwhelm you. There is no substitute for the documentation, good books, and even formal training.

  10. #10
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,547

    Re: ADO on Lan

    Here is a book online that you might find useful: https://flylib.com/books/en/3.9.1/

    It shows a "cover" and text saying it is about ADO.NET, but it seems to really be for ADO.

    I assume it isn't a pirated copy of the book.

  11. #11
    New Member
    Join Date
    May 2020
    Posts
    9

    Re: ADO on Lan

    Quote Originally Posted by Prahlad View Post
    In your Connection string use something like "\\Servername\Directory\Database.mdb" as the Database FileName and Path. Where 'ServerName' is the name of the PC that's hosting the DataBase, 'Directory' is the folder / path where the data is and 'Database' is the Database name.
    This approach has the weakness of Share Full Folder, so someone can still delete the * .mdb file

    Try to write Server + Client to see and access Data via IP LAN .... it's very safe

    I am studying and writing in that direction

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,010

    Re: ADO on Lan

    Quote Originally Posted by dilettante View Post
    We could list tips all day and overwhelm you. There is no substitute for the documentation, good books, and even formal training.
    Not to mention good old fashioned proofs of concept - make sample projects and try them out.

    -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??? *

  13. #13
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,010

    Re: ADO on Lan

    Quote Originally Posted by PhuongNam View Post
    This approach has the weakness of Share Full Folder, so someone can still delete the * .mdb file

    Try to write Server + Client to see and access Data via IP LAN .... it's very safe

    I am studying and writing in that direction
    IP addresses change... server names tend to be more static. Plus also if one machine physically needs to be swapped out, using a named server is the way to go.
    As for deleting the mdb file ... 1) only if they know it's there; 2) if someone deletes it, the world is going to come to a screeching halt - fast; 3) there should be a data backup plan in place, if there isn't, then there needs to be one.

    That said, I'm not a fan of shared file-based databases and would rather see SQL Server in some form or another, or even MySQL or Oracle. But, sometimes you gotta use what you have.


    -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??? *

  14. #14
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,318

    Re: ADO on Lan

    In terms of speeding it up it would help to know what you are doing that is slow. Also you should know that filling a listview from a large recordset is probably the slowest method you could choose.

  15. #15
    New Member
    Join Date
    May 2020
    Posts
    9

    Re: ADO on Lan

    sorry to post the same 2 times
    Last edited by PhuongNam; Sep 20th, 2020 at 05:08 AM.

  16. #16
    New Member
    Join Date
    May 2020
    Posts
    9

    Re: ADO on Lan

    Quote Originally Posted by techgnome View Post
    IP addresses change... server names tend to be more static. Plus also if one machine physically needs to be swapped out, using a named server is the way to go.
    As for deleting the mdb file ... 1) only if they know it's there; 2) if someone deletes it, the world is going to come to a screeching halt - fast; 3) there should be a data backup plan in place, if there isn't, then there needs to be one.

    That said, I'm not a fan of shared file-based databases and would rather see SQL Server in some form or another, or even MySQL or Oracle. But, sometimes you gotta use what you have.


    -tg
    When I have free time, I will research the code and apply it to see how it works very well
    https://www.vbforums.com/showthread....B-SimpleServer
    However, when applying it from the Client sending SQL to the Server, how does the Server send back to the Client side ??? !!! I am confused and do not know how to write

    Example: The following SQL From Client sends to Server

    SELECT [SoLuong], [DonGia], [ThanhTien], [SoPhieu] FROM [DataBaseNhap]


    After the server receives the SQL, how to send the Data back to the Client side ????

    Regarding IP, set up static IP for better data access and connection

    Because I write a small application using Ms Access * .mdb Or * .accdb is enough, so it is not necessary to use Ms SQL Server.

  17. #17
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,010

    Re: ADO on Lan

    Quote Originally Posted by PhuongNam View Post
    When I have free time, I will research the code and apply it to see how it works very well
    https://www.vbforums.com/showthread....B-SimpleServer
    However, when applying it from the Client sending SQL to the Server, how does the Server send back to the Client side ??? !!! I am confused and do not know how to write

    Example: The following SQL From Client sends to Server

    SELECT [SoLuong], [DonGia], [ThanhTien], [SoPhieu] FROM [DataBaseNhap]


    After the server receives the SQL, how to send the Data back to the Client side ????

    Regarding IP, set up static IP for better data access and connection

    Because I write a small application using Ms Access * .mdb Or * .accdb is enough, so it is not necessary to use Ms SQL Server.
    Through the connection... you establish a connection to the server and issue commands through it, the results come back through that connection.
    Same as how a web browser connects to a web server, gets the data, and brings it down to render on the client.

    As for the IP ... I'm not convinced that static IPs result in anything faster or more stable than a named server connection. I will say this though, as far as I know, using an IP directly means you're going to go against a single machine, which means that load balancing is out. (You may not care about that, but someone else may) ... I beleive for load balancing to work you have to hit a named resource.

    -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??? *

  18. #18
    New Member
    Join Date
    May 2020
    Posts
    9

    Re: ADO on Lan

    Quote Originally Posted by techgnome View Post
    Through the connection... you establish a connection to the server and issue commands through it, the results come back through that connection.
    Same as how a web browser connects to a web server, gets the data, and brings it down to render on the client.

    As for the IP ... I'm not convinced that static IPs result in anything faster or more stable than a named server connection. I will say this though, as far as I know, using an IP directly means you're going to go against a single machine, which means that load balancing is out. (You may not care about that, but someone else may) ... I beleive for load balancing to work you have to hit a named resource.

    -tg
    On this vbforums.com I searched for 2 consecutive days to see if there is a sample code Server + Client (VB6) that uses MS Access as Database.accdb to access data via TCPIP, not to refer to writing applications without seeing
    For example, from the Client side, I send to Server 1 SQL below

    SELECT [TenHang], [CK(%)], [DonGia], [GhiChu], [SoPhieu] FROM [DataBaseNhap]

    then after the server receives the connection to Database.accdb to access the data and send back to the client like ??? !!! details like ???

    If you know, please help me
    thank you

  19. #19
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,950

    Re: ADO on Lan

    Quote Originally Posted by PhuongNam View Post
    On this vbforums.com I searched for 2 consecutive days to see if there is a sample code Server + Client (VB6) that uses MS Access as Database.accdb to access data via TCPIP, not to refer to writing applications without seeing
    For example, from the Client side, I send to Server 1 SQL below

    SELECT [TenHang], [CK(%)], [DonGia], [GhiChu], [SoPhieu] FROM [DataBaseNhap]

    then after the server receives the connection to Database.accdb to access the data and send back to the client like ??? !!! details like ???

    What you need in most remote-scenarios is called: "serialization" (to and from ByteArrays).
    And an ideal ContainerObject with support for this is:
    (who'd have thought) ... an ADO-Rs.

    Since this question (remote-access to serverside DBs) is asked quite often recently,
    I've now put an "as simple as possible" Demo-Project into the CodeBank:
    https://www.vbforums.com/showthread....ss-via-http(s)

    HTH

    Olaf

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