Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: Access and SQL Server won't play nice

    I have a rather wide table in Access.
    I created the same table definition in SQL Server.

    I am trying to use an update query to move the data from Access to the linked SQL table but I am getting mysterious errors.

    It will append some records, but on other records (most of them) it will tell me either I have a data type conversion error, or I have a key violation.

    As for the first error, I am using a query with data type conversion functions to exactly match SQL server (I went through the SQL statement about 50 times). In addition, all my field are set to allow null values.

    As for the second error, there are NO KEYS defined in the SQL table, so there is not key to violate.


    For the SQL Date fields, I have a force date function that drops the time.
    For the SQL Bit fields, I am using (-1)*Nz([Field],0)
    For the SQL NVarChar fields I am using CStr(Nz([Field],""))
    For the SQL Int fields, I am using CInt(Nz([Field],0))


    The access query processes just fine, but then SQL rejects. I compared records that will append to records that won't and there is no discernible difference in the data. In the "rejected data" all the fields are the correct data type. The rejected records even look like accepted records.


    I am ready to drop kick my PC, except I know the problem is on the server ...

    Any ideas?
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You'll need to post the table definitions (with datatypes, constraints, and keys) for both tables in order for us to help you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I was getting ready to do that. But, I opted to go home, drink a couple beers and relax. Upon returning this morning, a careful re-examination reveals some sort of quirks working between Access and SQL Server.

    I revised a couple of things in Access, dropped and recreated a few things in SQL Server and then refreshed all of my links in Access and it now works.

    I'm kinda worried that I don;t know why it wasn't working before, but the need for progress over-rides my option of further investigation.

    I will see what I can muster up below.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    SQL Server table definition:
    Code:
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Ups_tblQuotes](
    	[QuoteKey] [int] NULL,
    	[QuoteNumber] [int] NULL,
    	[QuoteRev] [int] NULL,
    	[Customer] [int] NULL,
    	[Builder] [int] NULL,
    	[Residence] [int] NULL,
    	[NetworkLocation] [nvarchar](100) NULL,
    	[QuoteRequestDate] [date] NULL,
    	[QuoteRequestedBy] [int] NULL,
    	[QuoteStarted] [bit] NULL,
    	[QuoteStartedDate] [date] NULL,
    	[QuoteInsideSalesRep] [int] NULL,
    	[QuoteReady] [bit] NULL,
    	[QuoteReadyDate] [date] NULL,
    	[QuoteChecked] [bit] NULL,
    	[QuoteCheckedDate] [date] NULL,
    	[QuoteSent] [bit] NULL,
    	[QuoteSentDate] [date] NULL,
    	[QuoteAccepted] [bit] NULL,
    	[QuoteAcceptDate] [date] NULL,
    	[QuoteDrawings] [bit] NULL,
    	[QuoteDrawingsDate] [date] NULL,
    	[QuoteDrawingsSent] [bit] NULL,
    	[QuoteDrawingsSentDate] [date] NULL,
    	[QuoteDrawingsSigned] [bit] NULL,
    	[QuoteDrawingsSignedDate] [date] NULL,
    	[QuoteAmount] [money] NULL,
    	[QuoteFreight] [money] NULL,
    	[QuoteTax] [money] NULL,
    	[QuoteComment] [nvarchar](max) NULL,
    	[QuotePotential] [int] NULL,
    	[XLS_CustBuildRes] [nvarchar](256) NULL,
    	[XLS_SalesInsideComp] [nvarchar](256) NULL,
    	[QuoteOpen] [bit] NULL,
    	[StatusCode] [int] NULL,
    	[QuotePath] [nvarchar](255) NULL,
    	[QuoteCloseReason] [int] NULL,
    	[QuoteLostTo] [int] NULL,
    	[QuotePriceDelta] [int] NULL,
    	[QuoteCloseReasonText] [nvarchar](max) NULL,
    	[quoteName] [nvarchar](100) NULL,
    	[OrderNumber] [int] NULL,
    	[OrderConverted] [bit] NULL,
    	[OrderFollowUp] [bit] NULL,
    	[OrderFollowUpDate] [date] NULL,
    	[OrderEstHours] [int] NULL,
    	[OrderTargetDate] [date] NULL,
    	[OrderDepositRequested] [money] NULL,
    	[OrderDepositRequestedDate] [date] NULL,
    	[OrderDepositReceived] [bit] NULL,
    	[OrderDepositReceivedDate] [date] NULL,
    	[OrderHasMolding] [bit] NULL,
    	[OrderDoorDesc] [nvarchar](100) NULL,
    	[OrderNotes] [nvarchar](160) NULL,
    	[OrderShipTo] [int] NULL,
    	[OrderStaveCore] [int] NULL,
    	[OrderEBorV] [nvarchar](25) NULL,
    	[OrderPanelQty] [int] NULL,
    	[OrderHasGlass] [bit] NULL,
    	[OrderMoveFlagged] [bit] NULL,
    	[OrderMoved] [bit] NULL,
    	[OrderDoorQty] [int] NULL,
    	[OrderLeadTime] [int] NULL,
    	[OrderendLeadtime] [date] NULL,
    	[OrderEstHrs] [int] NULL,
    	[OrderProcessedDate] [date] NULL,
    	[DrawingAssignedTo] [int] NULL,
    	[DrawingStarted] [bit] NULL,
    	[DrawingRevReq] [bit] NULL,
    	[SunDor] [bit] NULL,
    	[BillToKey] [nvarchar](20) NULL,
    	[ShipToKey] [nvarchar](30) NULL,
    	[ContactKey] [nvarchar](40) NULL,
    	[BillToCustID] [int] NULL,
    	[BillToProspID] [int] NULL,
    	[ShipToID] [int] NULL,
    	[ContactID] [int] NULL,
    	[ShipToCustID] [int] NULL,
    	[ShipToProspID] [int] NULL
    ) ON [PRIMARY]
    
    GO
    Access "Append Query":
    Code:
    INSERT INTO dbo_Ups_tblQuotes ( 
    QuoteKey, 
    QuoteNumber, 
    QuoteRev, 
    Customer, 
    Builder, 
    Residence, 
    NetworkLocation, 
    QuoteRequestDate, 
    QuoteRequestedBy, 
    QuoteStarted, 
    QuoteStartedDate, 
    QuoteInsideSalesRep, 
    QuoteReady, 
    QuoteReadyDate, 
    QuoteChecked, 
    QuoteCheckedDate, 
    QuoteSent, 
    QuoteSentDate, 
    QuoteAccepted, 
    QuoteAcceptDate, 
    QuoteDrawings, 
    QuoteDrawingsDate, 
    QuoteDrawingsSent, 
    QuoteDrawingsSentDate, 
    QuoteDrawingsSigned, 
    QuoteDrawingsSignedDate, 
    QuoteAmount, 
    QuoteFreight, 
    QuoteTax, 
    QuoteComment, 
    QuotePotential, 
    XLS_CustBuildRes, 
    XLS_SalesInsideComp, 
    QuoteOpen, 
    StatusCode, 
    QuotePath, 
    QuoteCloseReason, 
    QuoteLostTo, 
    QuotePriceDelta, 
    QuoteCloseReasonText, 
    quoteName, 
    OrderNumber, 
    OrderConverted, 
    OrderFollowUp, 
    OrderFollowUpDate, 
    OrderEstHours, 
    OrderTargetDate, 
    OrderDepositRequested, 
    OrderDepositRequestedDate, 
    OrderDepositReceived, 
    OrderDepositReceivedDate, 
    OrderHasMolding, 
    OrderDoorDesc, 
    OrderNotes, 
    OrderShipTo, 
    OrderStaveCore, 
    OrderEBorV, 
    OrderPanelQty, 
    OrderHasGlass, 
    OrderMoveFlagged, 
    OrderMoved, 
    OrderDoorQty, 
    OrderLeadtime, 
    OrderendLeadTime, 
    OrderEstHrs, 
    OrderProcessedDate, 
    DrawingAssignedTo, 
    DrawingStarted, 
    DrawingRevReq, 
    SunDor, 
    BillToKey, 
    ShipToKey, 
    ContactKey )
    SELECT 
    CInt(Nz([QuoteKey],0)) AS Expr22, 
    CInt(Nz([QuoteNumber],0)) AS Expr23, 
    CInt(Nz([QuoteRev],0)) AS Expr24, 
    CInt(Nz([Customer],0)) AS Expr25, 
    CInt(Nz([Builder],0)) AS Expr26, 
    CInt(Nz([Residence],0)) AS Expr27, 
    CStr(Nz([NetworkLocation],"")) AS Expr28, 
    fnForceDate([QuoteRequestDate]) AS Date1, 
    CInt(Nz([QuoteRequestedBy],0)) AS Expr29, 
    (-1)*Nz([QuoteStarted],0) AS Expr1, 
    fnForceDate([QuoteStartedDate]) AS Date2, 
    CInt(Nz([QuoteInsideSalesRep],0)) AS Expr30, 
    (-1)*Nz([QuoteReady],0) AS Expr2, 
    fnForceDate([QuoteReadyDate]) AS Date3, 
    (-1)*Nz([QuoteChecked],0) AS Expr3, 
    fnForceDate([QuoteCheckedDate]) AS Date4, 
    (-1)*Nz([QuoteSent],0) AS Expr4, 
    fnForceDate([QuoteSentDate]) AS Date5, 
    (-1)*Nz([QuoteAccepted],0) AS Expr5, 
    fnForceDate([QuoteAcceptDate]) AS Date6, 
    (-1)*Nz([QuoteDrawings],0) AS Expr6, 
    fnForceDate([QuoteDrawingsDate]) AS Date7, 
    (-1)*Nz([QuoteDrawingsSent],0) AS Expr7, 
    fnForceDate([QuoteDrawingsSentDate]) AS Date8, 
    (-1)*Nz([QuoteDrawingsSigned],0) AS Expr8, 
    fnForceDate([QuoteDrawingsSignedDate]) AS Date9, 
    CCur(Nz([QuoteAmount],0)) AS Expr31, 
    CCur(Nz([QuoteFreight],0)) AS Expr32, 
    CCur(Nz([QuoteTax],0)) AS Expr33, 
    CStr(Nz([QuoteComment],"")) AS Expr34, 
    CInt(Nz([QuotePotential],0)) AS Expr35, 
    CStr(Nz([XLS_CustBuildRes],"")) AS xls, 
    CStr(Nz([XLS_SalesInsideComp],"")) AS salesComp, 
    (-1)*Nz([QuoteOpen],0) AS Expr9, 
    CInt(Nz([StatusCode],0)) AS Expr36, 
    CStr(Nz([QuotePath],"")) AS Expr37, 
    CInt(Nz([QuoteCloseReason],0)) AS Expr38, 
    CInt(Nz([QuoteLostTo],0)) AS Expr39, 
    CInt(Nz([QuotePriceDelta],0)) AS Expr40, 
    CStr(Nz([QuoteCloseReasonText],"")) AS Expr41, 
    CStr(Nz([quoteName],"")) AS Expr42, 
    CInt(Nz([OrderNumber],0)) AS Expr43, 
    (-1)*Nz([OrderConverted],0) AS Expr10, 
    (-1)*Nz([OrderFollowUp],0) AS Expr11, 
    fnForceDate([OrderFollowUpDate]) AS Date10, 
    CInt(Nz([OrderEstHours],0)) AS Expr44, 
    fnForceDate([OrderTargetDate]) AS Date11, 
    CCur(Nz([OrderDepositRequested],0)) AS Expr45, 
    fnForceDate([OrderDepositRequestedDate]) AS Date12, 
    (-1)*Nz([OrderDepositReceived],0) AS Expr12, 
    fnForceDate([OrderDepositReceivedDate]) AS Date13, 
    (-1)*Nz([OrderHasMolding],0) AS Expr13, 
    CStr(Nz([OrderDoorDesc],"")) AS Expr46, 
    CStr(Nz([OrderNotes],"")) AS Expr47, 
    CInt(Nz([OrderShipTo],0)) AS Expr48, 
    CInt(Nz([OrderStaveCore],0)) AS Expr49, 
    CStr(Nz([OrderEBorV],"")) AS Expr50, 
    CInt(Nz([OrderPanelQty],0)) AS Expr51, 
    (-1)*Nz([OrderHasGlass],0) AS Expr14, 
    (-1)*Nz([OrderMoveFlagged],0) AS Expr15, 
    (-1)*Nz([OrderMoved],0) AS Expr16, 
    CInt(Nz([OrderDoorQty],0)) AS Expr52, 
    CInt(Nz([OrderLeadtime],0)) AS Expr21, 
    fnForceDate([OrderendLeadTime]) AS Expr20, 
    CInt(Nz([OrderEstHrs],0)) AS Expr53, 
    fnForceDate([OrderProcessedDate]) AS Date14, 
    CInt(Nz([DrawingAssignedTo],0)) AS Expr54, 
    (-1)*Nz([DrawingStarted],0) AS Expr17, 
    (-1)*Nz([DrawingRevReq],0) AS Expr18, 
    (-1)*Nz([SunDor],0) AS Expr19, 
    CStr(Nz([BillToKey],"")) AS Expr55, 
    CStr(Nz([ShipToKey],"")) AS Expr56, 
    CStr(Nz([ContactKey],"")) AS Expr57
    FROM [tblQuotes-MDB]
    ORDER BY [tblQuotes-MDB].QuoteKey;
    Access Table definition:
    Code:
    QuoteKey, Long Integer, 4
    QuoteNumber, Long Integer, 4
    QuoteRev, Long Integer, 4
    Customer, Long Integer, 4
    Builder, Long Integer, 4
    Residence, Long Integer, 4
    NetworkLocation, Anchor, -
    QuoteRequestDate, Date/Time, 8
    QuoteRequestedBy, Long Integer, 4
    QuoteStarted, Yes/No, 1
    QuoteStartedDate, Date/Time, 8
    QuoteInsideSalesRep, Long Integer, 4
    QuoteReady, Yes/No, 1
    QuoteReadyDate, Date/Time, 8
    QuoteChecked, Yes/No, 1
    QuoteCheckedDate, Date/Time, 8
    QuoteSent, Yes/No, 1
    QuoteSentDate, Date/Time, 8
    QuoteAccepted, Yes/No, 1
    QuoteAcceptDate, Date/Time, 8
    QuoteDrawings, Yes/No, 1
    QuoteDrawingsDate, Date/Time, 8
    QuoteDrawingsSent, Yes/No, 1
    QuoteDrawingsSentDate, Date/Time, 8
    QuoteDrawingsSigned, Yes/No, 1
    QuoteDrawingsSignedDate, Date/Time, 8
    QuoteAmount, Currency, 8
    QuoteFreight, Currency, 8
    QuoteTax, Double, 8
    QuoteComment, Memo, -
    QuotePotential, Long Integer, 4
    XLS_CustBuildRes, Text, 255
    XLS_SalesInsideComp, Text, 255
    QuoteOpen, Yes/No, 1
    StatusCode, Long Integer, 4
    QuotePath, Text, 255
    QuoteCloseReason, Long Integer, 4
    QuoteLostTo, Long Integer, 4
    QuotePriceDelta, Long Integer, 4
    QuoteCloseReasonText, Memo, -
    quoteName, Text, 50
    OrderNumber, Long Integer, 4
    OrderConverted, Yes/No, 1
    OrderFollowUp, Yes/No, 1
    OrderFollowUpDate, Date/Time, 8
    OrderEstHours, Long Integer, 4
    OrderTargetDate, Date/Time, 8
    OrderDepositRequested, Currency, 8
    OrderDepositRequestedDate, Date/Time, 8
    OrderDepositReceived, Yes/No, 1
    OrderDepositReceivedDate, Date/Time, 8
    OrderHasMolding, Yes/No, 1
    OrderDoorDesc, Text, 50
    OrderNotes, Text, 150
    OrderShipTo, Long Integer, 4
    OrderStaveCore, Long Integer, 4
    OrderEBorV, Text, 255
    OrderPanelQty, Long Integer, 4
    OrderHasGlass, Yes/No, 1
    OrderMoved, Yes/No, 1
    OrderDoorQty, Long Integer, 4
    OrderLeadTime, Long Integer, 4
    OrderendLeadtime, Date/Time, 8
    OrderEstHrs, Long Integer, 4
    OrderProcessedDate, Date/Time, 8
    DrawingAssignedTo, Long Integer, 4
    DrawingStarted, Yes/No, 1
    DrawingRevReq, Yes/No, 1
    SunDor, Yes/No, 1
    BillToKey, Text, 15
    ShipToKey, Text, 15
    ContactKey, Text, 255
    Here's the function:
    Code:
    Public Function fnForceDate(TheDate As Variant) As Variant
    
        If IsNull(TheDate) Then
            fnForceDate = Null
        Else
            fnForceDate = CDate(Format(Nz([TheDate], "Jan 1, 1900"), "mm/dd/yyyy"))
        End If
        If fnForceDate < #1/1/2008# Then fnForceDate = Null
    End Function

  5. #5
    Join Date
    May 2011
    Posts
    1

    reply

    The issue you are experiencing is because you are either not mentioned right sql command or if despite of using right command if such issue is being occured then it could be sql table corruption isuse that trigger such strange error while you work with your sql data. Such corruption is possible to be solved using mysql repair software.

    For detailed help visit the webpage and get your query solved;-
    Repair MySQL | MySQL Repair Software | MySQL Repair Tool: MySQL Repair

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •