Re: upsizing to sql 2005

Update only the data portion. In your Access application, as an-mdb, not as
an adp, then link to the tables in the MS SQL Server (using the same name
you were using in your Jet queries and elsewhere in your application) and
your application should continue to work (with its Jet-queries, on linked
table).


You then have time to migrate manually your queries, if required, since your
application and the data, in the database, are working fine.


Hoping it may help,
Vanderghast, Access MVP


"Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:87296E29-7DFA-4B6E-B639-FC66A8C1D8C7@xxxxxxxxxxxxxxxx
Michel,

I am not sure how to fix the rest. We are currently using the access
database on the yahoo webserver and it gets worked on all day and updated
several times a day on the server.

If you or some one could explain to me how to fix this I will have the web
content people start making their changes, while I make the sql changes.
I
just do not know what to fix and how to fix it.

Thanks
Dee

"Michel Walsh" wrote:

Maybe all what could be done automatically was done.

Vanderghast, Access MVP

"Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E34AACCD-61BD-497C-87B4-7C299B49972B@xxxxxxxxxxxxxxxx
What I do not understand then, is why did these work and it stoped
there:

Here is an example that is in the earlier part of the upsizing:


Query Name: Arcadia - ARC
Upsized using SQL:
CREATE FUNCTION "Arcadia - ARC" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT StoreData.*
FROM StoreData
WHERE (((StoreData.id) Like '%-ARC'))
ORDER BY StoreData.id)
Query Name: Arcadia - FLW
Upsized using SQL:
CREATE FUNCTION "Arcadia - FLW" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT FabricsData.*
FROM FabricsData
WHERE (((FabricsData.id) Like '%-ARC'))
ORDER BY FabricsData.id)
Query Name: Arcadia - Sections
Upsized using SQL:
CREATE FUNCTION "Arcadia - Sections" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT SectionData.*
FROM SectionData
WHERE (((SectionData.id) Like '%-ARC'))
ORDER BY SectionData.id)

What is the difference here?

Thanks
Dee
"Michel Walsh" wrote:

The Cross queries (crosstab queries, you mean) are not upsizeable to
MS
SQL
Server. While SQL from MS SQL Server 2005 has some PIVOTing
capabilities,
it
cannot match the capabilities of Jet, such as not having an IN clause
in
the
PIVOT statement. You will have to re-write all those.


For queries like:

Query Name: Children's Factory - CHF
This query is not upsizeable


Since, it seems, we don't have the JET-SQL statement, it is hard to
tell
why
it cannot be upsized to MS SQL Server - SQL. *IF* the query is NOT a
crosstab, the most common kind of problem is that the query uses a VBA
function. It may also use SQL specific to JET. Edit each of these
queries
within JET, then, assuming you have some high-end managing tool at
your
disposition for MS SQL Server (other than Access), try to re-write the
query
in MS SQL Server. The tool I personally use is the MS SQL Server
Analyzer,
which is a kind of "debug immediate window", but for SQL statements.
If
you
are still unable to upsize one of these queries, you can always ask
here,
query by query, if someone got an idea.



Hoping it may help,
Vanderghast, Access MVP


"Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1C40AE07-CFE5-43C8-99C0-1877F13253FD@xxxxxxxxxxxxxxxx
Michel,

I ran this again and it looks like everything updated, but when I
look
at
the adp file it stoped at the same point every time.

Here is an idea of what I am getting:

Upsizing Wizard Report
1Database
Microsoft Access Database: C:\database\BizChair-2.mdb
SQL Server Database: BizChair-2SQL
Upsizing Parameters
Table Attributes to Export
Indexes Table relationships:
Validation rules Upsized using DRI
Defaults
Structure only, no Timestamp fields added:
No tables
Modifications to Existing Database
Attach newly created Save password and
SQL Server tables user ID with attached
tables
Client/Server Modifications
Create a new Access Save password and
client/server user ID with
application. application


then this happens:


Query Name: Chicago Textile - CT
Upsized using SQL:
CREATE FUNCTION "Chicago Textile - CT" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT StoreData.*
FROM StoreData
WHERE (((StoreData.id) Like '%-CT'))
ORDER BY StoreData.id)
Query Name: Chicago Textile - FLW
Upsized using SQL:
CREATE FUNCTION "Chicago Textile - FLW" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT FabricsData.*
FROM FabricsData
WHERE (((FabricsData.id) Like '%-CT'))
ORDER BY FabricsData.id)
Query Name: Chicago Textile - Sections
Upsized using SQL:
CREATE FUNCTION "Chicago Textile - Sections" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT SectionData.*
FROM SectionData
WHERE (((SectionData.id) Like '%-CT'))
ORDER BY SectionData.id)
Query Name: ChildBrite - FLW
Upsized using SQL:
CREATE FUNCTION "ChildBrite - FLW" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT FabricsData.*
FROM FabricsData
WHERE (((FabricsData.id) Like '%-CB'))
ORDER BY FabricsData.id)
Query Name: ChildBrite - CB
Upsized using SQL:
CREATE FUNCTION "ChildBrite - CB" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT StoreData.*
FROM StoreData
WHERE (((StoreData.id) Like '%-CB'))
ORDER BY StoreData.id)
- 40 - 18-Oct-07

