Results 1 to 4 of 4
  1. #1
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78

    is there any option like "set waning false" for running append queries in sql server

    in my access database there are more than of 20 append queries and source data comes from of a excel file, before migration to sql i import this excel file to my database with a macro and inserting records in a big table in ms access and from this access table i appended records from different fields to other tables with defining (set warning false) for the macro.

    now after migration to sql when i run the macro for running these append queries i got alot of error like "you can not set null value...." and error "primary key could not be duplicate" , ....



    i have tried to correct these queries with using NOT IN (SELECT.....) in the append queries but i still got error about these append queries and i can't manage the errors and i have doubt when i try to import a excel file

    before in ms access i did not have these errors if it had, ms access continues job until finished appending records and for duplicate or null value does not terminate the program.

    is there any option in sql server when running append queries does not terminate the execution for the null value or duplicate primary key like what ms access does in the macro "set warning false"

  2. #2
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, it's not because you don't see warnings that the import would continue, I always prefer to be warned when the action fails. The primary key is there to insure the data integrity of your database and will always stop any actions that would insert doubles or empty values. I would advise to import the excel data in a temporary table without any keys or constraints and than work the data so they correspond to the demands of your database structure in SQL server.
    If you don't like the warnings you get, I would advise to create a procedure in the SQL database using the try/catch syntax to trap the errors and use these to import the data.

  3. #3
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78
    Thanks, what i see in ms access when there is duplicate key value or null just shows warning and i can continue action queries but in sql server shows error and interrupts action query.
    I liked to know is it possible continue action queries in sql server withot interuppting and showing error for duplicate key value and null value.

    If it is possible just with stored procedure
    please explain or send a link how to execute stored procedure from Ms Access

  4. #4
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I think the easiest solution for you would be to create a SSIS package that pulls the data into SQL server directly from Access (pull instead of push). You can start that package from a SQL job if you want.

    https://docs.microsoft.com/en-us/sql...ver-data-tools

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-28-2017, 12:29 PM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 4
    Last Post: 06-18-2015, 09:37 PM
  4. oie.document.all.item("AHHHH").value = False
    By redbull in forum Programming
    Replies: 4
    Last Post: 12-13-2012, 01:54 PM
  5. SELECT "False" if any records are false?
    By Azurewrath in forum Queries
    Replies: 6
    Last Post: 12-21-2011, 03:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums