Re: upsizing to sql 2005

When I speak of web content, these are the people who put the information in
the access database. Our web site is currently hosted on yahoo. The access
database is hugh and we are also upsizing for security reason.

So what I mean was the the people who input the information into the
database can still do this from access and it will populate to sql is that
correct?

Thank you
Dee

"Michel Walsh" wrote:

I have no idea about the web content, neither how it is implemented in your
case.


Vanderghast, Access MVP

"Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:33959FB8-16B9-47F3-A393-E189BCA0FC03@xxxxxxxxxxxxxxxx
Thank Michel.

I had tried that and it seems to work with no errors. In this manner the
web content people will not have to learn anything different is that so?

Again Thank you
Dee

"Michel Walsh" wrote:

Basically, yes, the front end can still be in Access with a back end not
another mdb file, but MS SQL Server. I am not sure about Access-Web
thing,
though, but as far as 'plain' Access is involved, yes. That is another
(simpler) possibility than to move to an adp file...


Hoping it may help,
Vanderghast, Access MVP


"Dee" <Dee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C7228899-1E8D-47B5-AFA6-FAAEA54E8EB7@xxxxxxxxxxxxxxxx
Are you stating to upsize the database using the link option? Will
this
still have the access front end, because we do not want to train the
web
content developers sql. We would like for them to view and populate
the
tables and queries the same way they do now.

Thank you
Dee

"Michel Walsh" wrote:

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
.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: complex filter and calculations in access
    ... switch to SQL view. ... query by switching to datasheet view, ... of your database using the from address in this post. ... pre-op infections yes/no ...
    (microsoft.public.access.queries)
  • Re: upsizing to sql 2005
    ... Are you stating to upsize the database using the link option? ... content developers sql. ... Query Name: Arcadia - ARC ... AS RETURN (SELECT TOP 100 PERCENT StoreData.* ...
    (microsoft.public.access.queries)
  • Re: A little more meat this week
    ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ... for a s/w developer and a s/w developer simplifies for the end user). ...
    (comp.databases.pick)
  • Re: complex filter and calculations in access
    ... when using a subquery on the same table as the main query, ... switch to SQL view. ... of your database using the from address in this post. ...
    (microsoft.public.access.queries)
Loading