locked
Connection string to Access 2010 .accdb RRS feed

  • Question

  • I saw this on Google and decided to try to use it:

     

    System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=smsdata.accdb;Persist Security Info=False;");

    The system tells me that Microsoft.ACE.OLEDB.12.0 is not registered on the machine or that the machine can not find it. What should I do about this? The application I am building will not have to be a web based application. It is just a regular system database built on Access 2010.

    Tuesday, April 5, 2011 5:37 PM

Answers

  • You need to install the Office System Driver.  Once this is installed on your system, the connection string above should work.

     


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Proposed as answer by Levi Domingos Tuesday, April 5, 2011 9:51 PM
    • Marked as answer by S.e.p.y Tuesday, April 5, 2011 10:08 PM
    Tuesday, April 5, 2011 6:01 PM
  • Setting PlatformTarget

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    • Marked as answer by S.e.p.y Tuesday, April 5, 2011 11:36 PM
    Tuesday, April 5, 2011 10:15 PM

All replies

  • You need to install the Office System Driver.  Once this is installed on your system, the connection string above should work.

     


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Proposed as answer by Levi Domingos Tuesday, April 5, 2011 9:51 PM
    • Marked as answer by S.e.p.y Tuesday, April 5, 2011 10:08 PM
    Tuesday, April 5, 2011 6:01 PM
  • I installed and rebooted however I am still getting the same error as before. The system states that the provider is not registered.
    Tuesday, April 5, 2011 6:21 PM
  • You code may be JITted to the wrong bitness. Check the Access Driver's bitness and change your app's target platform (e.g. AnyCPU) to the same bitness (X86 or X64 based on the version of Access driver installed on the user's system). You can find more discussions in the 64bit development forum in the .Net development category.

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Tuesday, April 5, 2011 8:55 PM
  • You did answer my question, your solution just ended up not functioning. That's not your fault. I think Sheng might have something really useful in his reply on how to make things function but I can not try it because I only have the Express Edition of C# for right now that is all I can afford. I'm hoping I keep programming forever and then I will buy the good tools.
    Tuesday, April 5, 2011 10:09 PM
  • Setting PlatformTarget

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    • Marked as answer by S.e.p.y Tuesday, April 5, 2011 11:36 PM
    Tuesday, April 5, 2011 10:15 PM
  • Yes. That is spot on. That is exactly it. All I did was add  <PlatformTarget>x86</PlatformTarget> to the first property group as shown below. Thank you Jeng you are a great help.

     

    <?xml version="1.0" encoding="utf-8"?>

    <Project ToolsVersion="3.5" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

      <PropertyGroup>

        <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>

        <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>

        <ProductVersion>9.0.30729</ProductVersion>

        <SchemaVersion>2.0</SchemaVersion>

        <ProjectGuid>{37A5747D-783B-48A7-AFE8-F1B99FCDE8C6}</ProjectGuid>

        <OutputType>Exe</OutputType>

        <AppDesignerFolder>Properties</AppDesignerFolder>

        <RootNamespace>importdata</RootNamespace>

        <AssemblyName>importdata</AssemblyName>

        <TargetFrameworkVersion>v3.5</TargetFrameworkVersion>

        <FileAlignment>512</FileAlignment>

    <PlatformTarget>x86</PlatformTarget>

      </PropertyGroup>

    Tuesday, April 5, 2011 11:36 PM
  • The easiest place to get your ConnectionStrings is from here:

    http://www.connectionstrings.com/

    I think this is what you want:

    http://www.connectionstrings.com/excel-2007


    Ryan Shuell





    • Edited by ryguy72 Thursday, April 5, 2012 4:53 AM
    Thursday, April 5, 2012 3:46 AM
  • I had the same problem. found it!

    copy this project:

    http://msdn.microsoft.com/en-us/library/cc811599%28v=office.12%29.aspx#Ac2007DevelopingSolutions_OLEDB

    the problem was:

    http://www.dbforums.com/3153006-post2.html

    solved with:

    http://nogeekhere.blogspot.com/2009/01/more-explanation-of-coinitialize-and.html

    final code (don't forget to change: pad [line 10] and sql statement [line 56] )

    #include <iostream>
    #include <oledb.h>
    #include <atldbcli.h>
    #include <atldbsch.h>
    
    using namespace std;
    
    LPCOLESTR lpcOleConnect = L"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\\Studenten.accdb;Persist Security Info=False;";//;User Id=admin;Password=;"
    
    const char* DAM = "OLEDB";
    
    int main(){
    
    	// To initialize the connection to a database using an OLE DB provider, 
    	// two ATL classes are needed: CDataSource and CSession.
    	CDataSource dbDataSource;
    	CSession dbSession;
    
    	// Uses ATL's string conversion macros to convert between character encodings.
    	USES_CONVERSION;
    
    	// Open the connection and initialize the data source specified by the passed 
    	// initialization string.
    	HRESULT hr = E_FAIL;
    
    	hr = CoInitializeEx(NULL, COINIT_APARTMENTTHREADED);
    	hr = CoInitialize(NULL);
    	hr = dbDataSource.OpenFromInitializationString(lpcOleConnect);
    	if (FAILED(hr))
    	{
    		cout<<DAM<<": Unable to connect to data source "<<OLE2T(lpcOleConnect)<<endl;
    	}
    	else
    	{
    		hr = dbSession.Open(dbDataSource);
    		if (FAILED(hr))
    		{
    			cout<<DAM<<": Couldn't create session on data source "<<OLE2T(lpcOleConnect)<<endl;
    		}
    		else
    		{
    			CComVariant var;
    			hr = dbDataSource.GetProperty(DBPROPSET_DATASOURCEINFO, DBPROP_DATASOURCENAME, &var);
    			if (FAILED(hr) || (var.vt == VT_EMPTY))
    			{
    				cout<<DAM<<": No Data Source Name Specified."<<endl;
    			}
    			else
    			{
    				cout<<DAM<<": Successfully connected to database. Data source name:\n  "
    					<<COLE2T(var.bstrVal)<<endl;
                
    				// Prepare SQL query.
    				LPCOLESTR query = L"SELECT StudentenVoornaam,StudentenNaam FROM Studenten;";
    				cout<<DAM<<": SQL query:\n  "<<OLE2T(query)<<endl;
    
    				// Excecute the query and create a record set.
    				CCommand<CDynamicStringAccessor> cmd;
    				hr = cmd.Open(dbSession, query);
    				DBORDINAL colCount = cmd.GetColumnCount();
    				if (SUCCEEDED(hr) && 0 < colCount)
    				{
    					cout<<DAM<<": Retrieve schema info for the given result set: "<<endl;
    					DBORDINAL cColumns;
    					DBCOLUMNINFO* rgInfo = NULL;
    					OLECHAR* pStringsBuffer = NULL;
    					cmd.GetColumnInfo(&cColumns, &rgInfo, &pStringsBuffer);
    					for (int col=0; col < (int)colCount; col++)
    					{
    						cout<<" | "<<OLE2T(rgInfo[col].pwszName);
    					}
    					cout<<endl;
    
    					cout<<DAM<<": Fetch the actual data: "<<endl;
    					int rowCount = 0;
    					CRowset<CDynamicStringAccessor>* pRS = (CRowset<CDynamicStringAccessor>*)&cmd;
    					// Loop through the rows in the result set.
    					while (pRS->MoveNext() == S_OK)
    					{
    						for (int col=1; col <= (int)colCount; col++)
    						{
    							CHAR* szValue = cmd.GetString(col);
    							cout<<" | "<<szValue;
    						}
    						cout<<endl;
    						rowCount++;
    					}
    					cout<<DAM<<": Total Row Count: "<<rowCount<<endl;
    				}                   
    				else
    				{
    					cout<<DAM<<": Error: Number of fields in the result set is 0."<<endl;
    				}
    			}  
    		}
    	}
    
    	dbDataSource.Close();
    	dbSession.Close();
    	CoUninitialize(); 
    	cout<<DAM<<": Cleanup. Done."<<endl;
    	cin.get();
    }

    • Edited by bisschops 99 Saturday, April 7, 2012 7:13 PM links
    Saturday, April 7, 2012 7:12 PM