troubleshooting Question

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

Avatar of JamesDBuskirk
JamesDBuskirkFlag for United States of America asked on
Microsoft AccessVisual Basic.NET
9 Comments3 Solutions123 ViewsLast Modified:
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
SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 3 Answers and 9 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 3 Answers and 9 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004