Access Database export problem

Hello everybody,

This is my first post here. I currently want to integrate the measurement data into our company access database. I have created and registered an MDB database via the database wizard. Also, I inserted the STAT ON command into one of our programs to test out the feature. Sadly when the program reached this command, I received an error message like this:

Error[-1] in [Transaction]
Data type mismatch in criteria
State:22005, Native: -3030, Origin:[Microsoft][ODBC Microsoft Access Driver]

What am I doing wrong?
Is it possible that the reason of this error message is that I use PC-DMIS in Hungarian, where the decimal separator is ","? I think the program should handle this (The CSV export uses "." as decimal separator) and I would rather not experiment with the language settings, because once it jammed my probe file.

Thank you in advance!

vpt.se over 10 years ago

Make sure the values (datatypes) you export to your database are correctly formatted (numbers, text, etc):

http://office.microsoft.com/en-us/access-help/i-get-a-message-about-type-mismatch-HP001122322.aspx
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
Imre Pem over 10 years ago

Thanks for the advice. I will check this soon, though I expect PC-DMIS create its own database well formatted.

On more piece of clue: The OS is Windows XP, and MS Access is not installed on the particluar machine which runs PC-DMIS.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
RandomJerk over 10 years ago

It's probably seeing the date field in the CSV as a string, and you have the access field specified as date. I'm wondering if cDate(DateField) will do what you need, though I'm not even sure there is a function for cDate (I use cStr and cInt regularly, so I'm guessing)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
Imre Pem over 10 years ago

Hello,

Thank You for the advices. Formatting the MDB database fields could not solve my problem, so I switched to English language in PC-DMIS and now everything works perfectly. Now I will have to train my operators, but that is not a big problem. Anyway, I think I have a bug, where can I report it?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
RandomJerk over 10 years ago
I had the opposite problem you had (I had dd/mm/yyyy formatting) in the output file from a data logger, I wound up writing a string hacking subroutine to change them.

Sub FixDate() Dim i As Double, ii As Double, FF As String, MM As String, DD As String, YY As String i = 1 Do i = i + 1 FF = Cells(i, 1).Value YY = Right(FF, 4) FF = Left(FF, Len(FF - 5)) For a = 1 To 3 If Mid(FF, a, 1) = "/" Then DD = Left(FF, a - 1) MM = Right(FF, Len(FF) - a) Exit For End If Next a Cells(i, 2).Value = MM + "/" + DD + "/" + YY Loop Until Cells(i + 1, 1).Value = "" End Sub

My dates were coming in like "21/7/2014", so the routine saves the last four of the string for the year, cuts off the last 5 (to get rid of the slash), then just finds the slashes to get the values and concatenates the pieces into a new date code. This routine should work for you with no changes.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel