Access 97 to SQL 2000

Were looking to migrate all our Access 97 databases to SQL 2000, so this is the scenerio. We want to use Access 97 as the front end and SQL 2000 as the backend (Which will hold the tables, stored procedures and security). We have approximataley 400 users using Windows 2000 Pro and all using Novell 5.1 as our file servers. So we are not authenticating to an NT environment but rather to SQL Server, we only hold 2 CPU license and don't really want to use ODBC.

My questions:

What is the best method for Access 97 to connect to SQL 2000?

Can someone provide me an example of the method of doing the connection to the SQL 2000 in Access 97?

I understand it complex to have all your databases seperately for relationships and its easier to have then all in one database, what's your opinion?

Any opinions and ideas would greatly be appreciated, I really don't want to use Access '97 but its not my call. New project and they want me to make it work, I'm good with Access and have never really used SQL.You have to use the linked table to SQL Server via ODBC connection if you want to use ACCESS as fronted end. You also can execute pass-through query to access SQL Server tables too.

If you know how to programming VB to access database, there is no much difference between accessing ACCESS database or SQL Server database. You can use ADO, DAO or OLEDB to connect and access SQL Server from Modules in Access.

In order to handle about 400 users, you can consider to use SQL Server database roles and application role.

Access 2K to MSDE

With Acces 2000.
When i go to migrate a BD in Access 2000 to MSDE.
Acces 2000 gives me an overflow error.
I suppose it is an overfolw error ( in spanish DESBORDAMIENTO)
Is it possible to migrate from acces 2000 to MSDE ?
See How to Convert an Access Database to SQL Server
http://support.microsoft.com/default...EN-US;Q237980. This
paper has links to other papers that detail the errors and issues
invoving using the upsizing wizard.
--Mary
On Thu, 7 Oct 2004 09:24:56 +0200, "Miguel Genol" <mgenol@.teleline.es>
wrote:

>With Acces 2000.
>When i go to migrate a BD in Access 2000 to MSDE.
>Acces 2000 gives me an overflow error.
>I suppose it is an overfolw error ( in spanish DESBORDAMIENTO)
>Is it possible to migrate from acces 2000 to MSDE ?
>

Access 2000/2002 to SQL server

Hi everyones,
Is there a way to migrate an Access 2000 db to SQL Server 2000 or 2005?
thank you
serge
Hi,
You can use DTS to import all the data and create database.
Of course you'll have to setup security afterwards.
Danijel Novak
"Serge Myrand" <info@.softdelirium.qc.ca> wrote in message
news:43DCD052.D0C262F3@.softdelirium.qc.ca...
> Hi everyones,
> Is there a way to migrate an Access 2000 db to SQL Server 2000 or 2005?
> thank you
> serge
>
|||Apart from the dts package, You can use other methods as well:
From within ms-access : open database and go to Tools / Database utilities /
upsizing wizard
this wizard will generate a sql server database for you. You may have to do
some cleaning up in the sql database afterwards;
From SQL 2005 Management studio: connect to the sql server, in object
explorer rightclick on 'management' and choose import data. This opens the
import data wizard wich will create some sort of DTS package for you. I
personally have not tried this yet on sql 2005.
ON SQL 2000 enterprise manager there is a similar import data wizard.
From my own experience the Access wizard makes a more thorough conversion
(with primary keys and foreign keys added to the tables)
You may have to try different approaches on a test SQL server to find the
best result.
Jan D''Hondt
Jade bvba
Belgium
Database and .NET development
<Toto, I''ve got a feeling we''re not in Kansas anymore.>
"Serge Myrand" wrote:

> Hi everyones,
> Is there a way to migrate an Access 2000 db to SQL Server 2000 or 2005?
> thank you
> serge
>
sql

Access 2000 to SQL server

Hi everyones,
Is there an 'easy' way to migrate an Access 2000 db to SQL Server 2000
or 2005?
thank you
serge
Yes, there is a utility for SQL Server 2000, that will import all your
tables and data for you. I forget what it is called but it comes with
enterprise manager. I am pretty sure your views will not be imported
though as they use different languages.
|||menu "Tools->Database Utilities->Upszing Wizard...". Note, after running the
wizard, there may be still some manual modifications is required to the
generated SQL Server DB, depending on how complicated your Access DB is.
"Serge Myrand" <info@.softdelirium.qc.ca> wrote in message
news:43DD164D.420B97F4@.softdelirium.qc.ca...
> Hi everyones,
> Is there an 'easy' way to migrate an Access 2000 db to SQL Server 2000
> or 2005?
> thank you
> serge
>
>
|||Serge Myrand wrote:
> Hi everyones,
> Is there an 'easy' way to migrate an Access 2000 db to SQL Server 2000
> or 2005?
> thank you
> serge
To add to what others have said, 'easy' depends on how complicated your
database is. For instance, your VBA isn't going to get migrated. If
you have forms or reports, they will need some tweaking for sure.
Lastly, the Upsizing Wizard... I have yet to meet a database that it
cleanly migrated. There are always some modifications required.
Unfortunately, the errors that the Wizard reports are counterintuitive.
Good luck.
|||Thank very much
serge
6600Trilby@.gmail.com wrote:

> Yes, there is a utility for SQL Server 2000, that will import all your
> tables and data for you. I forget what it is called but it comes with
> enterprise manager. I am pretty sure your views will not be imported
> though as they use different languages.
|||Thank you very much
serge
Norman Yuan wrote:
[vbcol=seagreen]
> menu "Tools->Database Utilities->Upszing Wizard...". Note, after running the
> wizard, there may be still some manual modifications is required to the
> generated SQL Server DB, depending on how complicated your Access DB is.
> "Serge Myrand" <info@.softdelirium.qc.ca> wrote in message
> news:43DD164D.420B97F4@.softdelirium.qc.ca...
|||There is no report nor view but there are 165 tables
with many relations.
thank you very much
serge
Frank Rizzo wrote:

> Serge Myrand wrote:
> To add to what others have said, 'easy' depends on how complicated your
> database is. For instance, your VBA isn't going to get migrated. If
> you have forms or reports, they will need some tweaking for sure.
> Lastly, the Upsizing Wizard... I have yet to meet a database that it
> cleanly migrated. There are always some modifications required.
> Unfortunately, the errors that the Wizard reports are counterintuitive.
> Good luck.
|||I think he is talking about the upsizing wizard and it comes with Access -
not Enterprise manager. It's a menu option in the Access menu bar.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Serge Myrand" <info@.softdelirium.qc.ca> wrote in message
news:43DDEE1A.B3AD5819@.softdelirium.qc.ca...
> Thank very much
> serge
> 6600Trilby@.gmail.com wrote:
>

Access 2000 to SQL server

Hi everyones,
Is there an 'easy' way to migrate an Access 2000 db to SQL Server 2000
or 2005?
thank you
sergeYes, there is a utility for SQL Server 2000, that will import all your
tables and data for you. I forget what it is called but it comes with
enterprise manager. I am pretty sure your views will not be imported
though as they use different languages.|||menu "Tools->Database Utilities->Upszing Wizard...". Note, after running the
wizard, there may be still some manual modifications is required to the
generated SQL Server DB, depending on how complicated your Access DB is.
"Serge Myrand" <info@.softdelirium.qc.ca> wrote in message
news:43DD164D.420B97F4@.softdelirium.qc.ca...
> Hi everyones,
> Is there an 'easy' way to migrate an Access 2000 db to SQL Server 2000
> or 2005?
> thank you
> serge
>
>|||Serge Myrand wrote:
> Hi everyones,
> Is there an 'easy' way to migrate an Access 2000 db to SQL Server 2000
> or 2005?
> thank you
> serge
To add to what others have said, 'easy' depends on how complicated your
database is. For instance, your VBA isn't going to get migrated. If
you have forms or reports, they will need some tweaking for sure.
Lastly, the Upsizing Wizard... I have yet to meet a database that it
cleanly migrated. There are always some modifications required.
Unfortunately, the errors that the Wizard reports are counterintuitive.
Good luck.|||Thank very much
serge
6600Trilby@.gmail.com wrote:

> Yes, there is a utility for SQL Server 2000, that will import all your
> tables and data for you. I forget what it is called but it comes with
> enterprise manager. I am pretty sure your views will not be imported
> though as they use different languages.|||Thank you very much
serge
Norman Yuan wrote:
[vbcol=seagreen]
> menu "Tools->Database Utilities->Upszing Wizard...". Note, after running t
he
> wizard, there may be still some manual modifications is required to the
> generated SQL Server DB, depending on how complicated your Access DB is.
> "Serge Myrand" <info@.softdelirium.qc.ca> wrote in message
> news:43DD164D.420B97F4@.softdelirium.qc.ca...|||There is no report nor view but there are 165 tables
with many relations.
thank you very much
serge
Frank Rizzo wrote:

> Serge Myrand wrote:
> To add to what others have said, 'easy' depends on how complicated your
> database is. For instance, your VBA isn't going to get migrated. If
> you have forms or reports, they will need some tweaking for sure.
> Lastly, the Upsizing Wizard... I have yet to meet a database that it
> cleanly migrated. There are always some modifications required.
> Unfortunately, the errors that the Wizard reports are counterintuitive.
> Good luck.|||I think he is talking about the upsizing wizard and it comes with Access -
not Enterprise manager. It's a menu option in the Access menu bar.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Serge Myrand" <info@.softdelirium.qc.ca> wrote in message
news:43DDEE1A.B3AD5819@.softdelirium.qc.ca...
> Thank very much
> serge
> 6600Trilby@.gmail.com wrote:
>
>

Access 2000 to SQL server

Hi everyones,
Is there an 'easy' way to migrate an Access 2000 db to SQL Server 2000
or 2005?
thank you
sergeYes, there is a utility for SQL Server 2000, that will import all your
tables and data for you. I forget what it is called but it comes with
enterprise manager. I am pretty sure your views will not be imported
though as they use different languages.|||menu "Tools->Database Utilities->Upszing Wizard...". Note, after running the
wizard, there may be still some manual modifications is required to the
generated SQL Server DB, depending on how complicated your Access DB is.
"Serge Myrand" <info@.softdelirium.qc.ca> wrote in message
news:43DD164D.420B97F4@.softdelirium.qc.ca...
> Hi everyones,
> Is there an 'easy' way to migrate an Access 2000 db to SQL Server 2000
> or 2005?
> thank you
> serge
>
>|||Serge Myrand wrote:
> Hi everyones,
> Is there an 'easy' way to migrate an Access 2000 db to SQL Server 2000
> or 2005?
> thank you
> serge
To add to what others have said, 'easy' depends on how complicated your
database is. For instance, your VBA isn't going to get migrated. If
you have forms or reports, they will need some tweaking for sure.
Lastly, the Upsizing Wizard... I have yet to meet a database that it
cleanly migrated. There are always some modifications required.
Unfortunately, the errors that the Wizard reports are counterintuitive.
Good luck.|||Thank very much
serge
6600Trilby@.gmail.com wrote:
> Yes, there is a utility for SQL Server 2000, that will import all your
> tables and data for you. I forget what it is called but it comes with
> enterprise manager. I am pretty sure your views will not be imported
> though as they use different languages.|||Thank you very much
serge
Norman Yuan wrote:
> menu "Tools->Database Utilities->Upszing Wizard...". Note, after running the
> wizard, there may be still some manual modifications is required to the
> generated SQL Server DB, depending on how complicated your Access DB is.
> "Serge Myrand" <info@.softdelirium.qc.ca> wrote in message
> news:43DD164D.420B97F4@.softdelirium.qc.ca...
> > Hi everyones,
> >
> > Is there an 'easy' way to migrate an Access 2000 db to SQL Server 2000
> > or 2005?
> >
> > thank you
> > serge
> >
> >
> >|||There is no report nor view but there are 165 tables
with many relations.
thank you very much
serge
Frank Rizzo wrote:
> Serge Myrand wrote:
> > Hi everyones,
> >
> > Is there an 'easy' way to migrate an Access 2000 db to SQL Server 2000
> > or 2005?
> >
> > thank you
> > serge
> To add to what others have said, 'easy' depends on how complicated your
> database is. For instance, your VBA isn't going to get migrated. If
> you have forms or reports, they will need some tweaking for sure.
> Lastly, the Upsizing Wizard... I have yet to meet a database that it
> cleanly migrated. There are always some modifications required.
> Unfortunately, the errors that the Wizard reports are counterintuitive.
> Good luck.|||I think he is talking about the upsizing wizard and it comes with Access -
not Enterprise manager. It's a menu option in the Access menu bar.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Serge Myrand" <info@.softdelirium.qc.ca> wrote in message
news:43DDEE1A.B3AD5819@.softdelirium.qc.ca...
> Thank very much
> serge
> 6600Trilby@.gmail.com wrote:
>> Yes, there is a utility for SQL Server 2000, that will import all your
>> tables and data for you. I forget what it is called but it comes with
>> enterprise manager. I am pretty sure your views will not be imported
>> though as they use different languages.
>

