Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Import/Export to SQL Server 2000 Problem

Discussion in 'Information Technology' started by Frank, Jul 28, 2006.

  1. Frank

    Frank
    Expand Collapse
    Guest

    Hi All,

    I am attempting to import into SQL Server 2000 a table which results from a
    query in an Access 97 db.

    I know, I know...an Access 97 db...but that is what is being used right now
    and there are a number of progs accessing this db, so the boss is reluctant
    to change over.

    If I open the Access db, browse to the query and double click it, it work no
    problem. I produces a view with the pertinent data.

    When I open Sql Server, and usethe DTS Import/Export Wizard, I have tried
    two approaches.

    The first was to simply select 'Copy table(s) and View(s) from the source
    database, then select the query only, but before I compplete the wizard I
    get:

    Error Source: Microsoft JET DatabaseEngine
    Error Description: Data mismatch in criteria expression
    Context: Error calling OpenRowset on the provider


    The second way I tried was to check 'Use a query to specify the data to
    transfer' and then simply paste in the sql statement from the query field.
    If I click the 'Parse' button, I get 'SQL statement is valid".
    But when I try to run it I get 'Failed to copy 1 table...' If I click on
    the error msg, it says 'Data type mismatch in criteria expression'.

    The query itself is complicated. It uses other query results and goes about
    tree queries deep. But as I mentioned in the beginning, it does run
    successfully in Access when double clicking it.




    The sql statement in the query is as follows:

    SELECT DISTINCT
    CustomerLogin.CTA,
    CustomerLogin.EntryStation,
    CustomerLogin.IB,
    CustomerLogin.broker,
    CustomerPosition2.Account,
    CustomerPosition2.Name,
    IIf([customerposition2].[imargin] Is
    Null,"0",[customerposition2].[imargin]) AS IMargin,
    IIf([customerposition2].[ima] Is Null,"0",[customerposition2].[ima]) AS
    IMargina,
    IIf([customerposition2].[imssf] Is Null,"0",[customerposition2].[imssf])
    AS IMarginssf,
    CustomerPosition2.PL,
    IIf([QUAN] Is Not
    Null,Val([sc])+Val([profit])+Val([sumofsd])-([quan]*5),Val([sc])+Val([profit])+Val([sumofsd]))
    AS C,
    CustomerPosition2.C AS SC, [C]+[PL] AS LV, [LV]-[IMargin] AS ME,
    IIf([AVGOfProfit] Is Null,0,[AVGOfProfit]) AS Profit,
    CustomerPosition2.sumofsd,
    CustomerPosition2.SumOfValue, [profit]+[pl] AS NetPL,
    CustomerLogin.Level,
    RestingOrders.QLimit,
    RestingOrders.CountOfOrder
    FROM ((CustomerLogin
    LEFT JOIN CustomerPosition2 ON CustomerLogin.Account =
    CustomerPosition2.Account)
    LEFT JOIN Commission ON CustomerLogin.Account = Commission.email)
    LEFT JOIN RestingOrders ON CustomerLogin.Account = RestingOrders.Account;

    Any suggestions?

    Thanks in advance for any help
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005

  3. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    "Frank" <fkaesser@pfmail.com> wrote

    > I know, I know...an Access 97 db...but that
    > is what is being used right now and . . .


    What is it that you doubly "know?" There's nothing wrong with Access 97 --
    many think it was the best, most stable 32-bit Access version. It _is_ long
    out of support, but didn't have a lot of major problems lingering when they
    did stop support. I haven't done anything with it in some time, but still
    have it around, because there are clients in this area who still use it, and
    are happy with it, and might need some assistance.

    Talk with your SQL Server DBA, and get a database created for you in SQL
    Server 2000 with a table holding the same Fields. Link that DB from your
    Access database, and use an Append Query to add your records to it. If it is
    a matter of "permissions", get your manager to talk to their managers.

    Larry Linson
    Microsoft Access MVP



    > If I open the Access db, browse to the query and double click it, it work
    > no problem. I produces a view with the pertinent data.
    >
    > When I open Sql Server, and usethe DTS Import/Export Wizard, I have tried
    > two approaches.
    >
    > The first was to simply select 'Copy table(s) and View(s) from the source
    > database, then select the query only, but before I compplete the wizard I
    > get:
    >
    > Error Source: Microsoft JET DatabaseEngine
    > Error Description: Data mismatch in criteria expression
    > Context: Error calling OpenRowset on the provider
    >
    >
    > The second way I tried was to check 'Use a query to specify the data to
    > transfer' and then simply paste in the sql statement from the query field.
    > If I click the 'Parse' button, I get 'SQL statement is valid".
    > But when I try to run it I get 'Failed to copy 1 table...' If I click on
    > the error msg, it says 'Data type mismatch in criteria expression'.
    >
    > The query itself is complicated. It uses other query results and goes
    > about tree queries deep. But as I mentioned in the beginning, it does run
    > successfully in Access when double clicking it.
    >
    >
    >
    >
    > The sql statement in the query is as follows:
    >
    > SELECT DISTINCT
    > CustomerLogin.CTA,
    > CustomerLogin.EntryStation,
    > CustomerLogin.IB,
    > CustomerLogin.broker,
    > CustomerPosition2.Account,
    > CustomerPosition2.Name,
    > IIf([customerposition2].[imargin] Is
    > Null,"0",[customerposition2].[imargin]) AS IMargin,
    > IIf([customerposition2].[ima] Is Null,"0",[customerposition2].[ima]) AS
    > IMargina,
    > IIf([customerposition2].[imssf] Is
    > Null,"0",[customerposition2].[imssf]) AS IMarginssf,
    > CustomerPosition2.PL,
    > IIf([QUAN] Is Not
    > Null,Val([sc])+Val([profit])+Val([sumofsd])-([quan]*5),Val([sc])+Val([profit])+Val([sumofsd]))
    > AS C,
    > CustomerPosition2.C AS SC, [C]+[PL] AS LV, [LV]-[IMargin] AS ME,
    > IIf([AVGOfProfit] Is Null,0,[AVGOfProfit]) AS Profit,
    > CustomerPosition2.sumofsd,
    > CustomerPosition2.SumOfValue, [profit]+[pl] AS NetPL,
    > CustomerLogin.Level,
    > RestingOrders.QLimit,
    > RestingOrders.CountOfOrder
    > FROM ((CustomerLogin
    > LEFT JOIN CustomerPosition2 ON CustomerLogin.Account =
    > CustomerPosition2.Account)
    > LEFT JOIN Commission ON CustomerLogin.Account = Commission.email)
    > LEFT JOIN RestingOrders ON CustomerLogin.Account = RestingOrders.Account;
    >
    > Any suggestions?
    >
    > Thanks in advance for any help
    >
    >
    >
    >
    >
     
  4. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    If is is a one off job then
    1) Copy the query
    2) Change the copy to a Make Table query
    3) Run the copy to create a table
    4) Import/Export the resulting table to SQL Server.

    If it's something you need to do regularly
    5) Delete the copy
    6) Create a DTS package in SQL which
    a) Clears the SQL table
    b) Imports to the SQL table the results from the Access table
    7) Create a VBA proc which
    a) Deletes the records in the table
    b) Appends the results from your query to the table
    c) Runs the DTS package


    --

    Terry Kreft


    "Frank" <fkaesser@pfmail.com> wrote in message
    news:ehoeqqbiGHA.3496@TK2MSFTNGP02.phx.gbl...
    > Hi All,
    >
    > I am attempting to import into SQL Server 2000 a table which results from

    a
    > query in an Access 97 db.
    >
    > I know, I know...an Access 97 db...but that is what is being used right

    now
    > and there are a number of progs accessing this db, so the boss is

    reluctant
    > to change over.
    >
    > If I open the Access db, browse to the query and double click it, it work

    no
    > problem. I produces a view with the pertinent data.
    >
    > When I open Sql Server, and usethe DTS Import/Export Wizard, I have tried
    > two approaches.
    >
    > The first was to simply select 'Copy table(s) and View(s) from the source
    > database, then select the query only, but before I compplete the wizard I
    > get:
    >
    > Error Source: Microsoft JET DatabaseEngine
    > Error Description: Data mismatch in criteria expression
    > Context: Error calling OpenRowset on the provider
    >
    >
    > The second way I tried was to check 'Use a query to specify the data to
    > transfer' and then simply paste in the sql statement from the query field.
    > If I click the 'Parse' button, I get 'SQL statement is valid".
    > But when I try to run it I get 'Failed to copy 1 table...' If I click on
    > the error msg, it says 'Data type mismatch in criteria expression'.
    >
    > The query itself is complicated. It uses other query results and goes

    about
    > tree queries deep. But as I mentioned in the beginning, it does run
    > successfully in Access when double clicking it.
    >
    >
    >
    >
    > The sql statement in the query is as follows:
    >
    > SELECT DISTINCT
    > CustomerLogin.CTA,
    > CustomerLogin.EntryStation,
    > CustomerLogin.IB,
    > CustomerLogin.broker,
    > CustomerPosition2.Account,
    > CustomerPosition2.Name,
    > IIf([customerposition2].[imargin] Is
    > Null,"0",[customerposition2].[imargin]) AS IMargin,
    > IIf([customerposition2].[ima] Is Null,"0",[customerposition2].[ima])

    AS
    > IMargina,
    > IIf([customerposition2].[imssf] Is

    Null,"0",[customerposition2].[imssf])
    > AS IMarginssf,
    > CustomerPosition2.PL,
    > IIf([QUAN] Is Not
    >

    Null,Val([sc])+Val([profit])+Val([sumofsd])-([quan]*5),Val([sc])+Val([profit
    ])+Val([sumofsd]))
    > AS C,
    > CustomerPosition2.C AS SC, [C]+[PL] AS LV, [LV]-[IMargin] AS ME,
    > IIf([AVGOfProfit] Is Null,0,[AVGOfProfit]) AS Profit,
    > CustomerPosition2.sumofsd,
    > CustomerPosition2.SumOfValue, [profit]+[pl] AS NetPL,
    > CustomerLogin.Level,
    > RestingOrders.QLimit,
    > RestingOrders.CountOfOrder
    > FROM ((CustomerLogin
    > LEFT JOIN CustomerPosition2 ON CustomerLogin.Account =
    > CustomerPosition2.Account)
    > LEFT JOIN Commission ON CustomerLogin.Account = Commission.email)
    > LEFT JOIN RestingOrders ON CustomerLogin.Account = RestingOrders.Account;
    >
    > Any suggestions?
    >
    > Thanks in advance for any help
    >
    >
    >
    >
    >
     

Share This Page