Nulls and Strings comming from SQL

[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

 

[a a r o n . k e m p f]

Jesus dude-- you should jsut learn DDL and fire a statement

strSql = "CREATE TABLE tblName (id int identity primary key clustered"
strSQL = strSQL & "field1 varchar,"
strSQL = strSQL & "field2 varchar,"
strSQL = strSQL & "field3 varchar,"
strSQL = strSQL & "field4 varchar,"
strSQL = strSQL & "field5 varchar,"
strSQL = strSQL & "field6 varchar,"
strSQL = strSQL & "field7 varchar)"

To me, that is a lot easier to read-- and it's also easier to write,
because SQL Server will write DDL statements for you automatically.

In SQL Server Management Studio, you just right-click as Create To New
Query Window, and it will generate the DDL statement for you.

I just prefr using SQL Server tools, Jet just adds a lot of
unnecessary complexity (and worse debugging and performance) to
everything it touches.

-Aaron

 

[BruceM]

I'm trying to understand why you are creating a table in code.

If that is all of your code you haven't defined the db or tbl variables as
DAO.Database and TableDef. If that is something you left out of the code
posted here, but it's in your actual code, try stepping through the code to
see what line is causing the problem. Or, try creating a one-field table,
then add the lines a few at a time to see which one causes the failure.