Accelerate Sql Express?

Hi,

we're planning to migrate an existing dataset cache mechanism to Sql Server Express. The dataset isn't really able to handle and search one million recordsets any more :-)

Once a day the Cache Sql Database wil be filled with fresh data. In comparison with the dataset takes up to fourty times longer to build the cache on the same machine!! Ok, I expected that i will take longer than creating the objects in a database with all its "overhead" like atomic transactions etc. But not so much.

The current throughput on SQL Server ist nearly 400 inserted records per second in the same table. The dataset stores up to 17.000 of the same data.

I'm wondering about the the following fact. Allthough I'm testing the Mass-Inserts with generating synthetic records in a unlimited for-loop, the server CPU load is allways less than 3 %. For all I care it could be much higher while the nightly process of initilializing the cache. Could there be a unnecessary throttle that limits the amount of inserts per second?

Any ideas how to accelerate the database engine or optimizing bulk-inserts?

P.S. I tested some different options and played around with connection string options like packekt size, Named Pipes connections, recycling the last commando and parameters and so on. Without any significant effects..

Marcus

Could you post how you are doing the inserts are the moment? Are you using BULK INSERT or bcp, or are you doing regular inserts? Also, what is the transaction size you are using, and what are the specifications or your machine, disks, etc.|||Im inserting each record in a single regular insert statement via ADO.NET (2.0) ExecuteComand. As far as I know, buldinsert are only for files available, right?

The Maschine is a 4 x XEON 3,6 Ghz, 32 bit Win2003 Server Sp1, 4GB Ram and a fast SCSI Raid

What is the transaction size? Is this a configurable value? At the moment all values are unchanged
installation defaults.

Thanks
Marcus|||There are several ways to speedup:

1) Use a SqlTransaction in your ADO.NET code, and issue a commit after every N rows that you insert. If you don't do this, a commit will be done for each row, which is very expensive.

2) To get big perf improvements, you should use BulkInsert or BCP to bulk load your data in your database. These methods are much faster than doing individual inserts.

Thanks,

about migrating to 2005 from 2000.

Hi, since havenot done migration yet, my company is planning to migrate from sql 2000 to sql 2005 server. and i am in group for capacity planning. can anybody suggest me the steps i should be really taking in order to migrate to 2005, if anyone has done the it in real world with database up to 50g.
thanks
Dereki would start with running the upgrade advisor. this does not catch everything. one thing i found annoying is that old stored procedures with words in them that became new keywords in 2K5 broke all over the place.