upsizing to sql 2005

Hopefully only one more question. When I click on the upsizing wizzard,
choose my tables, then choose validate, etc., then choose link SQL server
tables to existing application. When this is complete which table do I add
new records to? One one that says link or the original one?

Thank you
Dee

"Michel Walsh" wrote:

It can be done FROM Access as well as from any other ODBC source, but it
will be stored INTO the MS SQL Server database.



Vanderghast, Access MVP



"Dee" wrote in message
...
If I upsize using the link option in Access 2003, when I input new data
into
the database where is this done from Access or SQL?

Thank you
Dee

"Michel Walsh" wrote:

I don't know how they do it from the web, so I cannot pronounce myself on
any web-part or involving the web, sorry, but from an Access application,
yes. Access can store data in an mdb or in other (updateable) data
source,
which include MS SQL Server linked table.

Vanderghast, Access MVP


"Dee" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
What I do not understand then, is why did these work and
it
stoped
the

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" wrote in message
...
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

Another questions: Can more then one person work on the database from Access
and how does it update the SQL database with the new records in Access?

Thanks
Dee

"Dee" wrote:

Hopefully only one more question. When I click on the upsizing wizzard,
choose my tables, then choose validate, etc., then choose link SQL server
tables to existing application. When this is complete which table do I add
new records to? One one that says link or the original one?

Thank you
Dee

"Michel Walsh" wrote:

It can be done FROM Access as well as from any other ODBC source, but it
will be stored INTO the MS SQL Server database.



Vanderghast, Access MVP



"Dee" wrote in message
...
If I upsize using the link option in Access 2003, when I input new data
into
the database where is this done from Access or SQL?

Thank you
Dee

"Michel Walsh" wrote:

I don't know how they do it from the web, so I cannot pronounce myself on
any web-part or involving the web, sorry, but from an Access application,
yes. Access can store data in an mdb or in other (updateable) data
source,
which include MS SQL Server linked table.

Vanderghast, Access MVP


"Dee" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
What I do not understand then, is why did these work and
it
stoped
the

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" wrote in message
...
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

Yes, from that moment, you add data through the link of the linked table,
so, in this case, into MS SQL Server.

A NOT linked table would store the data locally, though.


Vanderghast, Access MVP


"Dee" wrote in message
...
Hopefully only one more question. When I click on the upsizing wizzard,
choose my tables, then choose validate, etc., then choose link SQL server
tables to existing application. When this is complete which table do I
add
new records to? One one that says link or the original one?

Thank you
Dee

"Michel Walsh" wrote:

It can be done FROM Access as well as from any other ODBC source, but it
will be stored INTO the MS SQL Server database.



Vanderghast, Access MVP



"Dee" wrote in message
...
If I upsize using the link option in Access 2003, when I input new data
into
the database where is this done from Access or SQL?

Thank you
Dee

"Michel Walsh" wrote:

I don't know how they do it from the web, so I cannot pronounce myself
on
any web-part or involving the web, sorry, but from an Access
application,
yes. Access can store data in an mdb or in other (updateable) data
source,
which include MS SQL Server linked table.

Vanderghast, Access MVP


"Dee" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
What I do not understand then, is why did these work
and
it
stoped
the

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" wrote in message
...
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

If the database is on a standard Windows PC, a maximum of 10 connections is
allowed (a limitation from Windows). On a server, an Operating System with
the word SERVER in it, the maximum is configurable.


You can access to the database by multiple means (Access, Dot-Net
application, administration tools, ... ) and each mean can be launched by
multiple users,. in principle, unless someone decides to LOCK the data.


Access won't store anything, it will just act as a window to your data (from
a linked table). Access stores data locally only if the table is NATIVE to
your application (not linked).


Vanderghast, Access MVP



"Dee" wrote in message
...
Another questions: Can more then one person work on the database from
Access
and how does it update the SQL database with the new records in Access?

Thanks
Dee

"Dee" wrote:

Hopefully only one more question. When I click on the upsizing wizzard,
choose my tables, then choose validate, etc., then choose link SQL server
tables to existing application. When this is complete which table do I
add
new records to? One one that says link or the original one?

Thank you
Dee

"Michel Walsh" wrote:

It can be done FROM Access as well as from any other ODBC source, but
it
will be stored INTO the MS SQL Server database.



Vanderghast, Access MVP



"Dee" wrote in message
...
If I upsize using the link option in Access 2003, when I input new
data
into
the database where is this done from Access or SQL?

Thank you
Dee

"Michel Walsh" wrote:

I don't know how they do it from the web, so I cannot pronounce
myself on
any web-part or involving the web, sorry, but from an Access
application,
yes. Access can store data in an mdb or in other (updateable) data
source,
which include MS SQL Server linked table.

Vanderghast, Access MVP


"Dee" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
What I do not understand then, is why did these work
and
it
stoped
the

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" wrote in
message
...
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

Michel,

Thank you. You have been of great help. I will forward this information to
my manager so we can decide which way to go.

If I have any more questions I will post here under a new post.

Again thank you
Dee

"Michel Walsh" wrote:

Yes, from that moment, you add data through the link of the linked table,
so, in this case, into MS SQL Server.

A NOT linked table would store the data locally, though.


Vanderghast, Access MVP


"Dee" wrote in message
...
Hopefully only one more question. When I click on the upsizing wizzard,
choose my tables, then choose validate, etc., then choose link SQL server
tables to existing application. When this is complete which table do I
add
new records to? One one that says link or the original one?

Thank you
Dee

"Michel Walsh" wrote:

It can be done FROM Access as well as from any other ODBC source, but it
will be stored INTO the MS SQL Server database.



Vanderghast, Access MVP



"Dee" wrote in message
...
If I upsize using the link option in Access 2003, when I input new data
into
the database where is this done from Access or SQL?

Thank you
Dee

"Michel Walsh" wrote:

I don't know how they do it from the web, so I cannot pronounce myself
on
any web-part or involving the web, sorry, but from an Access
application,
yes. Access can store data in an mdb or in other (updateable) data
source,
which include MS SQL Server linked table.

Vanderghast, Access MVP


"Dee" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
What I do not understand then, is why did these work
and
it
stoped
the

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

Sorry, One more question:

The input for the records are done from the queries. If I do a link to the
sql server, could it work that way. Can the input still be done in the query
part of access and not in the adp?


"Dee" wrote:

Michel,

Thank you. You have been of great help. I will forward this information to
my manager so we can decide which way to go.

If I have any more questions I will post here under a new post.

Again thank you
Dee

"Michel Walsh" wrote:

Yes, from that moment, you add data through the link of the linked table,
so, in this case, into MS SQL Server.

A NOT linked table would store the data locally, though.


Vanderghast, Access MVP


"Dee" wrote in message
...
Hopefully only one more question. When I click on the upsizing wizzard,
choose my tables, then choose validate, etc., then choose link SQL server
tables to existing application. When this is complete which table do I
add
new records to? One one that says link or the original one?

Thank you
Dee

"Michel Walsh" wrote:

It can be done FROM Access as well as from any other ODBC source, but it
will be stored INTO the MS SQL Server database.



Vanderghast, Access MVP



"Dee" wrote in message
...
If I upsize using the link option in Access 2003, when I input new data
into
the database where is this done from Access or SQL?

Thank you
Dee

"Michel Walsh" wrote:

I don't know how they do it from the web, so I cannot pronounce myself
on
any web-part or involving the web, sorry, but from an Access
application,
yes. Access can store data in an mdb or in other (updateable) data
source,
which include MS SQL Server linked table.

Vanderghast, Access MVP


"Dee" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
What I do not understand then, is why did these work
and
it
stoped
the

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

A query in Access-Jet will be started and under the supervision of Jet, with
Jet syntax parsing and evaluation. If the Jet-query involves a linked table,
Jet communicates (through ODBC or otherwise) to the not-Jet-native data
storage, transparently.

Vanderghast, Access MVP

"Dee" wrote in message
...
Sorry, One more question:

The input for the records are done from the queries. If I do a link to
the
sql server, could it work that way. Can the input still be done in the
query
part of access and not in the adp?


"Dee" wrote:

Michel,

Thank you. You have been of great help. I will forward this information
to
my manager so we can decide which way to go.

If I have any more questions I will post here under a new post.

Again thank you
Dee

"Michel Walsh" wrote:

Yes, from that moment, you add data through the link of the linked
table,
so, in this case, into MS SQL Server.

A NOT linked table would store the data locally, though.


Vanderghast, Access MVP


"Dee" wrote in message
...
Hopefully only one more question. When I click on the upsizing
wizzard,
choose my tables, then choose validate, etc., then choose link SQL
server
tables to existing application. When this is complete which table do
I
add
new records to? One one that says link or the original one?

Thank you
Dee

"Michel Walsh" wrote:

It can be done FROM Access as well as from any other ODBC source,
but it
will be stored INTO the MS SQL Server database.



Vanderghast, Access MVP



"Dee" wrote in message
...
If I upsize using the link option in Access 2003, when I input new
data
into
the database where is this done from Access or SQL?

Thank you
Dee

"Michel Walsh" wrote:

I don't know how they do it from the web, so I cannot pronounce
myself
on
any web-part or involving the web, sorry, but from an Access
application,
yes. Access can store data in an mdb or in other (updateable)
data
source,
which include MS SQL Server linked table.

Vanderghast, Access MVP


"Dee" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
What I do not understand then, is why did these
work
and
it
stoped
the

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