Yes, it's not a data quality issue. The problem is related to the way SQL Server is handling the validation rules that were setup in access. Here's an example.
this statement:
insert into LinkedAccessDB...MyProfileTable (CLIAUPIN, TERR, NAME, zip, ATYPE)
select 'G66666' as CLIAUPIN, 'XW09' as TERR, 'test' as NAME, '00000' as zip, 'GP' as ATYPE
throws this error:
Server: Msg 7343, Level 16, State 2, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' could not INSERT INTO table '[LinkedAccessDB]...[MyProfileTable ]'. Unknown provider error.
[OLE/DB provider returned message: One or more values are prohibited by the validation rule 'Is Null Or Like "[A-Z]#####" Or Like "##[A-Z]#######" Or Like "DOC####" Or Like "LAB####" Or Like "RES####" Or Like "GVD####" Or Like "GVL####" Or Like "NFD*"' set for 'MyProfileTable.CLIAUPIN'. Enter a value that the expression for this field can accept.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IRowsetChange::InsertRow returned 0x80040e03: Unknown provider error.].
so the value i'm trying to insert for the field CLIAPUIN meets the constraints in the Access Database (it meets the first one - alpha char followed by 5 numbers), but it's reporting an error - i'm guessing becasue sql server doesn't know what to do with these validation rules in access?
Sincerely,
Matthew Mamet
Web Developer
embarc LLC
Matthew Mamet