Access front end, Sql Server backend, Mismatched data type when appending to SQL Server. These are all LinkedTables.
I am pulling 7 percentages .25% into dimmed variables in VBA. The variables are included in a SQL Insert Into statement. The SQL statement runs fine except for the percentages. SQL server 2008 r2 fields are:
This is the Field Type setting in SQL Server:
Dimmed Variables in VBA statement. I have tried many different types of variables, Number, Varient, etc. Nothing works.
Dim varFYTFP4 As Variant
Dim varSYTFP3 As Variant
Dim varSYTFP4 As Variant
Dim varTYTF As Variant
Dim varVPFP5 As Variant
Dim varCEYEAR As Variant
This is the dlookup function that grabs the 7 variables from the Access lookup table:
NumFYTFP3 = Nz(DLookup("FyrTriP3", "dbo_feeScheduleLookup")) 'Tried Number it didn't work.
varFYTFP4 = Nz(DLookup("FYRTriP4", "dbo_feeScheduleLookup"))
varSYTFP3 = Nz(DLookup("SyrTriP3", "dbo_feeScheduleLookup"))
varSYTFP4 = Nz(DLookup("SyrTriP4", "dbo_feeScheduleLookup"))
varTYTF = Nz(DLookup("TyrTriFee", "dbo_feeScheduleLookup"))
varVPFP5 = Nz(DLookup("VOPtabFee5", "dbo_feeScheduleLookup"))
varCEYEAR = Nz(DLookup("CEyear", "dbo_feeScheduleLookup"))
This is the SQL statement the variables are in the last line of the VALUES.
Fields and variables are highlighted in Bold and Text size.
Code:
stSQL2 = "INSERT INTO DBO_PropertyYearDetail" & _
"( PropertyID, County, TownshipNM, TownshipNO, kPIN, CycleID, CycleName, LevelofAssess, kAttyAssigned, FirstYRTriFeeP3, FirstYRTriFeeP4, SecondYrTriFeeP3, SecondYrTriFeeP4, ThirdYrTriFee, VOPTABFeeP5, CEYEAR) " _
& "VALUES ('" & varX2 & "','" & stCnty & "','" & stSelTwn & "','" & _
stTwnNo & "','" & stKPIN & "','" & stCycleID & "','" & stCycleNM & _
"','" & stLOA & "','" & stKattAs & _
"',' & NumFYTFP3 & ',' & varFYTFP4 & ',' & varSYTFP3 & ',' & varSYTFP4 & ',' & varTYTF & ',' & varVPFP5 & ', ' & varCEYEAR & ');"
DoCmd.RunSQL stSQL2
You will see different Dim types and different variables in the SQL statement. That is from my various tries, please ignore.
I cannot find the correct data types in Access VBA that will successfully write to the Sql Table. So far every type has failed.
What variable in the Access VBA statement works with Decimal(2.2) in SQL Server?
Any suggestions?
Fred