CODEWITHSUNDEEP
c#databasems-access
Abdi L
Abdi L

Reputation: 23

How to connect Microsoft access database to visual studio C#

I am using visual studio Windows forms for a login/sign-up project. So how would I go about connecting my Microsoft access data base to my visual studio project and establishing a connection in the code so I can write out a command.

Upvotes: 1

Views: 1443

Answers (1)

Narish
Narish

Reputation: 760

It uses a connection string similar to SQL. public string ConnString => $"Provider=Microsoft.ACE.OLEDB.16.0;Data Source = {FilePathHere};"

To persist to the file using ADO.NET, it will look like

private void ExecuteWrite(string sql)
{
    try
    {
        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            OleDbCommand cmd = new OleDbCommand(sql, conn) { CommandType = CommandType.Text };
            conn.Open();
            _ = cmd.ExecuteNonQuery();
        }
    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message);
        Console.WriteLine(e.ToString());
    }
}

A couple notes on Access:

  1. Access has its own form of SQL which is very different than what you are used to, for example instead of char varchar you will have types like text. Check the full list of differences here
  2. the SQL string you write must use " to escape things such as problematic column names
  3. Tables cannot exceed 255 columns for some reason. Be ready to create seperate queries to split any massive table that goes over that limit, as Access will straight up refuse to process that query. Its the only db I know that suffers from such a limitation and it was a pain for a project I worked on. I got around it using some LINQ to split up the desired columns, and then crafting the separate CREATE or INSERT queries

Upvotes: 1

Related Questions