Page 1 of 3 123 LastLast
Results 1 to 40 of 120

Thread: Data Service Tier Dll

  1. #1

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Data Service Tier Dll

    I want to write a dll for connecting to a SQL server so I can
    re-use it on my programs. I think its correct to do it this way for
    better performance. Right now I have eveything in the exe. How
    can I get started making it so its generic for any server or db?

    Also, would running an exe from a network share be detrimental
    to performance because of all the network traffic? Better to install
    the exe on every pc locally?

    Thanks for any help.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660
    Are you just wanting the dll to be generic for any SQL Server DB on Any Server ??

    If so then the main thing you would need to do would be to pass in the Connection string as an arguement to the dll.

    By doing this you could have all the logic for Connecting inside the Dll, but would pass in a connection string to it which would mean that it would be re-usable as you would be passing in the IP Address of the Server & Name of the DB each time it was used!

    I take it that you know a fair amount about dll's and the like you seem pretty experienced looking at some of your other posts ??

  3. #3

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Thanks, but I just wonder if its correct logic to do it like this? I want
    to be able to pass the connection string props and return an ADO
    connection object. Also, where would it be located, on the server,
    or ws' for best performance and maintenance?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Ever heard of a Factory? This is a great case to use one.

    In fact you are starting to want one, and do not know it yet. However I would recommend a slight change in your imagination.

    Currently, you want this:

    Code:
    (Client) --asksfor--> Factory.GiveMeAnObject("Databse specific crapola")
    This forces your client to contain unneeded "baggage" - specifically the "way" to get the "type" of database.

    What you should be wanting is your client NOT to know the "way", but simply the type of database.

    Code:
    (Client) --asksfor--> Factory.GiveMeAnMSSQLObject("less database crapola, like username - you cannot escape this crapola")
    (Client) --asksfor--> Factory.GiveMeAnMYSQLObject("less database crapola")
    This is the whole problem with databases - they simply refuse to be completely encasulated inside a dumb (read generic) object.
    There is unfortunately always some bagagge you will have to burden your client with.

  5. #5

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Thats fine Dave. I actually only want to pass the server name, db
    name, userid, and pwd. This will be specifically for SQL servers so
    I dont need to write it to handle anything other than that.

    What about where should it be located, on the server,
    or ws' for best performance and maintenance?

    And - running an exe from a network share be detrimental
    to performance because of all the network traffic? Better to install
    the exe on every pc locally?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  6. #6
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Are you making an in-process DLL, or an out-of-process EXE?

  7. #7

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Whichever is most suitable for the application's enviroment. Its a
    network with a dozen or so users, but if this works well in this
    app I want to use it on another program where the database is
    intense.

    If the apps exe is located on a network share then should I
    change it to local installs? Then depending on that answer would
    tell me which type of ActiveX to create, right?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  8. #8
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    er, I think the situation will be the same in those 2 cases:

    1) The Client app (exe) runs on a network share
    2) The Client app (exe) runs on the local PC disk

    The question regarding network clogging may rest in the diff between 3 things:

    1) In-process DLLs
    2) out-of-process LOCAL COM EXEs
    3) out-of-process REMOTE DCOM EXEs

    I think those 3 will have different affects on the network. What kind of performance metrics are you most concerned about?

  9. #9

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    As you can tell, this is my first attempt at creating an ActiveX process.
    So, if there is no difference between hosting the exe on a
    network share vs. a local exe then I only need to know if it
    should be dll or exe? I want the app to have fast db access and
    GUI response. I dont think I need to use DCOM?

    Thanks.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  10. #10
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    How many client EXEs are we talking about?

    I believe your best approach is an in-process ActiveX DLL. The only thing to watch out for is "how many connections are open at 1 time?"

    This resource management problem could be harmful in the case of an in-process DLL and an out-of-process LOCAL COM EXE.

    But it can be regulated globally with respect to the DB server if you use an out-of-process REMOTE DCOM EXE.

    As far as speed, I thought your object's main job is to return an ADO object? I dont see any performance difference in the LOCAL cases.

    Again, what specific thing do you want to work the fastest? The time it takes to return an object, or the performance of the object once its been instantiated?

    I am not sure if a Connection object will have any difference in performance characteristics once it has been instantiated, regardles of LOCAL or REMOTE location.

  11. #11

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    I guess I want performance of the object once its been instantiated.
    I'm getting a little confused here. I thought that by creating an
    activex object I would be able to increase the performance of my
    app?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  12. #12
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Originally posted by RobDog888
    I guess I want performance of the object once its been instantiated.
    I'm getting a little confused here. I thought that by creating an
    activex object I would be able to increase the performance of my
    app?
    Possibly. However my strongest motivation for involving pre-compiled (binary) components is the reduction in complexity.

    This gives me an increase in performance when designing, testing, debugging, optimizing, and refactoring a SYSTEM.

    Are you having problems with your run-time performance? We can definately examine the system and look for bottlenecks.

    But in my opinion, your course of action will benefit you at design time, perhaps not at run-time. I've been known to be wrong, though.

    If you want a substantial increase in performance, you will be better served moving to C++.

  13. #13

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Its just not possible to port it over to C++. The app has over 50
    forms, 5 modules, and 5 classes or so and it was written in such
    a way using Public ADO Recordsets populated with unprepared
    sql statements that its so slooooow slow. I know using sp's will
    be better for performance, but that would require a complete
    rewrite because of the public rs'. Its been driving me crazy the
    crappy way the other programmers wrote it years ago. I am
    slowly making improvements, but I wanted to refocus my efforts
    in a more beneficial area.

    So to get this app using a dll or two should help if I do it right.

    Thanks for letting me vent my frustrations. I just wish the other
    programmers knew what they were doing back then. Its just
    common sense!!! They have the entire program in the exe! No
    Data service tier. No business Rules tier. Nothing.

    There I go again. Help!!!
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  14. #14
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Originally posted by RobDog888
    Its just common sense!!!
    True Story:

    When I was about 12, my Grandpa (rest his soul) was tuning up an engine. He says: "DaviT (thick German accent) when you remove the head, you must turn each bolt a quarter turn at a time, and slowly back each bolt off in that way. If you remove one bolt all the way, before loosening another bolt, you will warp the head. IT'S JUST COMMON SENSE!"

    Those words ring in my ears to this day; I had almost no clue what he was talking about at the time, I was just a kid!

    Anyway, what is common sense to you comes from thousands of hours of experience, and is always a strange concept to the nOOb.

    I inherited a 10-year old VB 3 project that evolved to VB4 then VB5 and finally VB6. You should see me pull my hair out looking at the crap-f-ing-ola these nOObs put in there. No concept of standard OO practices whatsoever. One "programmer" was a secretary!!!! AAAAAHHHHHHAAAAAAAHHHHHAAAAAAAAH!!!!!!!!!

    You are not alone!

    Anyway, concerning your current "project", I would stay down the path you are on - not for the benefit of run-time performance. That will come later. But instead, if you can gut the crap-ola out of the source, things always become easier to refactor, and thus, optimize.

  15. #15
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660
    Condsidering it is an old app, is it using Disconnected Recordsets ?, this often speeds things up !

  16. #16
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660
    I suppose the point i was trying to make, with my last post was that, just shoving something into a dll or activeX object won't necessarily improve preformance, although it can if it is done right

    Before you do that i would look at all other ways of improving the way the App interacts with it's database, and try to rationalize the dodgy code that you have been lumbered with when you take on old projects that other people have written.

    Cant you stop it using Public Recordset's for a start!, or are there alot of them ??

  17. #17

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    There are about a dozen public rs' and they are used throughout
    the 50 forms in the program. I want to change that too. They are
    not disconnected either, but when ever they load a form from a rs
    they reopen the rs with another call. Dont make much sense to
    have it public if you are reopening the rs everytime? Might as well
    use private rs' then. Same difference.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  18. #18
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Agreed.

    Also wanted to point out that you want to keep you connections to a minimum.

    That means ideally you will only have 1 ADODB.Connection object instantiated and open at a time.

    However, I beleive having multiple ADODB.Recordset objects open is OK and should not be too resource intensive per se.

    That said, of course everything should be kept to a minimum where possible.

  19. #19

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    See, I want to use the data service dll to pass sp's and parameters
    to eliminate the unprepared public rs'. I have only one open
    persistant connection, but in order to see the add, deletes, and
    edits in the rs' the rs needs to be opened with adOpenDynamic. Then there need to be a way to know if there is a new record
    added or deleted so I can update the data on the form?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  20. #20
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Originally posted by RobDog888
    Then there need to be a way to know if there is a new record added or deleted so I can update the data on the form?
    Can an ADODB.Recordset raise an event when a record is added?

  21. #21
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    I am thinking no, but you could make your own mechanism into your data-tier, however that will be quite expensive at run-time...

  22. #22
    Lively Member
    Join Date
    Sep 2004
    Posts
    74
    Only go for an DLL when youre app will have 50+ users, otherwise the performance gain wil not outweigh the extra effort you will have to put in.

  23. #23
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Originally posted by RobDog888
    Then there need to be a way to know if there is a new record added or deleted so I can update the data on the form?
    This would require creating new database objects, namely Triggers. I dunno if there is a COM equivalent, I doubt it.

    http://msdn.microsoft.com/library/de..._ob_t_3nsk.asp

    Exerpt:
    SQL Server supports using triggers as a kind of stored procedure. Triggers are executed when a specified data modification, such as an attempt to delete a row, is attempted on the table on which the trigger is defined.

  24. #24

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Ah!, I never thought about using a trigger to tell the app that a
    record was either added, updated, or deleted. So would quering
    the db to get a temp table listing of current adds, updates, and
    deletes be the wrong way to do it?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  25. #25
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Unfortunately, I can't see any other way. I do not believe a Trigger can notify an external COM entity of an event. I have been known to be wrong though.

    It seems to me you are going to have to "poll" some new table you make just for the purpose of being notified of events occuring in the database.

  26. #26
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961

    Post

    Man, if this is really your best route then I feel like we are in the friggen stone age of computing

  27. #27

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Wait, since I will be opening the rs' with the adOpenDynamic
    the rs will always have the updated record contents? The issue is
    how to know when to reload the combos textboxes and other
    controls to include the updated data?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  28. #28
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Originally posted by RobDog888
    [color=darkblue]Wait, since I will be opening the rs' with the adOpenDynamic
    the rs will always have the updated record contents?color]
    Is this true? I wasn't able to find documentation to support this claim. Do you have some that does? That would be pretty cool.

  29. #29

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Dave, in the documentation for adOpenDynamic from MSDN:

    adOpenDynamic:
    Reflects any new additions, changes, and deletions made by
    other users, and allows all types of movement through the
    Recordset object that don't rely on bookmarks; allows bookmarks
    if the provider supports them. This cursor type isn't supported by
    the Microsoft Jet 4.0 OLE DB Provider.

    adOpenKeyset:
    Behaves like a Dynamic cursor, except that it doesn't contain any
    new or deleted records added by other users. Any data changes
    made by other users to the records available when the Recordset
    object was opened will still be visible. A Keyset cursor always
    supports bookmarks and therefore allows all types of movement
    through the Recordset object.

    But I never actually tested the difference if you open a rs with
    adOpenDynamic and you load some cbos. Then another user
    adds a new record, the rs should reflect the addition, but how do
    I know that I need to reload the cbo to reflect the addition?

    Thanks.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  30. #30
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Right. How do you know? More specifically WHEN do you know that a change has occurred? The ADODB.Recordset object does not advertise Events AFAIK.

    This is where a Trigger must be used; to find out WHEN something happens. It looks from the docs you provided that the Dynamic Recordset will reflect outside changes WHEN POLLED, but not alert you to changes at the time of the event.

    A way around this is to tie a Trigger to your table in question, and force some data into another table. Then, you could make a kind of Custom Class to detect changes in the second table and alert you via a true VB Event.

    I personally hope you find a better way than this - I am interested in this problem.

  31. #31

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    First: what does "AFAIK" stand for. I have seen it before can not
    figure it out.

    Second: If I use a trigger on the table in question, how do I get
    the trigger to relate to a vb event? Oh wait there is the
    RaiseEvent in SQL. In the triggers (ADD, UPDATE, and DELETE) I
    can raise a custom event telling my app that there was a change
    to a recordset. But then what if it comes from another instance of
    the app?

    Third: Oakland Raiders are going to win this years superbowl!
    They are going to spread that Green Bay cheese over a cracker
    and eat it to celebrate!

    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  32. #32
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Originally posted by RobDog888
    First: what does "AFAIK" stand for. I have seen it before can not
    figure it out.

    Second: If I use a trigger on the table in question, how do I get
    the trigger to relate to a vb event? Oh wait there is the
    RaiseEvent in SQL. In the triggers (ADD, UPDATE, and DELETE) I
    can raise a custom event telling my app that there was a change
    to a recordset. But then what if it comes from another instance of
    the app?

    Third: Oakland Raiders are going to win this years superbowl!
    They are going to spread that Green Bay cheese over a cracker
    and eat it to celebrate!

    1) As Far As I Know

    2) If SQL calls its RaiseEvent, how can that get communicated to your Client app? I'm not saying it can't I'm just saying I have no clue how that would work. I am not sure you can translate a SQL event (Trigger) into a VB event. Maybe this should be the subject of a new thread in the Database Section.

    3) LOL Packers RULE!

  33. #33

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Thanks. I agree that maybe this new subject should be a new
    thread in the databases forum.


    Direct quote from nfl.com front page story
    Two costly interceptions hurt Brett Favre and the Packers last
    week in a surprising loss to the Bears, a team Green Bay had
    dominated in recent years.
    They got beat by the BEARS! The Bears are one of the worst
    teams in the NFL and your Packers lost to them IN Green Bay.





    Just giving you a hard time!
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  34. #34
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Originally posted by RobDog888
    the BEARS! The Bears are one of the worst
    teams in the NFL and your Packers lost to them IN Green Bay.


    Hey, we were getting dead sick of always beating them... They lost to us the last 7 games in a row!

  35. #35
    Lively Member
    Join Date
    Sep 2004
    Posts
    74
    Originally posted by RobDog888
    Second: If I use a trigger on the table in question, how do I get
    the trigger to relate to a vb event? Oh wait there is the
    RaiseEvent in SQL. In the triggers (ADD, UPDATE, and DELETE) I
    can raise a custom event telling my app that there was a change
    to a recordset. But then what if it comes from another instance of
    the app?
    Actually triggers aren't meant for that, but it's is possible, cuz it's possible to 'enhance' SQL server with new commands (thru custom DLL's).
    But this is an big security risk and not really advised to do.

    But why would you want realtime updates of changing data?

    And finally, if you use such an kind of warning and the clients will an mass start updating the data when an event , youre SQL server and Network aren't gonna like that.

    Triggers are meant to keep youre database integrety good, not for noticing clients.

  36. #36
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    Originally posted by Calibra

    But why would you want realtime updates of changing data?
    He said it was because, when any user updates a certain table, he wants that change to reflect in a ComboBox on a Form.

    As from Calibra's comments describe, you will not be successful doing this. Perhaps you could re-evaluate the behaviour of your entire system and refactor that aspect out of it?

    I would spend 20 hours refactoring before I'd spend 10 hours going down the "wrong" path, Robdog.

  37. #37
    Lively Member
    Join Date
    Sep 2004
    Posts
    74
    Originally posted by Dave Sell
    He said it was because, when any user updates a certain table, he wants that change to reflect in a ComboBox on a Form.
    Then you could use an other trick, how about an Com component on the SQL server that monitors the table and fires an event to the clients when the data changes.

    This could be realised by using the event/source sink technology

  38. #38

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Could you elaborate on this?

    Also, Its not really a real-time program but rather I just want to
    know when populating the form (not Form_Load) if I need to
    requery the recordset to obtain the latest data.

    Thanks.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  39. #39
    Lively Member
    Join Date
    Sep 2004
    Posts
    74
    They are called connection points and it allows COM objects to fire events to the client or whatever you want.
    Coulcn't remember the name (SHAME), had to get my MOC book on COM development out of the bookcase (shame deeply )

    read these :
    COM tech overview

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncomser/html/complus4vb.asp

    More on connection points

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnguion/html/drgui082399.asp


    don't know if it's possible in VB (never had to use it ther, actually i have never written an COM in VB, just in C++ :-( )

  40. #40

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Thanks for the links. I will read up on them. Don't worry I havent
    written any COM either. How about when to know when
    populating a form to know if I should requery the db to refresh
    the rs for the latest recordset info?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

Page 1 of 3 123 LastLast

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