SQL Server to Access Data Transfer

  • 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

    http://www.embarcgroup.com



    Matthew Mamet

  • 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?

  • 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

    http://www.embarcgroup.com



    Matthew Mamet

  • Try replacing ALL "#" by "[0-9]"

    ex:

    "###" ---> "[0-9][0-9][0-9]"


    * Noel

  • 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@yahoo.com


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • 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.

  • 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