Migrating from Excel to sql-server

Hello,
I have got the job to migrate data from an Excel-sheet to a
sql-server-database. I have a lot of experience with Access but
Sql-Server is not in my line.
So who can give me some advice about how to do this job?

thanx for your help, HelmutEasiest way is to use the Data Transformation Services (DTS) of SQL Server.
It has a nice UI.

Start > Programs > Microsoft SQL Server > Import and Export Data

HTH,
Dave

"Helmut Blass" <helmut_blass@.tweb.de> wrote in message
news:bplp9f$olf$07$1@.news.t-online.com...
> Hello,
> I have got the job to migrate data from an Excel-sheet to a
> sql-server-database. I have a lot of experience with Access but
> Sql-Server is not in my line.
> So who can give me some advice about how to do this job?
> thanx for your help, Helmut

Migrating from Access, boolean field problem

Hallo,
I am migrating complex database from Access 2000 to MSSQL server 8. This
database is used by web applications. Some time I need to be able swicth
between the old Access version and new SQL server driven one. I have
different connection strings, and connecting works well.
However, some sql query incompatibility has appeared...
Number of Access tables have boolean (Yes/No) fields. In Access, I could run
a query like this:
SELECT id FROM myTable WHERE myBooleanField
it returns all records where myBooleanField value is true. Now, when I
migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" for
every Access "true" value, and "0" for "false" values. With this new field
type, I can no longer run the above mentioned query. I must change it to
this:
SELECT id FROM myTable WHERE myBooleanField=1
Now, this would not be a crazy problem, however I need to be able to run it
for some time on both database solutions, Access and MSSQL, so I really need
to have compatible queries.
One solution I see now would be converting the field type in access from
boolean to byte, and then run the "=1" queries. But that could involve range
of incompatibilities within the application itself, that assumes that these
fields are "true" or "false" instead of "1" and "0". Browsing through the
application to find these places is not really the best thing I can imagine.
So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
to solve my problem?
Thanks,
PavilsOne issue is that Access/Jet booleans are not true booleans, but can
also contain null values. Change the Jet columns to Not Null and
provide a default value (same with SQLS). This lets you avoid the
three-valued logic trap. In your SQL statements and code, perform all
comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
everywhere, so both engines are going to interpret "<>0" the same way.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Hallo,
>I am migrating complex database from Access 2000 to MSSQL server 8. This
>database is used by web applications. Some time I need to be able swicth
>between the old Access version and new SQL server driven one. I have
>different connection strings, and connecting works well.
>However, some sql query incompatibility has appeared...
>Number of Access tables have boolean (Yes/No) fields. In Access, I could run
>a query like this:
>SELECT id FROM myTable WHERE myBooleanField
>it returns all records where myBooleanField value is true. Now, when I
>migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" for
>every Access "true" value, and "0" for "false" values. With this new field
>type, I can no longer run the above mentioned query. I must change it to
>this:
>SELECT id FROM myTable WHERE myBooleanField=1
>Now, this would not be a crazy problem, however I need to be able to run it
>for some time on both database solutions, Access and MSSQL, so I really need
>to have compatible queries.
>One solution I see now would be converting the field type in access from
>boolean to byte, and then run the "=1" queries. But that could involve range
>of incompatibilities within the application itself, that assumes that these
>fields are "true" or "false" instead of "1" and "0". Browsing through the
>application to find these places is not really the best thing I can imagine.
>So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
>to solve my problem?
>Thanks,
>Pavils
>|||Thanks Mary,
This is a good suggestion, it helps me to move further...
Now another problem has arised:
Access runs this query without complaints:
UPDATE myTable SET myBool = NOT myBool WHERE id=100
What could be a syntax working on both Jet and MSSQL engines?
I tried this one...
UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
but I get error -
'IIf' is not a recognized function name.
Maybe some other smart syntax?
Thanks,
-- Pavils
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.4ax.com...
> One issue is that Access/Jet booleans are not true booleans, but can
> also contain null values. Change the Jet columns to Not Null and
> provide a default value (same with SQLS). This lets you avoid the
> three-valued logic trap. In your SQL statements and code, perform all
> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
> everywhere, so both engines are going to interpret "<>0" the same way.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com
> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
> <pavils@.mailbox.riga.lv> wrote:
> >Hallo,
> >
> >I am migrating complex database from Access 2000 to MSSQL server 8. This
> >database is used by web applications. Some time I need to be able swicth
> >between the old Access version and new SQL server driven one. I have
> >different connection strings, and connecting works well.
> >
> >However, some sql query incompatibility has appeared...
> >
> >Number of Access tables have boolean (Yes/No) fields. In Access, I could
run
> >a query like this:
> >
> >SELECT id FROM myTable WHERE myBooleanField
> >
> >it returns all records where myBooleanField value is true. Now, when I
> >migrated to MSSQL, these boolean fields are of "bit" type, and carry "1"
for
> >every Access "true" value, and "0" for "false" values. With this new
field
> >type, I can no longer run the above mentioned query. I must change it to
> >this:
> >
> >SELECT id FROM myTable WHERE myBooleanField=1
> >
> >Now, this would not be a crazy problem, however I need to be able to run
it
> >for some time on both database solutions, Access and MSSQL, so I really
need
> >to have compatible queries.
> >
> >One solution I see now would be converting the field type in access from
> >boolean to byte, and then run the "=1" queries. But that could involve
range
> >of incompatibilities within the application itself, that assumes that
these
> >fields are "true" or "false" instead of "1" and "0". Browsing through the
> >application to find these places is not really the best thing I can
imagine.
> >
> >So, maybe there is a real "boolean" field in MSSQL? Maybe other
approaches
> >to solve my problem?
> >
> >Thanks,
> >
> >Pavils
> >
>|||One thing that's going to continue to trip you up in writing your
queries is not understanding the fundamental differences between
Access SQL and T-SQL, the two main points of which are:
-- Access SQL relies heavily on the expression service and VBA to
compensate for the fact that it has no programming language features
on its own. You can execute VBA functions like Iif inside of a query
only against the Jet engine with Access as a FE. .
-- T-SQL contains many programming language features, like variables.
It does not rely on an external programming language to provide this
functionality the way Access does. When you try VBA syntax, it fails
because SQLS has no knowledge of VBA.
For the most part, you'll want to stay away from language elements
that are specific to either Jet SQL or T-SQL. I'd start by writing the
queries using the Query Analyzer, then copying/pasting them into an
Access query window to see if the syntax works in both. SQL Books
Online is a good reference--Access SQL documentation is practically
non-existant. Besides the language elements, be aware that you're also
going to have problems with delimiters when working with datetime
values -- Jet uses # and SQLS uses '.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 18:09:50 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Thanks Mary,
>This is a good suggestion, it helps me to move further...
>Now another problem has arised:
>Access runs this query without complaints:
>UPDATE myTable SET myBool = NOT myBool WHERE id=100
>What could be a syntax working on both Jet and MSSQL engines?
>I tried this one...
>UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
>but I get error -
>'IIf' is not a recognized function name.
>Maybe some other smart syntax?
>Thanks,
>-- Pavils
>"Mary Chipman" <mchip@.nomail.please> wrote in message
>news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.4ax.com...
>> One issue is that Access/Jet booleans are not true booleans, but can
>> also contain null values. Change the Jet columns to Not Null and
>> provide a default value (same with SQLS). This lets you avoid the
>> three-valued logic trap. In your SQL statements and code, perform all
>> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
>> everywhere, so both engines are going to interpret "<>0" the same way.
>> -- Mary
>> MCW Technologies
>> http://www.mcwtech.com
>> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
>> <pavils@.mailbox.riga.lv> wrote:
>> >Hallo,
>> >
>> >I am migrating complex database from Access 2000 to MSSQL server 8. This
>> >database is used by web applications. Some time I need to be able swicth
>> >between the old Access version and new SQL server driven one. I have
>> >different connection strings, and connecting works well.
>> >
>> >However, some sql query incompatibility has appeared...
>> >
>> >Number of Access tables have boolean (Yes/No) fields. In Access, I could
>run
>> >a query like this:
>> >
>> >SELECT id FROM myTable WHERE myBooleanField
>> >
>> >it returns all records where myBooleanField value is true. Now, when I
>> >migrated to MSSQL, these boolean fields are of "bit" type, and carry "1"
>for
>> >every Access "true" value, and "0" for "false" values. With this new
>field
>> >type, I can no longer run the above mentioned query. I must change it to
>> >this:
>> >
>> >SELECT id FROM myTable WHERE myBooleanField=1
>> >
>> >Now, this would not be a crazy problem, however I need to be able to run
>it
>> >for some time on both database solutions, Access and MSSQL, so I really
>need
>> >to have compatible queries.
>> >
>> >One solution I see now would be converting the field type in access from
>> >boolean to byte, and then run the "=1" queries. But that could involve
>range
>> >of incompatibilities within the application itself, that assumes that
>these
>> >fields are "true" or "false" instead of "1" and "0". Browsing through the
>> >application to find these places is not really the best thing I can
>imagine.
>> >
>> >So, maybe there is a real "boolean" field in MSSQL? Maybe other
>approaches
>> >to solve my problem?
>> >
>> >Thanks,
>> >
>> >Pavils
>> >
>|||Thanks, Mary for your help
I've been doing databasing in Access, mySQL, and some generic work in
interBase, MSSQL, and Oracle... but in this case I have to port fairly
complex application to MSSQL and I really see all those blanks I miss in the
knowledge about MSSQL. Well, but that's the nature of learning curve, being
very steep :) I'd be happy if this project would be originally developed in
MSSQL, but unfortunately this is not the case.
On my previous query, where I was looking for both Jet and MSSQL engine
compatible syntax for NOT-ting the boolean value, I solved it like this:
UPDATE myTable SET myBool = 1-ABS(myBool) WHERE id=123
That works on both Access and MSSQL.
The date delimiter thing I fortunately have abstracted away in my database
library, so it's easily fixable thing. Some issues about text/BLOB fields
are luring in now, but nothing very crucial though..
Thanks,
Pavils Jurjans
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:7roe30topfasals1m2avf2u2fj3dqol9dn@.4ax.com...
> One thing that's going to continue to trip you up in writing your
> queries is not understanding the fundamental differences between
> Access SQL and T-SQL, the two main points of which are:
> -- Access SQL relies heavily on the expression service and VBA to
> compensate for the fact that it has no programming language features
> on its own. You can execute VBA functions like Iif inside of a query
> only against the Jet engine with Access as a FE. .
> -- T-SQL contains many programming language features, like variables.
> It does not rely on an external programming language to provide this
> functionality the way Access does. When you try VBA syntax, it fails
> because SQLS has no knowledge of VBA.
> For the most part, you'll want to stay away from language elements
> that are specific to either Jet SQL or T-SQL. I'd start by writing the
> queries using the Query Analyzer, then copying/pasting them into an
> Access query window to see if the syntax works in both. SQL Books
> Online is a good reference--Access SQL documentation is practically
> non-existant. Besides the language elements, be aware that you're also
> going to have problems with delimiters when working with datetime
> values -- Jet uses # and SQLS uses '.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com

Migrating from Access, boolean field problem

Hallo,
I am migrating complex database from Access 2000 to MSSQL server 8. This
database is used by web applications. Some time I need to be able swicth
between the old Access version and new SQL server driven one. I have
different connection strings, and connecting works well.
However, some sql query incompatibility has appeared...
Number of Access tables have boolean (Yes/No) fields. In Access, I could run
a query like this:
SELECT id FROM myTable WHERE myBooleanField
it returns all records where myBooleanField value is true. Now, when I
migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" for
every Access "true" value, and "0" for "false" values. With this new field
type, I can no longer run the above mentioned query. I must change it to
this:
SELECT id FROM myTable WHERE myBooleanField=1
Now, this would not be a crazy problem, however I need to be able to run it
for some time on both database solutions, Access and MSSQL, so I really need
to have compatible queries.
One solution I see now would be converting the field type in access from
boolean to byte, and then run the "=1" queries. But that could involve range
of incompatibilities within the application itself, that assumes that these
fields are "true" or "false" instead of "1" and "0". Browsing through the
application to find these places is not really the best thing I can imagine.
So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
to solve my problem?
Thanks,
PavilsOne issue is that Access/Jet booleans are not true booleans, but can
also contain null values. Change the Jet columns to Not Null and
provide a default value (same with SQLS). This lets you avoid the
three-valued logic trap. In your SQL statements and code, perform all
comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
everywhere, so both engines are going to interpret "<>0" the same way.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:

>Hallo,
>I am migrating complex database from Access 2000 to MSSQL server 8. This
>database is used by web applications. Some time I need to be able swicth
>between the old Access version and new SQL server driven one. I have
>different connection strings, and connecting works well.
>However, some sql query incompatibility has appeared...
>Number of Access tables have boolean (Yes/No) fields. In Access, I could ru
n
>a query like this:
>SELECT id FROM myTable WHERE myBooleanField
>it returns all records where myBooleanField value is true. Now, when I
>migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" fo
r
>every Access "true" value, and "0" for "false" values. With this new field
>type, I can no longer run the above mentioned query. I must change it to
>this:
>SELECT id FROM myTable WHERE myBooleanField=1
>Now, this would not be a crazy problem, however I need to be able to run it
>for some time on both database solutions, Access and MSSQL, so I really nee
d
>to have compatible queries.
>One solution I see now would be converting the field type in access from
>boolean to byte, and then run the "=1" queries. But that could involve rang
e
>of incompatibilities within the application itself, that assumes that these
>fields are "true" or "false" instead of "1" and "0". Browsing through the
>application to find these places is not really the best thing I can imagine
.
>So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
>to solve my problem?
>Thanks,
>Pavils
>|||Thanks Mary,
This is a good suggestion, it helps me to move further...
Now another problem has arised:
Access runs this query without complaints:
UPDATE myTable SET myBool = NOT myBool WHERE id=100
What could be a syntax working on both Jet and MSSQL engines?
I tried this one...
UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
but I get error -
'IIf' is not a recognized function name.
Maybe some other smart syntax?
Thanks,
-- Pavils
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.
4ax.com...
> One issue is that Access/Jet booleans are not true booleans, but can
> also contain null values. Change the Jet columns to Not Null and
> provide a default value (same with SQLS). This lets you avoid the
> three-valued logic trap. In your SQL statements and code, perform all
> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
> everywhere, so both engines are going to interpret "<>0" the same way.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com
> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
> <pavils@.mailbox.riga.lv> wrote:
>
run
for
field
it
need
range
these
imagine.
approaches
>|||One thing that's going to continue to trip you up in writing your
queries is not understanding the fundamental differences between
Access SQL and T-SQL, the two main points of which are:
-- Access SQL relies heavily on the expression service and VBA to
compensate for the fact that it has no programming language features
on its own. You can execute VBA functions like Iif inside of a query
only against the Jet engine with Access as a FE. .
-- T-SQL contains many programming language features, like variables.
It does not rely on an external programming language to provide this
functionality the way Access does. When you try VBA syntax, it fails
because SQLS has no knowledge of VBA.
For the most part, you'll want to stay away from language elements
that are specific to either Jet SQL or T-SQL. I'd start by writing the
queries using the Query Analyzer, then copying/pasting them into an
Access query window to see if the syntax works in both. SQL Books
Online is a good reference--Access SQL documentation is practically
non-existant. Besides the language elements, be aware that you're also
going to have problems with delimiters when working with datetime
values -- Jet uses # and SQLS uses '.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 18:09:50 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:

>Thanks Mary,
>This is a good suggestion, it helps me to move further...
>Now another problem has arised:
>Access runs this query without complaints:
>UPDATE myTable SET myBool = NOT myBool WHERE id=100
>What could be a syntax working on both Jet and MSSQL engines?
>I tried this one...
>UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
>but I get error -
>'IIf' is not a recognized function name.
>Maybe some other smart syntax?
>Thanks,
>-- Pavils
>"Mary Chipman" <mchip@.nomail.please> wrote in message
> news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.
4ax.com...
>run
>for
>field
>it
>need
>range
>these
>imagine.
>approaches
>|||Thanks, Mary for your help
I've been doing databasing in Access, mySQL, and some generic work in
interBase, MSSQL, and Oracle... but in this case I have to port fairly
complex application to MSSQL and I really see all those blanks I miss in the
knowledge about MSSQL. Well, but that's the nature of learning curve, being
very steep I'd be happy if this project would be originally developed in
MSSQL, but unfortunately this is not the case.
On my previous query, where I was looking for both Jet and MSSQL engine
compatible syntax for NOT-ting the boolean value, I solved it like this:
UPDATE myTable SET myBool = 1-ABS(myBool) WHERE id=123
That works on both Access and MSSQL.
The date delimiter thing I fortunately have abstracted away in my database
library, so it's easily fixable thing. Some issues about text/BLOB fields
are luring in now, but nothing very crucial though..
Thanks,
Pavils Jurjans
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:7roe30topfasals1m2avf2u2fj3dqol9dn@.
4ax.com...
> One thing that's going to continue to trip you up in writing your
> queries is not understanding the fundamental differences between
> Access SQL and T-SQL, the two main points of which are:
> -- Access SQL relies heavily on the expression service and VBA to
> compensate for the fact that it has no programming language features
> on its own. You can execute VBA functions like Iif inside of a query
> only against the Jet engine with Access as a FE. .
> -- T-SQL contains many programming language features, like variables.
> It does not rely on an external programming language to provide this
> functionality the way Access does. When you try VBA syntax, it fails
> because SQLS has no knowledge of VBA.
> For the most part, you'll want to stay away from language elements
> that are specific to either Jet SQL or T-SQL. I'd start by writing the
> queries using the Query Analyzer, then copying/pasting them into an
> Access query window to see if the syntax works in both. SQL Books
> Online is a good reference--Access SQL documentation is practically
> non-existant. Besides the language elements, be aware that you're also
> going to have problems with delimiters when working with datetime
> values -- Jet uses # and SQLS uses '.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com|||Pavil,
I've been in this situation many times. As has been mentioned already, Acces
s'
YES/NO field is really a three-state field: YES/NO/NULL, where in SQL SERVER
the BIT type is 2-state.
The other problem is that Access treats TRUE as -1 where SQL Serrver actuall
y
treats TRUE as any non-zero value.
Armed with that information, you have two choices:
1) Use INT field in SQL Server, and you'll see good ol' -1 and 0 in that tab
le
anytime Access gets its ODBC/JET mitts on it.
2) When creating WHERE clauses in Access, get in the habit of ALWAYS using t
he
following test for TRUE:
Fld <> 0
Do that in VBA too, just to be consistent.

Migrating from Access to SQL Server with a web front end

Afternoon all,

Apologies for cross-posting but as my query covers both Access and SQL
Server I thought I'd send it both!

I have inherited a project to migrate a fairly complex series of
Access databases into a single proper SQL database with a web front
end.

Its quite a nasty job as people are working on a variety of data sets
at several Universities around the world and the data has got very
messy; hence the requirement to put it all on one live web enabled
database server and provide a web-based front end (particularly as
some users insist on using Macs so can't run Access as a front end
anyway).

If anyone could give me hints on how to perform such a migration or if
anyone knows of any good books or other documents on this I'd be
grateful for assistance.

Many thanks

Rich May
Museum of LondonFirst off, congratulations - this is a great move to make.

As far as converting the tables, use DTS (Data Transformation Services) in
sql server. It is pretty much wizard driven and works really well. As for
the queries, you would do best to forget you even have them in Access and
rewrite everything using Stored Procedures. My favorite book for "stepping"
between access and sql server is "Microsoft Access Developer's Guide to SQL
Server" by Chipman and Baron. It's wonderful.

As for your web front end... that is where the headache starts. You can
pretty much choose two paths if you want to stay on the Microsoft path. You
can do it using ASP or ASP.NET. Both will run on IIS (.net needs framework
installed).

If you are serving to Macs, you may deliver your app better using ASP
because you will be able to better control the rendered output (very good
for Netscape). I use Visual Basic to build "ActiveX DLL" components to
alleviate the complexity of the ASP pages so you would want to be proficient
in Visual Basic and instantiating COM components. Next, you need to take
ADO (ActiveX Data Objects) head on and learn it well, that is what you will
use to connect to sql, execute queries, and work with data.

If you are using IE on all clients and have control over them, you will find
that Visual Studio.NET does a great job at deploying web applications in
ease and speed, once you pass the high learning curve. I VERY highly
recommend "Programming Microsoft Visual Basic .Net" by Francesco Balena
(1600 pages).

If it all sounds complex, don't get me wrong... it is. If you need help,
let me know, we do this for a living.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Rich May" <rmay@.museumoflondon.org.uk> wrote in message
news:e12c70e0.0311140943.722ed0ba@.posting.google.c om...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of London|||several choices, depending on what you're comfy with ..
1) sql server, IIS & ASP
2) mySQL, apache, php

here's a RAD tool that I found the other day to create web pages for
either option (I haven't tried it yet)
http://www.citybusinesslogic.com/apwab.php

and somewhere I've got vba code to convert ms-access tables to mysql
format

rmay@.museumoflondon.org.uk (Rich May) wrote in message news:<e12c70e0.0311140943.722ed0ba@.posting.google.com>...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of London|||Hi Rich,

I think there are 3 options. a) Access' Upsizing Migration Wizard
b)Sql Server's DTS and c) setting Access as a linked server and
programmatically importing the data. I believe the main problem
you'll run into is that Access' datatypes are not 100% compatible with
Sql server's. - Louis|||louisducnguyen@.hotmail.com (louis nguyen) wrote in message news:<b0e9d53.0311142220.41dff853@.posting.google.com>...
> Hi Rich,
> I think there are 3 options. a) Access' Upsizing Migration Wizard
> b)Sql Server's DTS and c) setting Access as a linked server and
> programmatically importing the data. I believe the main problem
> you'll run into is that Access' datatypes are not 100% compatible with
> Sql server's. - Louis

Another problem I have seen with the upsize wizard is it makes all
columns with the type nvarchar i.e. unicode which doubles the space
requirements.

Duncan|||"duncan" wrote

> Another problem I have seen with
> the upsize wizard is it makes all
> columns with the type nvarchar i.e.
> unicode which doubles the space
> requirements.

First, I don't think that is true -- all _text_ columns, perhaps, but
certainly not numeric and date/time columns.

Secondly, were you under the impression that Access does NOT use unicode? It
has since Access 2000. There is an option for "unicode compression" to
reduce the impact; I don't know if there is such an option in SQL Server,
but would think that it would have a similar feature.

Larry Linson
Microsoft Access MVP|||Is Cold Fusion a viable alternative to ASP in this kind of situation?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||In Ms SQL...

Char is fixed length and pads with spaces
NChar is fixed length and pads with spaces, plus is unicode
VarChar is variable character (with max length spec)
NVarChar is variable (maxlength), plus is unicode

Since I don't deploy to any other language and don't plan to, I never use
the N' datatypes.

Also in SQL, the "SmallDateTime" field type is 4 bytes and the "DateTime" is
8 bytes). If it's 4 bytes in Access, then the equiv is "SmallDateTime".

In SQL Numeric DT's, there is tinyint, smallint, int, real, bigint, float,
money, smallmoney, and decimal. All with their mix of capacity.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Larry Linson" <bouncer@.localhost.not> wrote in message
news:bpytb.58064$p9.53071@.nwrddc02.gnilink.net...
> "duncan" wrote
> > Another problem I have seen with
> > the upsize wizard is it makes all
> > columns with the type nvarchar i.e.
> > unicode which doubles the space
> > requirements.
> First, I don't think that is true -- all _text_ columns, perhaps, but
> certainly not numeric and date/time columns.
> Secondly, were you under the impression that Access does NOT use unicode?
It
> has since Access 2000. There is an option for "unicode compression" to
> reduce the impact; I don't know if there is such an option in SQL Server,
> but would think that it would have a similar feature.
> Larry Linson
> Microsoft Access MVP|||Sure, I looked into it some time ago when I was a beginner, but then I
discovered the unimaginable amount of documentation and books from multiple
authors in Microsoft products and I didn't even give it a second thought.
To me, no product is satisfactory if I have to spend days looking for
answers, and especially when they aren't there to be found.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"John Callaghan" <jackit2@.yahoo.com> wrote in message
news:3fb88be3$0$194$75868355@.news.frii.net...
> Is Cold Fusion a viable alternative to ASP in this kind of situation?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||In my experience, the data conversion part of a project like this is almost
trivial compared to the front-end application challenge.

Access forms and reports can be incredibly complex, with subforms, simple
data binding, no-code dynamic behavior (record navigation, filtering,
sorting, finding, column-reordering, all those are built in to Access, not
at all in asp), and the list goes on and on. If your Access front-end
application is complex, it could be impossibly time-consuming to do in asp,
slightly less so in asp.net.

You would need some other tool for reporting (Crystal or others), I've made
crude html reports in asp and it is nearly impossible to do even the easy
stuff Access reports can do.

As a example, I wrote a full maintenance application for a 30 table database
(with lots of RI between tables), with multiple subforms, dropdown
navigation, and lots of other features, all in one day of work.. ASP would
have taken weeks to do even close to the same functionality.

So overall, my suggestion is if the Access forms are complex, plan for 1000
to 2000% more development time over Access forms. You will definitely need a
better reporting tool than html/asp. Hope this helps,

Steve Nyberg

"Rich May" <rmay@.museumoflondon.org.uk> wrote in message
news:e12c70e0.0311140943.722ed0ba@.posting.google.c om...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of Londonsql

migrating from Access to SQL Server

Our organization has about 10 multiuser Access databases that I'm
considering migrating to SQL Server. Currently, these split databases
perform reasonably well in Access over our LANs, but slow over the T1 lines.
However, we occasionally run into corruption problems, and we would
eventually like to develop some browser-based applications to work with our
data.
Most of these databases have dozens of queries and form controls also based
on queries, so the most realistic strategy for us in the short term would
probably be to just migrate the data, and link the remaining Access front
ends to the tables in SQL Server.
However, I'm wondering if we will accomplish much by taking such a step,
since all the queries will still be in the front end. I understand there
would be a performance improvement if we had the resources (which we
currently don't) to convert those queries to views and stored procedures in
SQL Server. But if we only move the data tables to the back end, do we
really obtain enough of a benefit to justify taking even that step?
Thanks in advance,
Paul
Hi,
Please do not misunderstand moving data from Acess to SQL Server would
increase the performance, it is still based on the design of the databases
and all the indexes. I would recommend to move from Access to SQL Server
since SQL Server can support more concurrent users and it provides better
security and data import/export functions.
Ed
"Paul Ponzelli" wrote:

> Our organization has about 10 multiuser Access databases that I'm
> considering migrating to SQL Server. Currently, these split databases
> perform reasonably well in Access over our LANs, but slow over the T1 lines.
> However, we occasionally run into corruption problems, and we would
> eventually like to develop some browser-based applications to work with our
> data.
> Most of these databases have dozens of queries and form controls also based
> on queries, so the most realistic strategy for us in the short term would
> probably be to just migrate the data, and link the remaining Access front
> ends to the tables in SQL Server.
> However, I'm wondering if we will accomplish much by taking such a step,
> since all the queries will still be in the front end. I understand there
> would be a performance improvement if we had the resources (which we
> currently don't) to convert those queries to views and stored procedures in
> SQL Server. But if we only move the data tables to the back end, do we
> really obtain enough of a benefit to justify taking even that step?
> Thanks in advance,
> Paul
>
>
|||Good points. Thanks, Ed.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:1F01575D-FA7D-4C73-ADC6-F205B7B035DD@.microsoft.com...[vbcol=seagreen]
> Hi,
> Please do not misunderstand moving data from Acess to SQL Server would
> increase the performance, it is still based on the design of the databases
> and all the indexes. I would recommend to move from Access to SQL Server
> since SQL Server can support more concurrent users and it provides better
> security and data import/export functions.
> Ed
> "Paul Ponzelli" wrote:
|||"Paul Ponzelli" <begone@.spam.forever> wrote in message
news:enIRb3vPFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Our organization has about 10 multiuser Access databases that I'm
> considering migrating to SQL Server. Currently, these split databases
> perform reasonably well in Access over our LANs, but slow over the T1
lines.
> However, we occasionally run into corruption problems, and we would
> eventually like to develop some browser-based applications to work with
our
> data.
In addition to the valid points that Ed made, SQL Server is far more
resilient to data corruption than Access.
Steve
|||Another reason to make the move.
Thanks, Steve.

Migrating from Access to SQL server

We have some data and a large number of queries in an Access database.
Can we migrate the queries over to SQL aong with the data? The data
should be easy, but rebuilding these queries would be a pain.
Lorenzo,
The Access query upsizing is handled variously - they are created on SQl
Server as either views, stored procedures, or user-defined function, though
a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
comprehensive snap shot report (.snp) to your My Documents directory of
everything it was and wasn't able to accomplish.
HTH,
Paul Ibison
|||In article <e7jLNoPLEHA.1340@.TK2MSFTNGP12.phx.gbl>,
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote:

> Lorenzo,
> The Access query upsizing is handled variously - they are created on SQl
> Server as either views, stored procedures, or user-defined function, though
> a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
> comprehensive snap shot report (.snp) to your My Documents directory of
> everything it was and wasn't able to accomplish.
> HTH,
> Paul Ibison
>
Thanks, I'll have to look at that again. I found some documentation on
MS's website. It sounded like SQL would not bring the queries in
directly, but the docs are rather ambiguous.

Migrating from Access to SQL server

We have some data and a large number of queries in an Access database.
Can we migrate the queries over to SQL aong with the data? The data
should be easy, but rebuilding these queries would be a pain.Lorenzo,
The Access query upsizing is handled variously - they are created on SQl
Server as either views, stored procedures, or user-defined function, though
a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
comprehensive snap shot report (.snp) to your My Documents directory of
everything it was and wasn't able to accomplish.
HTH,
Paul Ibison|||In article <e7jLNoPLEHA.1340@.TK2MSFTNGP12.phx.gbl>,
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote:
> Lorenzo,
> The Access query upsizing is handled variously - they are created on SQl
> Server as either views, stored procedures, or user-defined function, though
> a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
> comprehensive snap shot report (.snp) to your My Documents directory of
> everything it was and wasn't able to accomplish.
> HTH,
> Paul Ibison
>
Thanks, I'll have to look at that again. I found some documentation on
MS's website. It sounded like SQL would not bring the queries in
directly, but the docs are rather ambiguous.

Migrating from Access to SQL Server

I recently migrated some user tables from Access to SQL Server while leaving
the forms, reports, and queries in Access with Linked tables to the SQL
instance. Some of the users queries/forms are not working correctly? It
seems that Access Queries return a single empty row when no matches are foun
d
while SQL Server returns an empty result set. In Access, the users use a
query to search for a customer number, if no matching number is found the
customer form is displayed with one record containing no values and the user
s
can then input new customer information. When the query/form is pointed to
SQL Server, when no matching customer is found a blank page is displayed. I
s
there a way to force SQL Server to return an empty row if none are found?Hi,
probably the attached script would help to you (I'm not sure how to
implement it within Access enviroment):
create table t (col1 int null, col2 varchar(10) null)
--without variables
select col1,col2 from t
union all
select null, null
--with variables
declare @.r as int
select * from t
set @.r=@.@.rowcount
if @.r>0
return
else
select null as col1, null as col2
drop table t
Denis
"DT" wrote:

> I recently migrated some user tables from Access to SQL Server while leavi
ng
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are fo
und
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the us
ers
> can then input new customer information. When the query/form is pointed t
o
> SQL Server, when no matching customer is found a blank page is displayed.
Is
> there a way to force SQL Server to return an empty row if none are found?
>|||Although your solution does cause the form to be displayed (progress!) I can
not enter data on the form? Am I missing something?
"Denis" wrote:
> Hi,
> probably the attached script would help to you (I'm not sure how to
> implement it within Access enviroment):
> create table t (col1 int null, col2 varchar(10) null)
> --without variables
> select col1,col2 from t
> union all
> select null, null
> --with variables
> declare @.r as int
> select * from t
> set @.r=@.@.rowcount
> if @.r>0
> return
> else
> select null as col1, null as col2
>
> drop table t
> Denis
> "DT" wrote:
>|||There is no such thing as an "empty" row in SQL Server. The functionality
you are wanting would probably be a property of the recordset or data grid.
"DT" <DT@.discussions.microsoft.com> wrote in message
news:0D425E66-49D7-4C46-97E2-7813DB70A6D0@.microsoft.com...
>I recently migrated some user tables from Access to SQL Server while
>leaving
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are
> found
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the
> users
> can then input new customer information. When the query/form is pointed
> to
> SQL Server, when no matching customer is found a blank page is displayed.
> Is
> there a way to force SQL Server to return an empty row if none are found?
>

Migrating from Access to SQL server

We have some data and a large number of queries in an Access database.
Can we migrate the queries over to SQL aong with the data? The data
should be easy, but rebuilding these queries would be a pain.Lorenzo,
The Access query upsizing is handled variously - they are created on SQl
Server as either views, stored procedures, or user-defined function, though
a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
comprehensive snap shot report (.snp) to your My Documents directory of
everything it was and wasn't able to accomplish.
HTH,
Paul Ibison|||In article <e7jLNoPLEHA.1340@.TK2MSFTNGP12.phx.gbl>,
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote:

> Lorenzo,
> The Access query upsizing is handled variously - they are created on SQl
> Server as either views, stored procedures, or user-defined function, thoug
h
> a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
> comprehensive snap shot report (.snp) to your My Documents directory of
> everything it was and wasn't able to accomplish.
> HTH,
> Paul Ibison
>
Thanks, I'll have to look at that again. I found some documentation on
MS's website. It sounded like SQL would not bring the queries in
directly, but the docs are rather ambiguous.sql

Migrating from Access to SQL

Hi guys, its been awhile. I inherited a database thats an MDB and I need to get it on to sql server but I am having problem with this query, can anyone help please. Its suppose to give you the month and year when you click on it, you just put that in and up come the results on a query or report, now I have something similar to this but the code is very different.

SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and the Year]));Which bit are you struggling with? How far have you got?|||The format$ is the part thats giving me fits sQL server doesnt recognize that function|||try this --SELECT DATEPART(year,[Main Table].[Date]) AS [Date By Year]
, DATEPART(month,[Main Table].[Date]) AS [Date By Month]
, [Main Table].[Action Type]
, Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type]
INNER
JOIN [Main Table]
ON [Action Type].ID = [Main Table].[Action Type]
WHERE [Main Table].[Date] >= '2007-03-01' /* first day of this month */
AND [Main Table].[Date] < '2007-03-01' /* first day of next month */
GROUP
BY DATEPART(year,[Main Table].[Date])
, DATEPART(month,[Main Table].[Date])
, [Main Table].[Action Type]note you could concatenate those two datepart expressions if you need to

also note the index-friendly method of extracting rows for a certain month (anything else will involve table scans)

and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVING|||and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVINGUh? How can it not know the difference? Are you confusing access and access developers?|||no, access itself generates that crap|||Only if you don't add a second column into your query designer and make that a "Where".

But yes - Access does not encourage thoughtful & efficient query design.|||Thanks guys its just that the query he created gives you a dialog box "like the edit parameters" box and you can put the month and year in yourself and it generates a report. Now I have one for searching my subforms last name field ALTER FUNCTION dbo.SearchLName
(@.Enter_SubjLastName nvarchar(100))
RETURNS TABLE
AS
RETURN ( SELECT dbo.Report.[Incident Report No], dbo.Subjects.SubjFirstName, dbo.Subjects.SubjLastName, dbo.Report.TypeOfIncident
FROM dbo.Report INNER JOIN
dbo.Subjects ON dbo.Report.[Incident Report No] = dbo.Subjects.[Incident number]
WHERE (dbo.Subjects.SubjLastName = @.Enter_SubjLastName) )

This is a function and you enter in the last name and it generate a report for you, now from what I see hes doing the same thing but with Month and Year, does that make sense??|||Here is what I have so far but I need to do this by month too they get a pretty detailed report

SELECT DATEPART(year, dbo.[Main Table].Date) AS [Date By Year], DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
dbo.[Main Table].[Action Type], SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type] INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
GROUP BY DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]
HAVING (DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)|||Create an MS-Access form with two date controls, an Ok and a Cancel button on it When the user clicks Ok, validate the data in the controls, then use them to call the stored procedure.

-PatP|||The results would have to show in a report, I'm so frustrated, this is what I got so far but its spitting out an error message "Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Main Table'."

SELECT Year(3/01/2001)([Main Table].[Date],'yyyy') AS [Date By Year], [Main Table].[Violation Type], Sum([Main Table].[Loss])
AS [Sum Of Loss], Count([Main Table].[Violation Type])
AS [CountOfViolation Type]
FROM [Main Table]
GROUP BY Year(3/01/2001)([Main Table].Date,'yyyy'), [Main Table].[Violation Type], Year([Main Table].[Date])
HAVING ((Year()([Main Table].Date)=[Please Enter the Year]))
GO|||You'll have to excuse me, but can we go back a few posts to that first query. Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...|||SELECT
DATEPART(year, dbo.[Main Table].Date) AS [Date By Year],
DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type]
INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
WHERE
(DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)
GROUP BY
DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]

Incidently i'm not seeing a need for the inner join above...|||Quick guess :

SELECT
([Main Table].[Date],'yyyy') AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I inherited this database it was an MDB and I'm trying to convert it to an ADP I ran that select statement in the query analyzer and this is the error message i'm gettting

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.|||Hold on, i need some clarity here for piece of mind, to make sure that i'm working with the right thing :

Are you running these queries in MSSQL or MSAccess ?|||Found my error :

SELECT
DATEPART(year,[Main Table].[Date]) AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I am running them in MSSQL, I imported the tables over from Access 2003 onto SQL server. The user set up his reports a certain way and since Jet SQL and SQL Server dont exactly speak the same language I have to redo allot of the queries. Does that make sense??|||Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...the necessity for doing an inner join is not always predicated on returning something from the joined table

sometimes it is just those rows which have a match that you are interested in

example: say you are the HR department and you have an employees table

suppose you needed to store the email address of their parole officer (let's say to email them confirmation of the employee's attendance)

not every employee has a parole officer, check? so you don't keep the parole officer's email in the employees table (it would need to be NULL for most employees), you keep it in a separate table (incidentally, this is another example of proper normalization, it's 4nf i believe)

now you want to produce a list of employee names and departments for all employees who have a parole officer, but you don't actually need the parole officer's email address

what does your query look like?

that's right, an inner join, selecting only from the left table

:)|||See this the query he set up in Access, if you go into the [Main table] down where the field properties are, he has made one of the fields a combo box and the rowsource is that table [Action Type], and Violation Type is another table that also linked to it. Does that make sense??
SELECT DISTINCTROW Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Violation Type], Sum([Main Table].Loss) AS [Sum Of Loss], Count([Main Table].[Violation Type]) AS [CountOfViolation Type], [Action Type].[Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Violation Type], [Action Type].[Action Type], Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and year]));

Migrating from Access 2003 to SQL CE

I've a database in Access 2003 that I want to migrate to SQL CE since Pocket Access is no longer supported.

I've researched extensively this issue to no avail.

Even tried to use the ADS but it's not feasible,

Please, any help is welcomed!

I have written a set of commercial tools that may help you - see my company's site.

|||

I downloaded your DataPort Wizard, however I always got the same error "Connection to device failed"

I was trying to migrate a local database. From Access 2003 to SDF

Please can you give some hints on this?

thanks!

|||Can you please use the site's forum or the support email to continue this conversation?

Migrating from Access 2003 to SQL CE

I've a database in Access 2003 that I want to migrate to SQL CE since Pocket Access is no longer supported.

I've researched extensively this issue to no avail.

Even tried to use the ADS but it's not feasible,

Please, any help is welcomed!

I have written a set of commercial tools that may help you - see my company's site.

|||

I downloaded your DataPort Wizard, however I always got the same error "Connection to device failed"

I was trying to migrate a local database. From Access 2003 to SDF

Please can you give some hints on this?

thanks!

|||Can you please use the site's forum or the support email to continue this conversation?

Migrating From Access 2000 To SQl Server 2000

Hi,

I have developed an application using VB 6 (SP 5), MS Access 2000 and
Crystal Reports 9. I migrated from from access to SQl Server 2000.
This wasn't a problem as i could import all the tables to SQL Server.
Also, i could make VB6 talk to SQl Server.

The problem arsies when i run my application. The sql syntax for
access seems to be different than that for SQL Server. A simple
example being: In access boolean datatype is true/false ,whereas in
SQL Server the boolean equivalent is bit (numerical 1 or 0). These
kind of issues are causing problems and most queries don't run.

Would i need to go and change all the queries in accordance with SQl
Server syntax ,which would be very time consuming or is there any
function which will convert the access datatype into its equivalent
SQl Server datatype??

Any input/thoughts/suggestions would be appreciated.

Thanks
JatinThere is a big problem with ODBC JET Linked tables when it comes to boolean
fields in Access that get turned into BIT fields in SQL Server.

Basically, JET thinks of boolean TRUE as (-1) where SQL thinks of it as (1).
The ODBC driver switches the values for you in Access, so that when you look at
the table it shows (-1)...but if you look at the table in SQL Server it says
(1).

The bottom line is that the ODBC driver does NOT understand what TRUE or FALSE
means, so the short answer is YES, you have to change all of those refrences to
(-1) or (0) respectively.

The standard, quick answer to this problem is to use a find/replace tool (in
NOTEPAD) to change all

= TRUE

to

= -1

and = FALSE

to

= 0

WARNING: This is only true for JET-based ODBC queries. If you create a view on
SQL Server, you'll have to use = 1 for TRUE!!!!

For that reason, I normally use =0 for FALSE, and <>0 for TRUE!!! That way, it
always works.

Another option is to change the BIT datatype on SQL Server to INT
(integer)...that way a (-1) WILL BE STORED by the ODBC driver, and it'll be
consistent.

Hope this helps!

Migrating from Access 2000 to SQL Server

Hey All,
Migrating from Access 2000 to SQL Server 6.5 and am experiencing some difficulty...Using the database utilities -> wizard, use existing database, have File DSN created on Server, select it and the login appears. Enter the login and get error message:

"Reserved error (-1038); there is no message for this error"

I cannot locate any info on. If someone knows anything I would really appreciate your input.

Thanks,
soniaI thought I would add some info for you...

Now, normally when you specify the DSN (which is on a network) it prompts you for the user login but when I am in the wizard I didn't get the prompt, I had to actually use windows explorer, go to the correct server and when I selected it there I get the prompt, and I enter the correct user name/password and then the share folders appear. After I did that, the shared folders appear for me when using the wizard.

I located the error meaning in the reserved error refernces: JET_errBufferTooSmall -1038 Buffer is too small. I don't know what this means I should do... If someone knows anything I would really appreciate your input.

Thanks, Desperately hoping someone knows something
Ssql

Migrating form access to SQL

