access upsize problem

I am trying to upsize an access 2000 application to MSDE. I get an error 14,
invalid connection. I have read sever al articles in the MS KB but have not
found out what to do. I have WinXP, Office Pro 2000. I am trying to put the
MSDE server on the same machine as the access front end. I would appreciate
reference to a 'cookbook' article about how to make the upsize work. Thanks
I'm not sure what you've read or haven't read, but there isn't a
straightforward approach to getting the upsizing wizard to work,
especially with Access 2000, which was released *before* SQLS 2000,
and thus has many incompatibilities, including different date ranges
for datetime data types. I'd recommend upgrading to Access 2003 if you
want things to work smoothly even after you move the data over. Access
and SQLS are completely different, have data type incompatibilities,
and radically different functionality at the engine level. A straight
port of an Access mdb rarely works well if the application is at all
complex, as most business apps are by definition. It's important to
understand at the outset that the two engines are NOT interchangeable,
and that it is going to take a certain amount of work on your part
(depending on the complexity of your app) to get it working correctly
on MSDE.
The upsizing wizard was written for the lowest common denominator to
handle as many cases as possible by translating all text and memo
fields as unicode, and all validation rules (at least, the ones it can
figure out) as triggers. In cases where there is no exact translation,
entire tables get skipped. I'd recommend creating the structure in SQL
Server and then loading the data, which you can do using DTS or even
Access queries (linking to the empty SQLS tables from the mdb).
--Mary
On Thu, 21 Oct 2004 15:37:23 GMT, "Hugh N. Ross"
<bytewise@.optonline.net> wrote:

>I am trying to upsize an access 2000 application to MSDE. I get an error 14,
>invalid connection. I have read sever al articles in the MS KB but have not
>found out what to do. I have WinXP, Office Pro 2000. I am trying to put the
>MSDE server on the same machine as the access front end. I would appreciate
>reference to a 'cookbook' article about how to make the upsize work. Thanks
>

Access to SQL Server Upsize Query Problem

Hi,

I have a query written in Jet-SQL which i need to convert to T-SQL as I am upgrading an Access DB to SQL Server 2000.

Query is:
SELECT WDF_TBLSKILL.RECID,WDF_TBLSKILL.TECHNOLOGY,Iif(EXI STS(SELECT * FROM WDF_TBLCOMPANY_TBLSKILL_LNK WHERE SKILLID=WDF_TBLSKILL.RECID AND COMPANYID=857),1,0) AS CHOSENSKILL FROM WDF_TBLSKILL ORDER BY WDF_TBLSKILL.TECHNOLOGY ASC;

It is falling over on the 3rd select item (The Iif statement) which I want to return a 1 if any records are found in the subquery or 0 if they aren't.

I know Iif is not supported in SQL but can't find anything that seems to do the job.

Please help!!!SELECT WDF_TBLSKILL.RECID
, WDF_TBLSKILL.TECHNOLOGY
, case when EXISTS
(SELECT * FROM WDF_TBLCOMPANY_TBLSKILL_LNK
WHERE SKILLID=WDF_TBLSKILL.RECID
AND COMPANYID=857) then 1 else 0 end AS CHOSENSKILL
FROM WDF_TBLSKILL
ORDER BY WDF_TBLSKILL.TECHNOLOGY ASC|||thanks mate - much appreciated. you've saved me a lot of time and effort.

Access Linked Tables and SQL Server.

Hi,

i want to upsize Access Databases to SQL Server 2000.
I have two databases. Let A and B. A contains a table,
linked to table t of B.
The upsize wizard of Access creates for the A's linked table
a table that contains the data of table t at the moment of the
upsizing.
These data are static data and don't change when i modify the
data of table t.
What i have to do to fix it?

Thanks in advance.RE: Hi, i want to upsize Access Databases to SQL Server 2000. I have two databases. Let A and B. A contains a table,
linked to table t of B. The upsize wizard of Access creates for the A's linked table a table that contains the data of table t at the moment of the upsizing. These data are static data and don't change when i modify the data of table t.

Q1 What i have to do to fix it? Thanks in advance.

A1 Rework your tsql code to refer to the table in DB B.

Brief Explanation:

It is possible to use a fully qualified name to refer to the table in DB B, (when working with DB A). For Example: (run in "Query Analyzer", isql, osql, etc.)

Use A
GO

Select * From B..t
Go

[The example would return a full result set from Table t (in the B DB) even though the current DB context is DB A (Use A sets the context to DB A).]