Link to home
Start Free TrialLog in
Avatar of JamesDBuskirk
JamesDBuskirkFlag for United States of America

asked on

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

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
Avatar of JamesDBuskirk
JamesDBuskirk
Flag of United States of America image

ASKER

I just found out that the JET Driver does not require MS Access on the local PC, therefore versions of Access are unrelated and work I did in Access really does not count.
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very much for the detail. My VB programs have always used "#" on date type fields for both Access and SQL and as you suggest this works great. As I mentioned I did not originally author this project. I was unaware of the Windows involvement when the date is surrounded by apostrophe, this information  is quite useful. The data in this case is generate by another computer program (Hospital Clinical Systems) so every important date we encounter is valid as it has been edited by the program. They get checked despite this fact. Other date fields are blank by design, therefore I was forced to set these to NULL. These are dates associated with a potential event, if said event did not occur then a date does not exist.

I believe the current code corrected to use "#" for valid dates and NULL for invalid dates is best per your suggestions. I will test the "Ace" driver mentioned above, as my driver seems quite dated and I am puzzled why me and one end user has incurred this issue, but no one else has.
Note that ACE is for the newer .accdb Access file format. If your database is still in .mdb, then not need it. JET was designed for JET. It was installed automatically with older versions of Windows. But be careful when you say "the JET Driver does not require MS Access". You might have to install it for users who are on Windows 8 or more.
After testing with the original code (apostrophe's for actual dates) and adding the code to place NULL in the INSERT command for a bad date, this works. I expected this after reading Jacques's explanation, thanks again. Therefore the data mismatch errors are all in the aforementioned date fields that can be blank by design that are related to medical "Restraint Events".

I will stick with the JET drivers for now as all clients have old MDB as a template when they create new databases.

I will add the "#" back into the real date fields but wanted to refine the cause of this issue at the client site to the smallest set of conditions. In the original VB code, these un-populated "Restraint Event" dates would have been in the query as eight spaces surrounded by a single quote. Still vexed however why 50+ clients endure this and one does not. These "Restraint Event" fields are rarely populated. I'm going to check the offsets on the fixed length data commands to see if one is off by a character. The field JUST to the right of the date gets populated with a "777" when no restraint event occurs (the state makes these rules . . .). Perhaps a "7" drifted into one of the date fields and the windows "Control Panel" date conversion obviously would not return a good date.


I do have a much information as I think I can, now for psychological torture of allocating the 'points' :) Thank you both for your time.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Your problem has nothing to do with Null or not, but more likely with the format of the string expression for the date you insert in the SQL string.

Always apply a format of "yyyy/mm/dd"
However, "/" is the date separator in Access, so you have to escape it. If you use VB.NET, like:

    Dim SqlDate As String
    SqlDate = DateTime.Today.ToString("yyyy'/'MM'/'dd")

And as you have found out, the delimiter is always #. You may have met examples where single-quotes are applied, but that is for SQL Server. How the code can have been working for years using single-quotes is pure luck.

/gustav
Ultimately, the INSERTS work with apostrophe or # when a valid date is present, per Jacques remarks. I receive in my data eight digit dates in MMDDCCYY format that I convert into SQL compliant dates then INSERT records. It turns out the issue was solely due to blank date type fields.I was inserting eight spaces, now I am using the word NULL when it is blank.  

I incurred another bug related to this today.  I also get legitimate "99999999" procedure dates that indicate a surgical procedure was performed but the date of the procedure is unknown (I have no idea how that can happen . . .). These I need to think about but for now I must make NULL.

Thank you all again!