none
Pass-through of datasets to sql server from Access RRS feed

  • Question

  • I have an order entry/processing/quoting Access project. Some orders have hundreds of line items. The backend is a SQL Azure Db. When working this this data, it is pulled into a local table within Access. After client is done processing and manipulating the data, I have to update the Azure db. When lines are updated, inserted, or removed them using Access workspace this is SLOW. Too slow. Very basic Update statements take 60 seconds on some tables. It is not connection speed. I'm able to do the same things in C# in seconds using ADO.NET and SQL Server data providers.

    Is there any way I can pass to the sql server a dataset or datatype in some sort of passthrough? I can take it from there to preform insert, update, and delete statements on the sql server instead of the client. But I'm not seeing how to send anything other than simple datatypes as paramaters to stored procs from Access.
    Friday, May 19, 2017 5:18 PM

Answers

  • Well, if in c# only takes seconds, then simply call that c# code from Access. Given that you stated you already written and tested the update in c#, then why not continue using that same code?

    You can shell out to that existing c# code. Or you can turn such code into a com object.

    If I have this class module in vb.net:

    Public Class Class1

        Public Function MyHello()

            MsgBox("hello")

        End Function

    End Class

    Just check the box “make com assembly visible” (assembly dialog box in Visual Studio)

    And on main project, then just check Register for com interop.

    While there are TONS of articles on the internet that go on for 10+ pages about building interfaces etc. The above is all you need. Compile the project to x86. You do NOT have to build a COM interface - VS will do it for you.

    Now, to run the above .net code from Access VBA?

    In a test stub module in Access VBA?

    , this works:

    Sub TestComObject()
      
       Dim o    As Object
      
       Set o = CreateObject("ComNoReg.class1")
      
       o.MyHello


    End Sub

    That is it!!! I just typed both snips in as I wrote this post - it works just fine.

    So if you have working code in c#, then as above shows, you don’t even have to re-write the code. Just paste the whole mess into a class object and call it from Access.

    You can certainly call + use a pass-through query from Access, but you are limited to the command line interface that you have with say the SQL command line (from SSMS for example).

    If you have working code that passes an array in c#, then as above code snip in vb.net shows, you do NOT have to build a special "complex" COM interface in .net. You just tell Visual Studio to Expose the class. And once done  you can consume such .net code from Access - and with very little work

    I suppose you could have some table server side that you empty, and then append the local data up to the server and then call some store proc to process the rows. However such a setup tends to be less than ideal for multi-users of such applications.

    While your internet connection may seem ok, it still typically about 100 times slower then say your local office LAN.

    You can also execute any t-sql from Access VBA with this code:

       With CurrentDb.QueryDefs("qryPassR")
          .SQL = "select * from tblHotels" - or ANY T-SQL command
          .ReturnsRecords = False     ' set this for exec T-SQL commands that don't return data
          .Execute
       End With  

    The above code assumes that you setup a pass-through query in Access called “qryPassR” in Access. So it becomes a "catch all" that you can "stuff" the T-SQL as above and then .execute it. So I use the above code stub over and over in (and the same single PT query) over and over anytime in VBA I want to write + run T-SQL from VBA code.

    So you can execute any t-sql command quite easy from Access. But passing a “complex” data structure as a parameter to t-sql is not so easy in VBA.

    Regards
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada




    • Edited by Albert D. Kallal Friday, May 19, 2017 11:22 PM
    • Marked as answer by HTHP Saturday, June 10, 2017 12:46 PM
    Friday, May 19, 2017 11:16 PM

  • I've searched but can't find a straight forward answer on how to register the COM Interop dll I've created on these machines. Do I need .NET installed on all those machines? How do I do that without installing Visual Studio? I also keep seeing something about "C:\Program Files (x86)\Microsoft.NET\Framework\regasm.exe", but my computer doesn't even have this folder. I'm able to run regasm on my computer via the Developer Command Prompt for VS2015 on my machine, but I don't have VS installed on all these other computers.

    Is the Regasm.exe tool one size fits all? Do I need anything else on those machines? I'm obviously able to register it on my machine.
    

    Yes, Regasm is the correct tool. Depending upon the version of Visual Studio you are working with you can also create either an Installer or Setup project for the .NET DLL as well. If you use Regasm then specify the Codebase option if the .NET DLL is not registered with the GAC (Global Assembly Cache).

    A .NET DLL would still require that a supported version of the .NET Framework is installed, and Regasm would be in the latest version of that .NET Framework folder (C:\Windows\Microsoft.NET\Framework).


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by HTHP Saturday, June 10, 2017 12:46 PM
    Saturday, May 20, 2017 2:45 PM

