G
Greg
I have two versions of the linked table in question on a SQL server. One
loaded with data generated in Access (that one works.) the other loaded from
data in SQL.
The Access program fails to load data when the link is from the SQL loaded
table, and
susceeds when its the Access loaded data from the SQL server
When I run the query in SQL it works. None of the fields are NULL
When I run the query in Access as a SELECT, it works. None of the fields
are NULL
When I do the INSERT in Access it fails.
See attached bmp
It complains "Set 0 field(s) to Null due to a type conversion error"
'create lost_details table
If Not TableExist("lost_details") Then
Set tbl = db.CreateTableDef("lost_details")
With tbl
.Fields.Append .CreateField("index", dbLong)
.Fields.Append .CreateField("period_in_days", dbInteger)
.Fields.Append .CreateField("cust_grouping", dbText, 8)
.Fields.Append .CreateField("cust_group", dbText, 40)
.Fields.Append .CreateField("prod_grouping", dbText, 8)
.Fields.Append .CreateField("prod_group", dbText, 40)
.Fields.Append .CreateField("cust_num", dbText, 10)
.Fields.Append .CreateField("cust_name", dbText, 35)
' .Fields.Append .CreateField("cust_fam", dbText, 20)
' .Fields.Append .CreateField("mkt_seg", dbText, 3)
.Fields.Append .CreateField("lost_date", dbDate)
.Fields.Append .CreateField("com_num", dbText, 10)
.Fields.Append .CreateField("term_est", dbInteger)
' .Fields.Append .CreateField("term_est", dbText, 4)
.Fields.Append .CreateField("reason", dbText, 3)
.Fields.Append .CreateField("qty", dbInteger)
.Fields.Append .CreateField("part_num", dbText, 35)
.Fields.Append .CreateField("part_desc", dbText, 35)
' .Fields.Append .CreateField("model", dbText, 35)
' .Fields.Append .CreateField("part_fam", dbText, 4)
' .Fields.Append .CreateField("class", dbText, 8)
' .Fields.Append .CreateField("seg", dbText, 3)
.Fields.Append .CreateField("bmhs", dbText, 1)
.Fields.Append .CreateField("lp", dbCurrency)
.Fields.Append .CreateField("com_price", dbCurrency)
.Fields.Append .CreateField("perc_lp", dbDouble)
.Fields.Append .CreateField("competitor", dbText, 20)
.Fields.Append .CreateField("comments", dbText, 255)
End With
db.TableDefs.Append tbl
End If
loaded with data generated in Access (that one works.) the other loaded from
data in SQL.
The Access program fails to load data when the link is from the SQL loaded
table, and
susceeds when its the Access loaded data from the SQL server
When I run the query in SQL it works. None of the fields are NULL
When I run the query in Access as a SELECT, it works. None of the fields
are NULL
When I do the INSERT in Access it fails.
See attached bmp
It complains "Set 0 field(s) to Null due to a type conversion error"
'create lost_details table
If Not TableExist("lost_details") Then
Set tbl = db.CreateTableDef("lost_details")
With tbl
.Fields.Append .CreateField("index", dbLong)
.Fields.Append .CreateField("period_in_days", dbInteger)
.Fields.Append .CreateField("cust_grouping", dbText, 8)
.Fields.Append .CreateField("cust_group", dbText, 40)
.Fields.Append .CreateField("prod_grouping", dbText, 8)
.Fields.Append .CreateField("prod_group", dbText, 40)
.Fields.Append .CreateField("cust_num", dbText, 10)
.Fields.Append .CreateField("cust_name", dbText, 35)
' .Fields.Append .CreateField("cust_fam", dbText, 20)
' .Fields.Append .CreateField("mkt_seg", dbText, 3)
.Fields.Append .CreateField("lost_date", dbDate)
.Fields.Append .CreateField("com_num", dbText, 10)
.Fields.Append .CreateField("term_est", dbInteger)
' .Fields.Append .CreateField("term_est", dbText, 4)
.Fields.Append .CreateField("reason", dbText, 3)
.Fields.Append .CreateField("qty", dbInteger)
.Fields.Append .CreateField("part_num", dbText, 35)
.Fields.Append .CreateField("part_desc", dbText, 35)
' .Fields.Append .CreateField("model", dbText, 35)
' .Fields.Append .CreateField("part_fam", dbText, 4)
' .Fields.Append .CreateField("class", dbText, 8)
' .Fields.Append .CreateField("seg", dbText, 3)
.Fields.Append .CreateField("bmhs", dbText, 1)
.Fields.Append .CreateField("lp", dbCurrency)
.Fields.Append .CreateField("com_price", dbCurrency)
.Fields.Append .CreateField("perc_lp", dbDouble)
.Fields.Append .CreateField("competitor", dbText, 20)
.Fields.Append .CreateField("comments", dbText, 255)
End With
db.TableDefs.Append tbl
End If