none
connection string based on ms access version present in PC RRS feed

  • Question

  • I have developed a windows application. My development environment is win7, office2007, C#, VS2010, Winforms. My application stores data in MS Access.

    Is there any way with which i can create a connection string based on MS Access version present in PC?


    markand

    Thursday, January 16, 2014 2:28 PM

Answers

  • Hi,

    >> Is there any way with which i can create a connection string based on MS Access version present in PC? <<

    Based on my understanding, I understood there is an issue regarding MS Access connection string.

    As usual, there are three common MS Access in the word. MS Access 2007, MS Access 2010, MS Access 2013. For the above three version, we can use Microsoft ACE OLEDB 12.0 to set up connection. There are two scenarios,

    1. Standard security

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
    Persist Security Info=False;
    

    2. With database password

    This is the connection string to use when you have an Access 2007 - 2013 database protected with a password using the "Set Database Password" function in Access.

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
    Jet OLEDB:Database Password=MyDbPassword;
    

    For MS Access 2003 and earlier version,

    1. Standard security (mdb file)

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.mdb;
    Persist Security Info=False;
    

    2. With database password (mdb file)

    This is the connection string to use when you have an Access 97 - 2003 database protected with a password using the "Set Database Password" function in Access.

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.mdb;
    Jet OLEDB:Database Password=MyDbPassword;

    For more information, please refer to the link below,

    http://www.connectionstrings.com/access/

    As you know, we should know which version of office installed in client. We can use the Registry class

    HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\App Paths

    OR

    HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\App Paths

    After that, we can use different connection string depends on different office version. Please refer to the code below,

        public partial class OfficeComponentsVersion : Form
        {
            public OfficeComponentsVersion()
            {
                InitializeComponent();
            }
    
            private const string RegKey = @"Software\Microsoft\Windows\CurrentVersion\App Paths";
    
            private void Form1_Load(object sender, EventArgs e)
            {
                string _wordPath = GetComponentPath(OfficeComponent.Word);
                string _excelPath = GetComponentPath(OfficeComponent.Excel);
                string _pptPath = GetComponentPath(OfficeComponent.PowerPoint);
                string _outlookPath = GetComponentPath(OfficeComponent.Outlook);
                string _accessPath = GetComponentPath(OfficeComponent.Access);
    
                if (GetMajorVersion(_wordPath) >= 12) lblWord.Text = "2007 OR Higher";
                else if (GetMajorVersion(_wordPath) ==11) lblWord.Text = "2003";
                else lblWord.Text = "Error";
    
                if (GetMajorVersion(_excelPath) >= 12) lblExcel.Text = "2007 OR Higher";
                else if (GetMajorVersion(_excelPath) ==11) lblExcel.Text = "2003";
                else lblExcel.Text = "Error";
    
                if (GetMajorVersion(_pptPath) >= 12) lblPpt.Text = "2007 OR Higher";
                else if (GetMajorVersion(_pptPath) ==11) lblPpt.Text = "2003";
                else lblPpt.Text = "Error";
    
                if (GetMajorVersion(_outlookPath) >= 12) lblOutlook.Text = "2007 OR Higher";
                else if (GetMajorVersion(_outlookPath) ==11) lblOutlook.Text = "2003";
                else lblOutlook.Text = "Error";
    
                if (GetMajorVersion(_accessPath) >= 12) lblAccess.Text = "2007 OR Higher";
                else if (GetMajorVersion(_accessPath) == 11) lblAccess.Text = "2003";
                else lblOutlook.Text = "Error";
            }
    
            /// <summary>
            /// gets the component's path from the registry. if it can't find it - retuns an empty string
            /// </summary>
            /// <param name="_component"></param>
            /// <returns></returns>
            private string GetComponentPath(OfficeComponent _component)
            {
                string toReturn = string.Empty;
                string _key = string.Empty;
    
                switch (_component)
                {
                    case OfficeComponent.Word:
                        _key = "winword.exe";
                        break;
                    case OfficeComponent.Excel:
                        _key = "excel.exe";
                        break;
                    case OfficeComponent.PowerPoint:
                        _key = "powerpnt.exe";
                        break;
                    case OfficeComponent.Outlook:
                        _key = "outlook.exe";
                        break;
                    case OfficeComponent.Access:
                        _key = "MSACCESS.EXE";
                        break;
                }
    
                //looks inside CURRENT_USER:
                RegistryKey _mainKey = Registry.CurrentUser;
                try
                {
                    _mainKey = _mainKey.OpenSubKey(RegKey + "\\" + _key, false);
                    if (_mainKey != null)
                    {
                        toReturn = _mainKey.GetValue(string.Empty).ToString();
                    }
                }
                catch
                { }
    
                //if not found, looks inside LOCAL_MACHINE:
                _mainKey = Registry.LocalMachine;
                if (string.IsNullOrEmpty(toReturn))
                {
                    try
                    {
                        _mainKey = _mainKey.OpenSubKey(RegKey + "\\" + _key, false);
                        if (_mainKey != null)
                        {
                            toReturn = _mainKey.GetValue(string.Empty).ToString();
                        }
                    }
                    catch
                    {
                    }
                }
    
                //closing the handle:
                if (_mainKey != null)
                    _mainKey.Close();
    
                return toReturn;
            }
    
            /// <summary>
            /// Gets the major version of the path. if file not found (or any other exception occures
            /// - returns 0
            /// </summary>
            private int GetMajorVersion(string _path)
            {
                int toReturn = 0;
                if (File.Exists(_path))
                {
                    try
                    {
                        FileVersionInfo _fileVersion = FileVersionInfo.GetVersionInfo(_path);
                        toReturn = _fileVersion.FileMajorPart;
                    }
                    catch
                    { }
                }
    
                return toReturn;
            }
        }
    
        public enum OfficeComponent
        {
            Word,
            Excel,
            PowerPoint,
            Outlook,
            Access
        }

    The result is shown in the following figure,

    Since I have installed Office 2013, so it shown like above figure. BTW, I have uploaded the project on SkyDrive. You can download from here.

    For more information, please refer to the link below,

    http://www.codeproject.com/Articles/26520/Getting-Office-s-Version

    If I misunderstood or my points are incorrect, please feel free to let me know.

    Have a nice time!

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 17, 2014 3:04 AM
  • Hi,

    >> But what if the provider driver is not installed in the PC? <<

    If MS Access version is 2007 or higher. It has installed “Microsoft.ACE.OLEDB.12.0” provider in your machine.

    If MS Access version is 2003 or lower. You should install “Microsoft.ACE.OLEDB.12.0” provider first. Please check out the link below,

    http://www.microsoft.com/en-in/download/details.aspx?id=13255

    You can include the provider in your project setup.

    Have a nice time!

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 17, 2014 6:54 AM

All replies

  • Hi,

    >> Is there any way with which i can create a connection string based on MS Access version present in PC? <<

    Based on my understanding, I understood there is an issue regarding MS Access connection string.

    As usual, there are three common MS Access in the word. MS Access 2007, MS Access 2010, MS Access 2013. For the above three version, we can use Microsoft ACE OLEDB 12.0 to set up connection. There are two scenarios,

    1. Standard security

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
    Persist Security Info=False;
    

    2. With database password

    This is the connection string to use when you have an Access 2007 - 2013 database protected with a password using the "Set Database Password" function in Access.

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
    Jet OLEDB:Database Password=MyDbPassword;
    

    For MS Access 2003 and earlier version,

    1. Standard security (mdb file)

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.mdb;
    Persist Security Info=False;
    

    2. With database password (mdb file)

    This is the connection string to use when you have an Access 97 - 2003 database protected with a password using the "Set Database Password" function in Access.

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.mdb;
    Jet OLEDB:Database Password=MyDbPassword;

    For more information, please refer to the link below,

    http://www.connectionstrings.com/access/

    As you know, we should know which version of office installed in client. We can use the Registry class

    HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\App Paths

    OR

    HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\App Paths

    After that, we can use different connection string depends on different office version. Please refer to the code below,

        public partial class OfficeComponentsVersion : Form
        {
            public OfficeComponentsVersion()
            {
                InitializeComponent();
            }
    
            private const string RegKey = @"Software\Microsoft\Windows\CurrentVersion\App Paths";
    
            private void Form1_Load(object sender, EventArgs e)
            {
                string _wordPath = GetComponentPath(OfficeComponent.Word);
                string _excelPath = GetComponentPath(OfficeComponent.Excel);
                string _pptPath = GetComponentPath(OfficeComponent.PowerPoint);
                string _outlookPath = GetComponentPath(OfficeComponent.Outlook);
                string _accessPath = GetComponentPath(OfficeComponent.Access);
    
                if (GetMajorVersion(_wordPath) >= 12) lblWord.Text = "2007 OR Higher";
                else if (GetMajorVersion(_wordPath) ==11) lblWord.Text = "2003";
                else lblWord.Text = "Error";
    
                if (GetMajorVersion(_excelPath) >= 12) lblExcel.Text = "2007 OR Higher";
                else if (GetMajorVersion(_excelPath) ==11) lblExcel.Text = "2003";
                else lblExcel.Text = "Error";
    
                if (GetMajorVersion(_pptPath) >= 12) lblPpt.Text = "2007 OR Higher";
                else if (GetMajorVersion(_pptPath) ==11) lblPpt.Text = "2003";
                else lblPpt.Text = "Error";
    
                if (GetMajorVersion(_outlookPath) >= 12) lblOutlook.Text = "2007 OR Higher";
                else if (GetMajorVersion(_outlookPath) ==11) lblOutlook.Text = "2003";
                else lblOutlook.Text = "Error";
    
                if (GetMajorVersion(_accessPath) >= 12) lblAccess.Text = "2007 OR Higher";
                else if (GetMajorVersion(_accessPath) == 11) lblAccess.Text = "2003";
                else lblOutlook.Text = "Error";
            }
    
            /// <summary>
            /// gets the component's path from the registry. if it can't find it - retuns an empty string
            /// </summary>
            /// <param name="_component"></param>
            /// <returns></returns>
            private string GetComponentPath(OfficeComponent _component)
            {
                string toReturn = string.Empty;
                string _key = string.Empty;
    
                switch (_component)
                {
                    case OfficeComponent.Word:
                        _key = "winword.exe";
                        break;
                    case OfficeComponent.Excel:
                        _key = "excel.exe";
                        break;
                    case OfficeComponent.PowerPoint:
                        _key = "powerpnt.exe";
                        break;
                    case OfficeComponent.Outlook:
                        _key = "outlook.exe";
                        break;
                    case OfficeComponent.Access:
                        _key = "MSACCESS.EXE";
                        break;
                }
    
                //looks inside CURRENT_USER:
                RegistryKey _mainKey = Registry.CurrentUser;
                try
                {
                    _mainKey = _mainKey.OpenSubKey(RegKey + "\\" + _key, false);
                    if (_mainKey != null)
                    {
                        toReturn = _mainKey.GetValue(string.Empty).ToString();
                    }
                }
                catch
                { }
    
                //if not found, looks inside LOCAL_MACHINE:
                _mainKey = Registry.LocalMachine;
                if (string.IsNullOrEmpty(toReturn))
                {
                    try
                    {
                        _mainKey = _mainKey.OpenSubKey(RegKey + "\\" + _key, false);
                        if (_mainKey != null)
                        {
                            toReturn = _mainKey.GetValue(string.Empty).ToString();
                        }
                    }
                    catch
                    {
                    }
                }
    
                //closing the handle:
                if (_mainKey != null)
                    _mainKey.Close();
    
                return toReturn;
            }
    
            /// <summary>
            /// Gets the major version of the path. if file not found (or any other exception occures
            /// - returns 0
            /// </summary>
            private int GetMajorVersion(string _path)
            {
                int toReturn = 0;
                if (File.Exists(_path))
                {
                    try
                    {
                        FileVersionInfo _fileVersion = FileVersionInfo.GetVersionInfo(_path);
                        toReturn = _fileVersion.FileMajorPart;
                    }
                    catch
                    { }
                }
    
                return toReturn;
            }
        }
    
        public enum OfficeComponent
        {
            Word,
            Excel,
            PowerPoint,
            Outlook,
            Access
        }

    The result is shown in the following figure,

    Since I have installed Office 2013, so it shown like above figure. BTW, I have uploaded the project on SkyDrive. You can download from here.

    For more information, please refer to the link below,

    http://www.codeproject.com/Articles/26520/Getting-Office-s-Version

    If I misunderstood or my points are incorrect, please feel free to let me know.

    Have a nice time!

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 17, 2014 3:04 AM
  • Thanx Jimmy Yang.

    As per your answer, Provider "Microsoft.ACE.OLEDB.12.0" will work with any version of MS Access.

    But what if the provider driver is not installed in the PC?

    What to do at that stage?


    markand


    Friday, January 17, 2014 6:17 AM
  • Hi,

    >> But what if the provider driver is not installed in the PC? <<

    If MS Access version is 2007 or higher. It has installed “Microsoft.ACE.OLEDB.12.0” provider in your machine.

    If MS Access version is 2003 or lower. You should install “Microsoft.ACE.OLEDB.12.0” provider first. Please check out the link below,

    http://www.microsoft.com/en-in/download/details.aspx?id=13255

    You can include the provider in your project setup.

    Have a nice time!

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 17, 2014 6:54 AM