MSDE Newbie

[JimmieC]

I am really confused. I created a database in Access 2000, split it and then upsized the backend to MSDE, and linked to the upsized tables. The linked tables now show a globe icon next to them. Also, local tables were created on the backend. I will be putting the backend on a NT server and the front end on about four workstations. Each workstation has a copy of Access 2000. Here's my confusion. The tables that were upsized look the same. The datatypes are the same including autonumber fields. Have I created a MSDE backend? Do I need to install MSDE on each workstation? Under MSDE do all the queries reside on the front end? What am I missing here-everything works great, but I can't believe I've really connected to a MSDE back end. So far it's way to easy. Appreciate all the help and advice I can get.

Thanks.

jimmiec

 

[Pat Hartman]

MSDE is a stripped down version of SQL Server. Therefore, your data is no longer in a self contained file that can easily be moved from location to location. You should also be aware that MSDE is deliberately crippled so that once you get more than 4 concurrent users, it slows down dramatically. Otherwise why would anyone spend the money to buy SQL Server?

Sorry to say I have no clue as to what has to be installed where to run your application. I am going to guess that MSDE since it IS SQL server, works like SQL Server for multiple users (I have it running locally on my home PC but I've never used it on a network). Therefore, MSDE needs to be installed on the server and be an active running task so that it can process requests. You would need some way to start it automatically whenever the server reboots. Then on each of your client PC's, you would need to create a DSN so that the local PC can "find" the physical database containing the tables.

You may or may not have noticed yet that you cannot alter the MSDE tables via the Access GUI anymore. You will only be able to alter them through DAO, ADO, or SQL DDL queries. You can create an .adp that will give you some control over the physical tables but that is not a very good interface. Download an eval version of SQL Server but only install the Enterprise Manager part. This software will give you full control over the physical databases but if you are not a DBA you may have a big learning curve ahead.

My advice, either convert to SQL server if that is an option for you or go back to Jet.

 

[JimmieC]

Another learning curve.

MSDE is a stripped down version of SQL Server. Therefore, your data is no longer in a self contained file that can easily be moved from location to location. You should also be aware that MSDE is deliberately crippled so that once you get more than 4 concurrent users, it slows down dramatically. Otherwise why would anyone spend the money to buy SQL Server?

Sorry to say I have no clue as to what has to be installed where to run your application. I am going to guess that MSDE since it IS SQL server, works like SQL Server for multiple users (I have it running locally on my home PC but I've never used it on a network). Therefore, MSDE needs to be installed on the server and be an active running task so that it can process requests. You would need some way to start it automatically whenever the server reboots. Then on each of your client PC's, you would need to create a DSN so that the local PC can "find" the physical database containing the tables.

You may or may not have noticed yet that you cannot alter the MSDE tables via the Access GUI anymore. You will only be able to alter them through DAO, ADO, or SQL DDL queries. You can create an .adp that will give you some control over the physical tables but that is not a very good interface. Download an eval version of SQL Server but only install the Enterprise Manager part. This software will give you full control over the physical databases but if you are not a DBA you may have a big learning curve ahead.

My advice, either convert to SQL server if that is an option for you or go back to Jet.

Thanks for responding.

what does it mean SQL server is "embedded" in Access 2000. Why does MSDE need to be resident on the server if sql server is embedded in Access 2000.
Seems as though life is full of learning curves!

Thanks,

JimmieC

 

[JimmieC]

Another learning curve.

MSDE is a stripped down version of SQL Server. Therefore, your data is no longer in a self contained file that can easily be moved from location to location. You should also be aware that MSDE is deliberately crippled so that once you get more than 4 concurrent users, it slows down dramatically. Otherwise why would anyone spend the money to buy SQL Server?

Sorry to say I have no clue as to what has to be installed where to run your application. I am going to guess that MSDE since it IS SQL server, works like SQL Server for multiple users (I have it running locally on my home PC but I've never used it on a network). Therefore, MSDE needs to be installed on the server and be an active running task so that it can process requests. You would need some way to start it automatically whenever the server reboots. Then on each of your client PC's, you would need to create a DSN so that the local PC can "find" the physical database containing the tables.

You may or may not have noticed yet that you cannot alter the MSDE tables via the Access GUI anymore. You will only be able to alter them through DAO, ADO, or SQL DDL queries. You can create an .adp that will give you some control over the physical tables but that is not a very good interface. Download an eval version of SQL Server but only install the Enterprise Manager part. This software will give you full control over the physical databases but if you are not a DBA you may have a big learning curve ahead.

My advice, either convert to SQL server if that is an option for you or go back to Jet.

Thanks for responding.

What does it mean SQL server (MSDE)is "embedded" in Access 2000. Why does MSDE need to be resident on the server if MSDE is embedded in Access 2000.

Seems as though life is full of learning curves!

Thanks,

JimmieC

 

[Pat Hartman]

MSDE is not "embedded" in Access. It is a separate program that is included on the A2K and newer installation CD. It is not installed as part of the normal installation or even a custom installation. It is a totally separate install.

You should notice that when you installed MSDE, you ended up with a new icon in your tray. It looks like a PC Tower case with a circle in front of it. The circle contains a green square when the MSDE server is running and a red square when the MSDE server is not running. If you open your db and try to access a table when the server is not running, you will get an error. The way real database servers work is that they have continuously running "listening" processes. When you do something in your application that requires data, Jet evaluates the query and sends a request to the database server "listining" program. That program takes the request, processes it, retrieves the data, and returns a recordset (in the case of a select query) to the requesting program. If Jet tries to contact the db server and it's "listining" program is not running, Jet will return an error.

 

[JimmieC]

I read it wrong.

MSDE is not "embedded" in Access. It is a separate program that is included on the A2K and newer installation CD. It is not installed as part of the normal installation or even a custom installation. It is a totally separate install.

You should notice that when you installed MSDE, you ended up with a new icon in your tray. It looks like a PC Tower case with a circle in front of it. The circle contains a green square when the MSDE server is running and a red square when the MSDE server is not running. If you open your db and try to access a table when the server is not running, you will get an error. The way real database servers work is that they have continuously running "listening" processes. When you do something in your application that requires data, Jet evaluates the query and sends a request to the database server "listining" program. That program takes the request, processes it, retrieves the data, and returns a recordset (in the case of a select query) to the requesting program. If Jet tries to contact the db server and it's "listining" program is not running, Jet will return an error.

Pat,
Thanks again for your response. In "Special Edition Access 2000" ,Que, page 945, it reads SQL Server 7.0 comes in Embedded (MSDE), Desktop, Standard and Enterprise Editions. I read (or misread) that to "mean" that SQL was embedded in Access2000. Now I understand the confusion.

Thanks for your help,

JimmieC