Question Connecting to Access 2010 Database

mh1hep

New member
Joined
Jan 5, 2011
Messages
2
Programming Experience
3-5
Sorry if this is a duplicate post but there are so many I can't figure out what applies to me.

I had a .NET 2003 application that connected to an Access 2000 database using ODBC.

I upgraded this to .NET 2008 and Access 2010 in a Windows 7 environment.

My connection string used to be

"PageTimeout=5;DSN=MS Access Database;DefaultDir=" & dbpath & ";DriverId=25;DBQ="C:\MGSDATA_VB\Access_MGSDATA_2010.mdb;FIL=MS"

That gave me a data architecture error when I tried to connect.

I downloaded the Access 2010 database engine and changed the connection string to:

"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\MGSDATA_VB\Access_MGSDATA_2010.mdb"

Now I get this message when I try to connect:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified



What is the problem? I'm certain that the path on my C: drive is correct since it worked on the old version.

Thanks
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,723
Location
Sydney, Australia
Programming Experience
10+
Why use ODBC? For Access, regardless of version, your first choice should be OLE DB. If you're using an MDB file then you don't need the Access Connectivity Engine (ACE) because the nuilt in Jet engine can handle connection. You can use ACE if you want to, and you must if your data file is an ACCDB, but then every user must either install Access or ACE. For the correct connection string format, visit:

ConnectionStrings.com - Forgot that connection string? Get it here!

Having said all that, the most likely cause of your issue is that you're developing on a 64-bit OS. There is no 64-bit version of Jet or ACE 12 (2007). There is a 64-bit version of ACE 14 (2010), but that's not as good as it sounds. If you install Access 2010 or ACE 14, you can choose either 32-bit or 64-bit, but you cannot install both.

Now, by default, your project will target Any CPU, which means that it will run in 32-bit mode on a 32-bit OS and in 64-bit mode on a 64-bit OS. If your app runs in 64-bit mode, it cannot use 32-bit versions of Jet or ACE. That means that you, and anyone else who uses your app on a 64-bit OS, will have to install the 64-bit version of Access 2010 or ACE 14. Given that so many people will want to install the 32-bit version of Office, that's usually not a genuine option.

What you will probably need to do, and what most others do, is change the target platform of your project from Any CPU to x86. This forces your app to run in 32-bit mode always, so it can always use 32-bit Jet or ACE.
 

mh1hep

New member
Joined
Jan 5, 2011
Messages
2
Programming Experience
3-5
Thanks very much. I have to say I really wasn't expecting such an esoteric solution to work but it did. Who knew 64/32 bit conflicts could cause a problem like this. I went into config manager in Build and just changed the platform type to x86, and nothing else, and it worked!!


thanks again
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,723
Location
Sydney, Australia
Programming Experience
10+
Who knew 64/32 bit conflicts could cause a problem like this.

Anyone who's ever tried to use a COM component on a 64-bit platform, unfortunately. Jet is a COM component and COM components are pretty much exclusively 32-bit. The same applies to using the Shockwave/Flash component for displaying Flash animations in a .NET app or the Adobe Reader component for displaying PDFs in a .NET app.
 
Top Bottom