Writing Data To Access2010 from VB2010

Question

I'm trying to write data to an Access 2010 table from VB 2010. Here's the VB code:

        Dim cn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ssr2011f\be\ssrAnalysisTWEAK.ACCdb")
        cn.Open()
        Dim Str As String = "insert into BSP1 values(" & CInt(pcValue) & ",'" & (hsName) & ") "
        Dim cmd As New System.Data.OleDb.OleDbCommand(Str, cn)
        Dim icount As Integer = cmd.ExecuteNonQuery

I get the following error at the line "cn.Open()":

A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll

I also got this error using the Connection string "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ssr2011f\be\ssrAnalysisTWEAK.ACCdb;". My Windows version is 7.0.

Why is the connection failing?

Is the missing quote a paste error ? `& ") "` should be `& "') "` — Fionnuala, Jun 18 '12 at 16:11
@HansUp Hence the comment, rather than answer. It saves time in the end. — Fionnuala, Jun 18 '12 at 17:18
@George Try these Connection Strings and see if they work: [Connection String - Access 2010](http://www.connectionstrings.com/access-2010/) You may have to double wack your directory as well, like `C:\\ssr2011f\\be...` — GoldBishop, Apr 09 '14 at 02:11

Marty · Answer 1 · 2014-08-14T21:14:10.267

1

I have seen errors like this before when using an Access database as a back end, while developing on a computer that is running a 64-bit operating system.

Try explicitly targeting the x86 platform in your Visual Studio project's compile options.

In the solution explorer, right click on your project and select Properties

Select the Compile tab

Click the Advanced Compile Options button

In the Target CPU dropdown, select "x86" (instead of "AnyCPU")

When I did this I was able to execute your code without errors (pointing it to my own access DB of course).

Hope it helps.

edited Aug 14 '14 at 21:14

answered Aug 14 '14 at 17:41

Marty

1,896
1
12
11

Or install the x64 ACE Drivers. By default your system will have either the 32bit or the 64bit ACE drivers installed, dependent on the bitness of the version of MSOffice you installed. You can only have one of these installed at a time if I remember correctly, so having x86 and x64 installed at the same time is out of the question. Also, if you will be deploying your application to another PC which does not have MSOffice installed, you will need to install the ACE drivers and/or make sure the correct bitness of the ACE driver is present on that machine. – codechurn Aug 14 '14 at 21:30

Writing Data To Access2010 from VB2010

1 Answers1