Import/Export to SQL Server 2000 Problem

[Frank]

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

 

[Larry Linson]

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

 

[Terry Kreft]

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

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