locked
Excel --> MVC RRS feed

  • Question

  • User245903486 posted

    Hello,

    I'm trying to create new website MVC with Owin/Identity and control all records from Excel. But I can't find any useful information how to do it. Maybe someone can guide me a bit? or show any links with similar solutions.

    I have Excel file with users/password/my custom fields/ and etc, and I have my old website which is connected to MS Access Database .mdb which is almost mirror to my Excel file Then I need to update something I just open my Excel file and launch my VBA code and it updates all records on MS Access file, so I don't need any manual things to update records.

    I want to have my original Database on Excel and update it from Excel to MVC database. Like add new users, update passwords, edit my custom fields. I already did Excel -> Access(.mdb) -> MVC Database, but it requires to open website and manually Click "update". I would love to get ride from that 3-rd move.

    any help appreciate, sry for my english.

    sincerely

    Monday, May 21, 2018 1:00 PM

Answers

  • User245903486 posted

    Hi all, thanks for your answers. Dunno why but my last post is missing, so trying to add new one, and hope it's not duplicate. Seems I found solution how to control my ASP.NET database with Excel VBA.  I'm pretty new on MVC just started 1 month ago. I have already added Identity/Owin to the project and transferred all users from Old database(Excel/MS Access .mdb) to this new system, I tried to read/update fields and seems its working, so Excel is still very powerful tool so I will stick with it :).

    If anyone will need sample I'm posting it, but before you launch make sure you add Reference on Excel: Tools --> Reference --> Microsoft ActiveX Data Objects 6.1 Library

    Sub aaaaaaa()
        Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
    
        Set conn = New ADODB.Connection
        With conn
            .connectionString = "Driver={SQL Server native Client 11.0};Server=(LocalDB)\MSSQLLocalDB;AttachDBFileName=LocationDatabase.mdf;Trusted_Connection=Yes"
            .CursorLocation = adUseClient
            .CommandTimeout = 0
            .Open
            If .State <> 1 Then
                MsgBox "Nooo Connection!!!", vbExclamation
                Exit Sub
            End If
        End With
        
        Set rs = conn.Execute("Update [Table] SET UserID = 4 WHERE ID='1'")
        Set rs = conn.Execute("Select * From [Table]")
    
        MsgBox rs("UserID")
        
        rs.Close
        conn.Close
    
        Set rs = Nothing
        Set conn = Nothing
    End Sub

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 23, 2018 7:24 AM

All replies

  • User1120430333 posted

    There is no such thing as a MVC database. MVC is a UI design pattern.

    https://www.codeproject.com/Articles/228214/Understanding-Basics-of-UI-Design-Pattern-MVC-MVP

    https://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs

    Access database is not a multi user database suited for Web based solutions where multiple users can use the database concurrently.

    Monday, May 21, 2018 5:40 PM
  • User1724605321 posted

    Hi Estimate ,

    If the problem lies on how to read data from Excel and do CRUD database operations , you can use Open XML SDK  to fetch data from excel and use ORM or ADO.Net to do update the database . You can use create user roles in mvc so that only specific user groups could access the pages which imports data from excel to database .  If i misunderstand your requirement , please feel free to let me know .

    Best Regards,

    Nan Yu

    Tuesday, May 22, 2018 9:21 AM
  • User-821857111 posted

    There is no reason why you can't use OleDb to connect to your mdb file and use that at a data store. My recommendation is that you import the content of the Excel file into Access and then ditch the Excel file. Excel is not a sustainable way to manage data for a web site. 

    Either way, if you want to use Identity you will have to write your own custom storage provider. There are none that I know of for Access or Excel.

    https://docs.microsoft.com/en-us/aspnet/identity/overview/extensibility/implementing-a-custom-mysql-aspnet-identity-storage-provider

    Tuesday, May 22, 2018 10:48 AM
  • User-821857111 posted

    Access database is not a multi user database suited for Web based solutions where multiple users can use the database concurrently.
    Actually, for small to medium web sites, it's not that bad. It supports up to 255 concurrent users (connections) and is a lot more resilient when used as a Jet database rather than as part of an Access application. I used to have a site with 10k page views a day happily running off one. Having said that, I wouldn't recommend it if the developer has other options available to them.

    Tuesday, May 22, 2018 10:55 AM
  • User245903486 posted

    Hi all, thanks for your answers. Dunno why but my last post is missing, so trying to add new one, and hope it's not duplicate. Seems I found solution how to control my ASP.NET database with Excel VBA.  I'm pretty new on MVC just started 1 month ago. I have already added Identity/Owin to the project and transferred all users from Old database(Excel/MS Access .mdb) to this new system, I tried to read/update fields and seems its working, so Excel is still very powerful tool so I will stick with it :).

    If anyone will need sample I'm posting it, but before you launch make sure you add Reference on Excel: Tools --> Reference --> Microsoft ActiveX Data Objects 6.1 Library

    Sub aaaaaaa()
        Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
    
        Set conn = New ADODB.Connection
        With conn
            .connectionString = "Driver={SQL Server native Client 11.0};Server=(LocalDB)\MSSQLLocalDB;AttachDBFileName=LocationDatabase.mdf;Trusted_Connection=Yes"
            .CursorLocation = adUseClient
            .CommandTimeout = 0
            .Open
            If .State <> 1 Then
                MsgBox "Nooo Connection!!!", vbExclamation
                Exit Sub
            End If
        End With
        
        Set rs = conn.Execute("Update [Table] SET UserID = 4 WHERE ID='1'")
        Set rs = conn.Execute("Select * From [Table]")
    
        MsgBox rs("UserID")
        
        rs.Close
        conn.Close
    
        Set rs = Nothing
        Set conn = Nothing
    End Sub

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 23, 2018 7:24 AM
  • User1120430333 posted

    DA924

    Access database is not a multi user database suited for Web based solutions where multiple users can use the database concurrently.

    Actually, for small to medium web sites, it's not that bad. It supports up to 255 concurrent users (connections) and is a lot more resilient when used as a Jet database rather than as part of an Access application. I used to have a site with 10k page views a day happily running off one. Having said that, I wouldn't recommend it if the developer has other options available to them.

    I got burnt by Access  many years ago on a mufti user client/server desktop solution  using VB6 as the database became corrupted on a routine basis needing repair, which was corrected when  I moved the data to MS SQL Server and never looked back. Myself, I wouldn't touch Access with a 10 foot pool using it in any multi user environment.  

    Wednesday, May 23, 2018 9:30 AM
  • User-821857111 posted

    I got burnt by Access  many years ago on a mufti user client/server desktop solution  using VB6
    Yes - it isn't designed for that kind of usage. It becomes flaky if you have multiple open connections and are doing a lot of writing to the db. If the usage is largely read-only (like for a busy blog) it stands up pretty well.  I wouldn't use it for any business-critical LOB applications.

    Thursday, May 24, 2018 7:04 AM