Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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.OL
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.OL
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
the JET driver is for A2003 and lower version
try this provider for A2013
Provider=Microsoft.ACE.OLE
Persist Security Info=False;
try this provider for A2013
Provider=Microsoft.ACE.OLE
Persist Security Info=False;
There is no difference between different versions of Access or even different programs for most of Microsoft application. They all follow the COM standard, and thus this should not be a concern to you... as long as you understand the standard.
What you need then is to understand the difference between dates between quotes and dates between #. Also, if you work with the Access query designer, you need to know that # does not have the same meaning there as it has in VB.
When you use quotes, either in VB or in the query designer, dates in quotes are considered as text. The system thus needs to make a conversion between the text and a date, that is recorded as a number (Long) in the database. This conversion is made taking into account the Control Panel. Thus, '2015-02-01' will come as Feb 1 on some computers, Jan 2 on some others, and will simply trigger an error on a computer where the Control Panel is set for dd-mm-yyyy because 2015 is not valid for a day.
One will thus never use quotes with dates in VB or in a query.
In these, the developer needs to enter dates between #.
In VB, a date between # is always month-day-year. It will have the same value no matter on which computer it is displayed, but with normal code, will display according to the Control Panel configuration. Thus #02-01-2015# will always be Feb 1, but can be displayed 1 Feb or 1 février.
The meaning of the # in the query designer is different, because that tool was created first for a non-programmer user. In a query, you type the date between # with the format expected on the computer on which you are typing. The designer will translate it to mm-dd-yyyy in the SQL that it generates, thus following the Microsoft convention. If you reopen the same query in the designer on another computer, it will take what it sees in the SQL, and before displaying it in the designer, grid, will translate, between #, to the format defined in the Control Panel so that the user sees the date in the format he is used to on his computer.
When you retrieve a date from a control, it always comes as Text. Your role as a programmer is to assume that the user knows how to type a date according to his Control Panel. He already type dates in Excel, doesn't he? Then do as Excel does and simply convert it to a Date by using one of the date conversion methods. It's not your role to analyze the date, the date conversion methods will do it as long as it is a date that is valid for the Control Panel. Once converted to a Date, you have not problem using it.
Now, one problem I see everywhere is programmers who use Text fields to record dates. Sorry for them, they are doomed. Different users will enter dates in different formats, and they will all be recorded as is in the Text Field, ending up in a total mess. Dates should always be recorded in a Date field in the database, and handled in Date variables in code.
What you need then is to understand the difference between dates between quotes and dates between #. Also, if you work with the Access query designer, you need to know that # does not have the same meaning there as it has in VB.
When you use quotes, either in VB or in the query designer, dates in quotes are considered as text. The system thus needs to make a conversion between the text and a date, that is recorded as a number (Long) in the database. This conversion is made taking into account the Control Panel. Thus, '2015-02-01' will come as Feb 1 on some computers, Jan 2 on some others, and will simply trigger an error on a computer where the Control Panel is set for dd-mm-yyyy because 2015 is not valid for a day.
One will thus never use quotes with dates in VB or in a query.
In these, the developer needs to enter dates between #.
In VB, a date between # is always month-day-year. It will have the same value no matter on which computer it is displayed, but with normal code, will display according to the Control Panel configuration. Thus #02-01-2015# will always be Feb 1, but can be displayed 1 Feb or 1 février.
The meaning of the # in the query designer is different, because that tool was created first for a non-programmer user. In a query, you type the date between # with the format expected on the computer on which you are typing. The designer will translate it to mm-dd-yyyy in the SQL that it generates, thus following the Microsoft convention. If you reopen the same query in the designer on another computer, it will take what it sees in the SQL, and before displaying it in the designer, grid, will translate, between #, to the format defined in the Control Panel so that the user sees the date in the format he is used to on his computer.
When you retrieve a date from a control, it always comes as Text. Your role as a programmer is to assume that the user knows how to type a date according to his Control Panel. He already type dates in Excel, doesn't he? Then do as Excel does and simply convert it to a Date by using one of the date conversion methods. It's not your role to analyze the date, the date conversion methods will do it as long as it is a date that is valid for the Control Panel. Once converted to a Date, you have not problem using it.
Now, one problem I see everywhere is programmers who use Text fields to record dates. Sorry for them, they are doomed. Different users will enter dates in different formats, and they will all be recorded as is in the Text Field, ending up in a total mess. Dates should always be recorded in a Date field in the database, and handled in Date variables in code.
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.
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.
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.
There is a debate over that, but personnally, I almost never permit Null in my fields. They cause more headaches than they solve.
Instead of dealing with nulls or using an extra field as you want to do, I simply define a date (or any other type of value for other types of fields) that is completely out of the range of expected dates, something like 01-01-1900 or 12-31-9999. I define it as the default date, and consider it to be a null when I encounter it.
Instead of dealing with nulls or using an extra field as you want to do, I simply define a date (or any other type of value for other types of fields) that is completely out of the range of expected dates, something like 01-01-1900 or 12-31-9999. I define it as the default date, and consider it to be a null when I encounter it.
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("y
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
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("y
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!
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!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial