Microsoft Access Read/Write on Plesk Windows Server C# ASP.NET

Microsoft Access database crud operations are mostly done by the ACE driver which works offline smoothly but when you run your application online especially on the windows server through Plesk. The ACE driver is not supported by the mostly windows server and MS Access is also not recommended for online applications. But in some circumstances, there is a need to run it online mostly, in the case of running an offline application online to enhance customer services. This post will help you that how can you run the MS Access application on a windows server using the Plesk server interface.  I will show you to connect your application using ODBC server and perform read/write operations in MS Access in the Windows Server Plesk server interface. 

ODBC

Open Database Connectivity (ODBC) is an open standard Application Programming Interface (API) for accessing a database. In 1992, Microsoft partnered with Simba to build the world’s first ODBC driver; SIMBA.DLL and standards-based data access was born. By using ODBC statements in a program, you can access files in a number of different common databases. In addition to the ODBC software, a separate module or driver is needed for each database to be accessed.

Step 1: 

Login to your plesk panel > goto Website & Domain > Click on ODBC Data Sources.

Step 2 :  

Click Add ODBC DSN. DSN is a connection object saves the database information.

Step 3 :  

Add Connection Object Name then connection description then select your database.

Step 4: 

 Enter your database path and leave all textbox . Click save button . your ODBC server is ready to use. You can test it by clicking the test button before saving to check whether it is properly connected to your MDB file or not. 

Step 5 : 

Now just use your DSN name as connection string . 

public string getConstrDSNOrderDSN()
        {

        
            return "Dsn=Your-DSN-Name" 

           

        }

Step 6

Database CRUD operation will be the same as in another database. You will just have to use ODBC connection or command provider 

using System.Data.Odbc;

 public List<OrderMast> GetPendingOrderModel(string sql)
        {
            List<OrderMast> Record = new List<OrderMast>();
            try
            {


                using (OdbcConnection dbcon = new OdbcConnection(db.getConstrDSNOrderDSN()))
                {


                    using (OdbcCommand cmd = new OdbcCommand(sql, dbcon))
                    {

                        if (dbcon.State == ConnectionState.Closed)
                            dbcon.Open();
                        using (OdbcDataReader drOb = cmd.ExecuteReader())
                        {
                            if (drOb.HasRows)
                            {

                                while (drOb.Read())
                                {
                                    OrderMast data = new OrderMast();

                                    data.Acc_head = drOb.IsDBNull(0) ? " " : Convert.ToString(drOb[0]);
                                    data.vDate = drOb.IsDBNull(1) ? " " : Convert.ToString(drOb[1]);
                                    data.LRNo = drOb.IsDBNull(2) ? " " : Convert.ToString(drOb[2]);
                                    data.RNo = drOb.IsDBNull(3) ? " " : Convert.ToString(drOb[3]);
                                    data.TotQty = drOb.IsDBNull(4) ? " " : Convert.ToString(drOb[4]);
                                    data.TotAmt = drOb.IsDBNull(5) ? " " : Convert.ToString(drOb[5]);
                                    Record.Add(data);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return Record;
        }

In the above function, SQL is coming from the calling function, hold in SQL variable. 

 Next Topic