All replies

  • It would probably help to see the code you are using to sync the changes. BTW, why don't you just create a linked table in the Access database project to the SQL Server table instead of importing to a local table?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, May 19, 2017 6:51 PM
  •  I store the data in a local table for many reasons. It is faster to manipulate data while it is in a local table than a linked table. The changes may not be committed so I don't want the program working in the live tables. I have better control over validating the entire dataset as a whole based on the unique business logic. I can avoid unnessesary code because I don't have to worry about extra effort of avoiding effecting data in the table that the user is not intending to manipulate. I wish it was easier to manipulate datasets in memory in Access, but it seems much more dependable and easier to work with data in a local table than a disconnected ADO.Recordset object.

    If I by using a linked table, you mean insert the data into a linked table when the user finishes manipulating it(like a proxy of sorts) then tell the server to process it, that has complications too. Mainly there are numerous users in this application and these features of it. So they would all be using the same linked table. Plus, it still takes Access a long time to insert this data into a linked table.

    It seems like everything is processing on the UI thread, and it takes forever. I need a way to offload the insert/update/delete commands to the SQL server.
    • Edited by HTHP Friday, May 19, 2017 9:39 PM
    Friday, May 19, 2017 9:38 PM
  • Well, if in c# only takes seconds, then simply call that c# code from Access. Given that you stated you already written and tested the update in c#, then why not continue using that same code?

    You can shell out to that existing c# code. Or you can turn such code into a com object.

    If I have this class module in vb.net:

    Public Class Class1

        Public Function MyHello()

            MsgBox("hello")

        End Function

    End Class

    Just check the box “make com assembly visible” (assembly dialog box in Visual Studio)

    And on main project, then just check Register for com interop.

    While there are TONS of articles on the internet that go on for 10+ pages about building interfaces etc. The above is all you need. Compile the project to x86. You do NOT have to build a COM interface - VS will do it for you.

    Now, to run the above .net code from Access VBA?

    In a test stub module in Access VBA?

    , this works:

    Sub TestComObject()
      
       Dim o    As Object
      
       Set o = CreateObject("ComNoReg.class1")
      
       o.MyHello


    End Sub

    That is it!!! I just typed both snips in as I wrote this post - it works just fine.

    So if you have working code in c#, then as above shows, you don’t even have to re-write the code. Just paste the whole mess into a class object and call it from Access.

    You can certainly call + use a pass-through query from Access, but you are limited to the command line interface that you have with say the SQL command line (from SSMS for example).

    If you have working code that passes an array in c#, then as above code snip in vb.net shows, you do NOT have to build a special "complex" COM interface in .net. You just tell Visual Studio to Expose the class. And once done  you can consume such .net code from Access - and with very little work

    I suppose you could have some table server side that you empty, and then append the local data up to the server and then call some store proc to process the rows. However such a setup tends to be less than ideal for multi-users of such applications.

    While your internet connection may seem ok, it still typically about 100 times slower then say your local office LAN.

    You can also execute any t-sql from Access VBA with this code:

       With CurrentDb.QueryDefs("qryPassR")
          .SQL = "select * from tblHotels" - or ANY T-SQL command
          .ReturnsRecords = False     ' set this for exec T-SQL commands that don't return data
          .Execute
       End With  

    The above code assumes that you setup a pass-through query in Access called “qryPassR” in Access. So it becomes a "catch all" that you can "stuff" the T-SQL as above and then .execute it. So I use the above code stub over and over in (and the same single PT query) over and over anytime in VBA I want to write + run T-SQL from VBA code.

    So you can execute any t-sql command quite easy from Access. But passing a “complex” data structure as a parameter to t-sql is not so easy in VBA.

    Regards
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada




    • Edited by Albert D. Kallal Friday, May 19, 2017 11:22 PM
    • Marked as answer by HTHP Saturday, June 10, 2017 12:46 PM
    Friday, May 19, 2017 11:16 PM
  • Well first, what you seem to be doing is a "batch update", so I'm not sure what constitutes "too slow". Second, can't you use a connected ADO Recordset to the SQL Server database, possibly using a server-side cursor? If you are using UpdateBatch on a Recordset then that will not be very fast for large data sets.

    AFAIK, there is no support for passing Classic ADO Recordset objects to a SQL Server stored procedure. Perhaps you may want to stick with a .NET solution since it sounds like you are able to do this more efficiently using those data access libraries.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, May 19, 2017 11:24 PM
  • Albert D. Kallal, I followed your instructions for COM Interop and it works fantastic. The performance was like night and day. However, I've only written a few dlls before and am finding out registering COM Interop dlls is obviously a different beast. I'm unable to register it with regsvr32.exe. I have to deploy this Access project on multiple 2008 and 2016 servers, windows 7, windows 8, and windows 10 machines. This may be boardering outside the scope of an Access thread, but I'd like to think not because it seems like COM Interop and VBA are intimately linked topics.

    I've searched but can't find a straight forward answer on how to register the COM Interop dll I've created on these machines. Do I need .NET installed on all those machines? How do I do that without installing Visual Studio? I also keep seeing something about "C:\Program Files (x86)\Microsoft.NET\Framework\regasm.exe", but my computer doesn't even have this folder. I'm able to run regasm on my computer via the Developer Command Prompt for VS2015 on my machine, but I don't have VS installed on all these other computers.

    Is the Regasm.exe tool one size fits all? Do I need anything else on those machines? I'm obviously able to register it on my machine.
    

    • Edited by HTHP Saturday, May 20, 2017 2:03 PM
    Saturday, May 20, 2017 2:02 PM

  • I've searched but can't find a straight forward answer on how to register the COM Interop dll I've created on these machines. Do I need .NET installed on all those machines? How do I do that without installing Visual Studio? I also keep seeing something about "C:\Program Files (x86)\Microsoft.NET\Framework\regasm.exe", but my computer doesn't even have this folder. I'm able to run regasm on my computer via the Developer Command Prompt for VS2015 on my machine, but I don't have VS installed on all these other computers.

    Is the Regasm.exe tool one size fits all? Do I need anything else on those machines? I'm obviously able to register it on my machine.
    

    Yes, Regasm is the correct tool. Depending upon the version of Visual Studio you are working with you can also create either an Installer or Setup project for the .NET DLL as well. If you use Regasm then specify the Codebase option if the .NET DLL is not registered with the GAC (Global Assembly Cache).

    A .NET DLL would still require that a supported version of the .NET Framework is installed, and Regasm would be in the latest version of that .NET Framework folder (C:\Windows\Microsoft.NET\Framework).


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by HTHP Saturday, June 10, 2017 12:46 PM
    Saturday, May 20, 2017 2:45 PM
  • Well it appears I'm able to use Regasm to register on a 2008 server because I no longer get the unable to initialize ActiveX component error. But I now get a different error: "-2147024894: Automation error. The system cannot find the file specified."

    This dll does nothing more than run the code to display the message box in the Albert's example. Any ideas on that?
    • Edited by HTHP Saturday, May 20, 2017 3:17 PM
    Saturday, May 20, 2017 3:17 PM
  • Well it appears I'm able to use Regasm to register on a 2008 server because I no longer get the unable to initialize ActiveX component error. But I now get a different error: "-2147024894: Automation error. The system cannot find the file specified."

    This dll does nothing more than run the code to display the message box in the Albert's example. Any ideas on that?

    Did you use the Codebase option when registering? It will specify the path to the .NET DLL.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, May 20, 2017 4:47 PM
  • As noted per my example in VS you check the “register for COM interop”. This simply runs a regasm after you compile the project (and it does so with /codebase /tlb). So this option does nothing to the project except register it after you compile.

    The option under the assembly tab called “Make assembly COM visible” is what actually allows the COM objet to be registered and consumed as a “ActiveX”/COM. So in theory just compiling on your dev machine you don’t need the register for COM interop except of course to run + test the code on that dev machine.

    As for registering on the target machine? You have a lot of options to deal with this. I am sure right now you have some batch file or some means to deploy the Access front end. Perhaps a batch file or even some install script. After all (especially when using the free Access runtime) you likely need a target folder/location for the application, a shortcut icon on the desktop  (and set folder as a trusted location to eliminate several Access security prompts). Access full edition often makes this entry in the registry for you – but it is a simple registry setting. However this is not your issue at this point in time – but “some” deployment strategy will have to be given considering here, be it .net, Access or anything you plan to deploy. Most Access folks cobble together some kind of "update" system - often just a simple check of a version number in the front end and some "version" number in a table server side.

    It is also possible to JUST place the “.dll” in say perhaps the same folder at the front end and have Access “side” load + use the .dll without having regasm the .dll (trying to keep this post short – but will expand on this perhaps another time).

    So your batch file or installer will need to execute a regasm. You likey either targeting .net 2 or likey .net 4.

    Note that you need /codebase /tlb if memory serves me correct.

    So this in a bat file:

    rem // Registers Our NAV Pre processor object

    rem // location of regasm.exe
    set NETDIR=%windir%\Microsoft.NET\Framework\v2.0.50727

    rem // get current directory
    set MYDIR=%~dp0

    rem // register the library and output the .tlb file
    %NETDIR%\regasm.exe "%MYDIR%\ERSNavProcessObject.dll" /CODEBASE /tlb

    rem pause


    And for 3.5 to 4.52, then you use

    C:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm

    error: "-2147024894

    That “error” is usually in regards to file/path or permissions or that a dependency is missing. So ensure that you run regasm with elevated rights (right click on cmd.exe and “run as administrator”). Also ALWAYS include the /codebase /tlb option when you register the .dll. As noted since this is a “COM” object, then location of the .dll not that important – but I usually place it in the same folder as the Access front end.

    So deployment can be a simple batch file, windows script or perhaps what you are using now to deploy updates. I mean, you likely had to deal with issuing updates, and something better then running around with a jump drive to update each workstation likely been considered here.

    So from the command line as an admin, I would try a regasm again with elevated rights.

    > Do I need .NET installed on all those machines?

    Yes, just like any .net program, you need the .net runtimes.

    Everything these days has .net 2 installed, and the vast majority of machines also have .net 4 these days since so much other software requires the .net runtime.

    Server 2008 is quite old – so it may well not have the .net 4 runtime that you targeting in your VS project. (check the path names above on that box - you see if .net 4 is installed.

    I would target .net 2.0, or say 3.5. However in some cases I do have to target 4.5.2 due to 3d party libraries I am using.

    edit: also note you need to force the VS project to x86 - but other then that, the simple code above should run and work.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada


    Saturday, May 20, 2017 6:15 PM
  • The messagebox test works now with COM Interop after registering with /codebase /tlb. I used to use batch files to push updates, but I've written a C# program to copy all files to the machines from my end and on all the machines I have another C# program that copies a new version of the FE each time a user executes it. Into a special directory. Within this directory I also have many resource folders and files etc. I'll place my COM objects somewhere in there and copy them over with everything else during updates.

    I can't really fire a batch file for registering the COM dlls during updates from my deployment machine. But I can store a batch file on each machine and leave a file indicating last time the batch was ran, and when updated dlls are pushed to those machines. Then I will have the program that opens the FE for the user run the batch file if it the COM dll was updated. That way it only runs once, and only when the first user opens the FE. Of course, I may run into issues with elevated privledges. But thats different topic.

    I will post back when I've implemented the working solution. Its time to retire for today. Thanks for the help to you both!
    Saturday, May 20, 2017 7:01 PM
  • Hello,

    If you issue has been resolved, we would appreciate if you could share the solution here. And I would suggest you mark helpful post or your solution as answer to close this thread.

    Regards,

    Celeste


    MSDN 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.

    Wednesday, June 7, 2017 8:39 AM
    Moderator
  • I have not had time to fully implement a solution to this problem because other projects have taken priority. The performance issue is an edge case senario which the company has decided it can deal with while it pursues other issues. Although I think the COM Interop will work, I have not had time to test pulling the data from the accde file so the C# project can do the processing via COM Interop commands. I hope I can return to this in the near future. What I have learned is invaluble, so thanks for the assistance! I'm sure it will come to great use in the future.
    Saturday, June 10, 2017 12:58 PM