Query Name: Children's Factory - CHF
This query is not upsizeable
CREATE FUNCTION "ChildBrite - CB" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT StoreData.*
FROM StoreData
WHERE (((StoreData.id) Like '%-CB'))
ORDER BY StoreData.id)
Query Name: Choice Furnishings - FLW
This query is not upsizeable
CREATE FUNCTION "ChildBrite - CB" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT StoreData.*
FROM StoreData
WHERE (((StoreData.id) Like '%-CB'))
ORDER BY StoreData.id)
Query Name: Choice Furnishings Inc - CFI
This query is not upsizeable
CREATE FUNCTION "ChildBrite - CB" ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT StoreData.*
FROM StoreData
WHERE (((StoreData.id) Like '%-CB'))
ORDER BY StoreData.id)

It continues and never populates the adp after that, but it seems
data
went
into the sql server because the records for all tables are the same
for
access and sql.

I am using alot of queries and cross queries. Do not know what to
fix
this
problem.

Thanks
Dee



"Michel Walsh" wrote:

If the upsizer cannot convert a Jet-query to an MS SQL-query, you
have
to
do
it by hand, in the end of the process. As example, if your query
uses
a
VBA
function, like Nz, then you have to change it for COALESCE; etc.
It
is a
case-by-case job. If you have a, one, but specific problem at that
step,
you
can use the newsgroup to ask what can be done ! for a very
*specific*
problem, that is.

So, do the most that can be done, automatically, and in the end,
add
the
required 'manual' touch.



Vanderghast, Access MVP


"Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:27B12D63-6E4E-48D4-A4F4-FCE26962A061@xxxxxxxxxxxxxxxx
How can I fix thist kind of problems? I am running into problem
where
it
stops at the same query every time and I do not know what the
problem
is.
Is
there any documentation I can view that would tell me what to
look
for?
Is
there anything I need to be running that would check the database
before
upsizing it?

Thanks
Dee


"Michel Walsh" wrote:

As far as the data is concerned, there should be no problem as
long
as
the
table design is similar.

So, if your actual data is coherent under Jet, it will also be
coherent
under MS SQL Server 2005, most probably.


A notable exception is that Jet allows multiple NULL under an
index
not
allowing duplicated values, while MS SQL Server 2005 only allows
one
row
with a NULL under the same design.


That does not mean that all your queries will be fine, though,
due
to
many
difference between Jet and MS SQL Server... In fact, it is
highly
probable
that some queries won't be upgraded automatically (such as those
implying
a
crosstab, among others), or those using VBA functions.



Vanderghast, Access MVP


"Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:667EB9AC-8E7D-4AD2-9867-6787BE42C955@xxxxxxxxxxxxxxxx
I am using the upsizing tool to slq 2005 from access 2003. I
have
three
tables which use 100's of queries. Some if the primary keys
to
the
three
tables may be the same. For example I have a storedata table,
a
fabric
table and a section table. In the storedata table the fabrics
that
go
to
some of the queires will have the same extention like -dd and
the
same
for
sections which are arms, headrest, etc.

Can I upsize one store at a time? How can I fix this problem?

Thanks
Dee






.

Relevant Pages

  • Re: upsizing to sql 2005
    ... another mdb file, but MS SQL Server. ... Query Name: Arcadia - ARC ... are still unable to upsize one of these queries, ...
    (microsoft.public.access.queries)
  • Re: upsizing to sql 2005
    ... I would not really bother with the PIVOT clause of MS SQL Server 2005. ... Query Name: Arcadia - ARC ... are still unable to upsize one of these queries, ...
    (microsoft.public.access.queries)
  • Re: ntext getting truncated
    ... Tibor Karaszi, SQL Server MVP ... I applied the query as follows: ... select counthowmany, datalengthntextlength ...
    (microsoft.public.sqlserver.programming)
  • Re: upsizing to sql 2005
    ... which include MS SQL Server linked table. ... Query Name: Arcadia - ARC ... are still unable to upsize one of these queries, ...
    (microsoft.public.access.queries)
  • Re: VS.NET 2003, ADO.NET 1.1 and Access 2000: Getting a Concurrency violation
    ... I am using an Access update query, with parameters, for the ... Then my app goes against a SQL ... OleDbDataAdapter that I use to retrieve the original table from the ... So your named parameters for SQL Server suddenly become anonymous ...
    (microsoft.public.dotnet.framework.adonet)
Loading