We help IT Professionals succeed at work.
Get Started

Inserting records with date type fields into Microsoft Access 2013 using VB 2012 - data type mismatch

Last Modified: 2016-01-13
I have a VB 2012 Application that writes to an Access database. My development PC has Access version 2013. The client in question has Access version 2010. I do not know what versions of Access other clients are running (50+ clients). The end user received a "Data mismatch" error. I received the data he was using and replicated the error in the debugger.  There perpetrators were the date fields which the prior programmer formatted for the INSERT command with single quotes around the dates. To solve the issue I had to (a) Place "#" instead of apostrophe around valid dates, and (b) place the word NULL in place of blank date fields. This all seems normal so far.

a. When I cut and pasted the Query from the VB App into access and ran it, I incurred the same data mismatch error, but the record waswritten to the Access Table. In VB, the same error occurred but the record did NOT get written. Does anyone have any idea why?

b. How in the world are my other 50 clients loading this data? The only possibility I can think of is that some versions of Access do not require the "#" symbol to surround the dates?

I am afraid to distribute this patch to anyone else. Unfortunately the client in question has not tried my changes yet, but they worked on my DEV PC with his data.

Below is the connections string, parhapsthis is related -        
strSSConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Me.txtDBName.Text
Watch Question
Top Expert 2015
This problem has been solved!
Unlock 3 Answers and 9 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE