December 22, 2003 at 8:26 am
I'm working on a project for a client that involves writing data from SQL Server to Access DB. I've added the Access DB as a linked server to our SQL Server and am able to successfully write stored procs that read/write from the Access tables.
The problem is, that some of the fields in the Access DB have Validation Rules and Input masks setup. For instance, there is an email field with a validation of *@*.*
The * wildcard is not recognized by SQL server and is consequently throwing an error on valid email addresses.
Is there anyway to tell Access to ignore those validations without removing them? The Access DB strucutre is not in my control . . .
Also, we've discounted using SQL Server Replication for this project, so the question remains - how to get around Access DB validation rules when inserting data into Access using a SQL Server 2000 Stored proc?
Thanks!
Sincerely,
Matthew Mamet
Web Developer
embarc LLC
Matthew Mamet
December 22, 2003 at 8:54 am
quote:
The problem is, that some of the fields in the Access DB have Validation Rules and Input masks setup. For instance, there is an email field with a validation of *@*.*The * wildcard is not recognized by SQL server and is consequently throwing an error on valid email addresses.
Have you created check constraint in your table in SQL Serevr to ensure the emaill address entered into SQL Server is valid before it goes to Access database?
December 22, 2003 at 9:48 am
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
December 22, 2003 at 10:25 am
Try replacing ALL "#" by "[0-9]"
ex:
"###" ---> "[0-9][0-9][0-9]"
* Noel
December 23, 2003 at 2:35 am
I have another solution for you .. to transfer data and Structure from SQL Server to Access:
open Access
from "file" menu, choose "Get External Data" , then "Import"
then in "Files of type" , choose "ODBC DataBases"
then choose DSN name that is connected to your SQL Server .. or make a new DSN name to your SQL Server
then press "OK"
then choose the tables you want and click "OK"
and table will be transfered with data and structure
Note:
the relationship doesn't tranfsered .. you have to make it manually in access again
Note:
you can use this way to get the table you want then update your empty new structure tables from it
I hope this help you
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
December 23, 2003 at 7:16 am
When working with Access and SQL Server, we normally use Access as a sort of Front-End and SQL Server as the data store. Try Alamir's suggestion and use ODBC to connect to SQL Server and then do your development in Access. You can still get at stored procs in SQL Server by using a Pass-Thru query in Access and calling the stored proc in SQL Server.
December 23, 2003 at 7:41 am
why not just use an access append query to add the data?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply