Data-type mismatch Access 2010

Question

We have been creating a HR Database using Access as the back-end and Excel as the front-end. When I run my macro in Excel to insert entries into the MasterTable it says; "Data Type Mismatch". The only field that I had changed was the "Job" Field which required a value between 0.0 - 1.0 (i.e. 0.2 means they are only working one day).

Previously when I inserted entries in the decimal place would not show until I changed the field type in Access to accept decimals. After this change, the macro no longer works.

Can anyone point out why this is?

I have only just started learning SQL/Access so it is very likely I made a very basic mistake.

I searched up on SO a few other answers which talked about using the DECIMAL field instead of changing the properties field but this didn't solve my issue. My code:

 Sub ExportDataToAccess() Dim cn As Object Dim strQuery As String Dim Id As String Dim Positions As String Dim BU As String Dim Job As Double Dim Variance As String Dim myDB As String 'Initialize Variables Id = Worksheets("test").Range("A2").Value Positions = Worksheets("test").Range("B2").Value BU = Worksheets("test").Range("C2").Value Job = Worksheets("test").Range("D2").Value myDB = "X:\Users\ADMIN\Documents\HR_Establishment_DB1.accdb" Set cn = CreateObject("ADODB.Connection") With cn .Provider = "Microsoft.ACE.OLEDB.12.0" 'For *.ACCDB Databases .ConnectionString = myDB 'Connects to my DB .Open End With strQuery = "INSERT INTO MasterTable ([Id], [Positions], [BU], [Job]) " & _ "VALUES (""" & Id & """, """ & Positions & """, """ & BU & """, " & Job & "); " cn.Execute strQuery cn.Close Set cn = Nothing End Sub

danielle · Accepted Answer · 2015-10-21 06:08:07Z

Do you intend the value of ID to be obtained from the excel (Id = Worksheets("test").Range("A2").Value)?

I think it is causing the error. If a field on access is of AutoNumber data type, you don't have to include it on your INSERT query as access automatically assigns a value for this in incremental manner.

If you want access to automatically assign a value for ID, change this:

 strQuery = "INSERT INTO MasterTable ([Id], [Positions], [BU], [Job]) " & _ "VALUES (""" & Id & """, """ & Positions & """, """ & BU & """, " & Job & "); "

to this:

 strQuery = "INSERT INTO MasterTable ([Positions], [BU], [Job]) " & _ "VALUES (""" & Positions & """, """ & BU & """, " & Job & "); "

Job has the data type Number with a field size of "double" and is accepting up to 4 Decimal Places.
have you tried casting your job variable to double? Try changing this: 'Job = Worksheets("test").Range("D2").Value' to this: 'Job = CDbl(Worksheets("test").Range("D2").Value)'
Hi Danielle, I tried that as well and it's the same error still. :(
Can you put a breakpoint on cn.Execute strQuery and take note of the value of strQuery and post it here?
strQuery = "INSERT INTO MasterTable ([Id], [Positions], [BU], [Job]) VALUES ("", "Business Analyst", "Finance", 0.8); " And then it breaks at that point for reasons unknown.

Collectives™ on Stack Overflow

Data-type mismatch Access 2010

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related