I am facing problems in migrating my application from Access to SQL.
Few of the fuctions like CBDATE (userdefined functions) are not working.
Please let me know how to overcome this problem.
ThanksHi Manas. I am unaware of a function within Access called CBDATE...is it something you designed and coded (in VBA possibly)? There are a many things you can do in SQL to get the same functionality, but could you post an example code snipet for what that function does?|||

Note he stated it was a user defined function.

You will have to migrate that function into SQL, along with the rest of the database.

|||Did you define the function in the database itself (can you do that in Access?), or in the front end?|||In access you can write functions in VBA that can be called from queries...I'm assuming that's what was being done...|||Ah yes, you are most probably correct. In that case, I presume he will have to write an analagous function in SQL to correct that, with the same name and return type?|||Yeah, I would assume that to be the case...problem would be if the VBA function is doing anything you can't do in T-SQL...then it would be more difficult...

Migrating Data from MsAccess

Hello
I'm trying to migrate the data from Access to MSDE database.
What is the easiest and most efficent method of doing it.
Also the AutoNumber field from Access, how does that move over to MSDE ?
Any help would be appreciated.
Thanks
Hemang
"Hemang Shah" <hemang@.hemang.net> wrote in message
news:YNSdneSqD-fQkGzcRVn-2A@.rogers.com...
> Hello
> I'm trying to migrate the data from Access to MSDE database.
> What is the easiest and most efficent method of doing it.
> Also the AutoNumber field from Access, how does that move over to MSDE ?
> Any help would be appreciated.
> Thanks
> Hemang
>
|||Buy Microsoft SQL Server Developers Version (if you don't already have it)
and use Enterprise Manager and the data import wizard to import your Access
data. Dealing with an AutoNumber column is easy, define your destination
columns as an IDENTITY column and allow identity insert when importing the
data.
Jim
"Hemang Shah" <hemang@.hemang.net> wrote in message
news:YNSdneSqD-fQkGzcRVn-2A@.rogers.com...
> Hello
> I'm trying to migrate the data from Access to MSDE database.
> What is the easiest and most efficent method of doing it.
> Also the AutoNumber field from Access, how does that move over to MSDE ?
> Any help would be appreciated.
> Thanks
> Hemang
>
|||Thanks Jim
Can you import to a MSDE database from Enterprise Manager ? Will that
database still be portable to MSDE ?
Thanks
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:e$s2eN9$EHA.2552@.TK2MSFTNGP09.phx.gbl...
> Buy Microsoft SQL Server Developers Version (if you don't already have it)
> and use Enterprise Manager and the data import wizard to import your
> Access data. Dealing with an AutoNumber column is easy, define your
> destination columns as an IDENTITY column and allow identity insert when
> importing the data.
> Jim
> "Hemang Shah" <hemang@.hemang.net> wrote in message
> news:YNSdneSqD-fQkGzcRVn-2A@.rogers.com...
>
|||hi Hemang
Hemang Shah wrote:
> Thanks Jim
> Can you import to a MSDE database from Enterprise Manager ? Will that
> database still be portable to MSDE ?
>
SQL Server 2000 and MSDE 2000 share the same engine, as they are only
different edition of the same product, so MSDE database can be used by SQL
Server, but SQL Server dbs can be used by MSDE depending on it's limitation
on size...
regards
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea
I used the upsized wizard of ms Access and it created a new database for me
in SQL and also retained the relationships and data, it was a breeze!
Thank You
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:35f9qhF4ispi8U1@.individual.net...
> hi Hemang
> Hemang Shah wrote:
> SQL Server 2000 and MSDE 2000 share the same engine, as they are only
> different edition of the same product, so MSDE database can be used by SQL
> Server, but SQL Server dbs can be used by MSDE depending on it's
> limitation
> on size...
> regards
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql

Migrating an Access Database to MSDE

I'm very new to MSDE, so this may be a basic question but I've been unable
to find a solution.
Is it possible to migrate a database from Access to a Sqldatabase in MSDE?
If so, how? The Access Upsize Wizard doesn't seem to do it?
Thanks in advance.
The Access upsize wizard should move the data but not much more. You can
also use DTS to import Access/JET database. Unfortunately, an "Access" JET
database also contains, forms, reports, macros and other "stuff" that is
lost when you move to MSDE. The Access application itself also uses
techniques that don't migrate well and don't take advantage of SQL Server's
power.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Tom Wing" <biciwing@.optonline.net> wrote in message
news:c6m7d.22354$kq6.12187878@.news4.srv.hcvlny.cv. net...
> I'm very new to MSDE, so this may be a basic question but I've been unable
> to find a solution.
> Is it possible to migrate a database from Access to a Sqldatabase in MSDE?
> If so, how? The Access Upsize Wizard doesn't seem to do it?
> Thanks in advance.
>
sql

Migrating Access XP database to SQL Server 2005 Express

I want to migrate my Access XP database to SQL Server 2005 Express Edition. The Start Menu does not display any migration assistant in SQL Server 2005 Express menu list.

How to migrate it?

hi,

never tried it my self, but have a look at http://www.microsoft.com/downloads/details.aspx?familyid=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en

regards

|||

Access includes a tool called the upsizing wizard that will assist in moving your database to SQL.

Regards,

Mike

Migrating Access XP database to SQL Server 2005 Express

I want to migrate my Access XP database to SQL Server 2005 Express Edition. The Start Menu does not display any migration assistant in SQL Server 2005 Express menu list.

How to migrate it?

hi,

never tried it my self, but have a look at http://www.microsoft.com/downloads/details.aspx?familyid=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en

regards

|||

Access includes a tool called the upsizing wizard that will assist in moving your database to SQL.

Regards,

Mike

Migrating Access to SQL Server

We use MS Access 2000 as our database, but run into lots of proplems. So we decided to research the migration to SQL Server.

I used Access Upsizing utitlity to migrate tables and their data to SQL server very easily, and all the relationships, indecies and other information are converted correctly.

Next is to migrate the queries. I found that to be a pain. Is there any tool out there that can do it for me? Any help is greatly appreciated.

Once that's done, then it's the application itself, but I am not worrying that for now.

Thanks, guysI don't have a tool to recommend.

Have you checked out the part of Microsoft's SQL Server 2000 Resource Kit that deal with migration?Chapter 5 - Migrating Access 2000 Databases to SQL Server 2000. There is a section called "Migrating Access Queries" you might find helpful.

Terri