Excel --> MVC

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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.  

Answer 7

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.