type of database to be used.. 4

[MarQ]

good day, i am a beginner(not so much) in vb6, and have been using MSAccess for my database. lately somebody told me to make a hospital system and i was thinking that there will be lots and lots of data to be stored in the database, so i really need to make my database to be good. My question is...Can I use MSaccess for that? if there are any better software for database, what would i use? can I also use MySQL as my database since its free? I just want to start RIGHT before any serious problem occurs in the process...THank you very much.. suggestions will be greatly appreciated..thank you and more power to all..

 

[fredericofonseca]

No you should NOT use Access.

MySQL is only free if your application is also Free Source. Otherwise you have to pay for it.

Depending on the size of the hospital, and on the number of persons using the system at anyone time you may be able to start with MSDE (SQL Server with some limitations), and then move to the full SQL Server when the users require it.

Options for full DB's are
SQL Server
Oracle
Sybase
Progress
Informix
IBM DB2

Either is fine, each has it's own quircks. Both SQL and Sybase use Transact-SQL, so code that works on one will probably work on the other also. (not always as there are small variances).




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[bjd4jc]

I diddo fredrico.. do NOT use Access for this. I personaly enjoy working with SQL Server. This is probably your best *free* option (MSDE) because there will be NO code changes if you decide to upgrade to a purchased copy of SQL Server.

 

[Alleyopp]

Someone wrote on this topic at microsoft.com Access group..

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

Access Discussion Group -

http://office.microsoft.com/search/redir.aspx?AssetID=XT010911421033&CTT=5&Origin=HA011504841033

It is under Access New Users group..
thread - Why is no one USING the product for small business?

We are using Access as our backend for our Prototye with the front end of VB.. However, there is a noticable slowness and also a limitation of not being able to use stored procedures.. Also, with our user base expanding Access has a supposed limit of 255 users.
My preference is to use MS SQL server, which of course MS made sure it worked well with VB,or at least if not it is considered a feature... I have used Oracle (Way expensive) with VB and we had problems using ADO with it..

Good Luck

 

[dilettante]

While there is a lot of heat about the number of users a Jet (not Access) database can support, for many applications the real problem is the 2GB MDB-file size limit. Of course MSDE (and SQL Server Express, see SQL Server 2005 Express Edition Beta 2) also have limits on the amount of activity they support as well as a 2GB database limit.

The important difference as stated above by others though is the ease of a later port to full SQL Server once the need arises. There are new administration issues to consider though.

One should keep in mind that typical VB6 2-tier applications will run into concurrent-user limitations sooner with early MSDE versions than with Jet databases. This was addressed to some extent in MSDE 2000, which throttles based on concurrent operations rather than concurrent connections.

Then, there is a virtual governor that will address the performance of the system while looking into the "five concurrent users" limitation. Another thing to keep in mind here is, we're talking about a five concurrent workload throttle on the engine itself, which means if there are more than five concurrent batches of queries being executed, then the sixth user, the next connection that hits the server concurrently, will cause the performance to degrade.

So it is not five users who are connected to the system. It is five concurrent queries running on the engine. So if there are multiple users and all the users are idle, it should not affect the workload governor at all. It should not invoke the workload governor at all. So that's one important distinction to make there.

Then, there is a database size limitation on the MSDE, which is 2 GB. Again, another important thing there is, it's not 2 GB of transaction log we're talking about. It's the limitation only on the size of the data that you put into the database, which are the .mdf and .ndf files, the primary data file and the secondary data file. There is no limit on the transaction log (.ldf files) as such.

Again, since this 2 GB applies to the data files, for the transaction files, we should make sure that proper maintenance is in place in terms of the transaction log size. We should look into the recovery models and make sure that the transaction log is automatically truncated or being backed up regularly, so that it does not grow to a very big size. That can be done either by allowing SQL Server to automatically truncate the log using the correct recovery model, like a simple recovery model, or by putting scheduled jobs and other information in place to back up the transaction log regularly.

More: http://support.microsoft.com%2Fservicedesks%2Fwebcasts%2Fen%2Fwc021402%2Fwct021402.asp]Microsoft Data Engine: Deploying and Troubleshooting MSDE 2.0[/url]. The number "5" above is bogus, internally it is really 8 I believe. But MSDE itself will perform "hidden" operations that compete with your front-end application for the pool of 8 operations, so Microsoft tends to quote 5 instead.


The point of it all is that there are some non-trivial issues to look into when moving from Jet-based applications to MSDE or SQL Server Express. It isn't the "no brainer" some might lead you to believe. It is optimized for 3-tier applications such as web applications in ASP or similar technologies.

MSDE is a very important tool for the VB developer to learn to understand though. It most definately has its place, and SQL Server Express will make the move even easier by addressing a number of administration and deployment issues for smaller applications. Just keep in mind that VB5/6 developers are second-class citizens in SQL Server Express' world: it is targeted at Visual Studio 2005 (.Netsville) and may end up only being available via VS 2005 SKUs instead of "free" like MSDE. This may mean MSDE 2000 is your best bet, so learn it and grab up whatever docs and articles on it you can before they disappear.

The best info here at Tek-Tips may be forum958, Microsoft: Access Project (ADP) Forum.

 

[dilettante]

I forgot to add that so far everything says SQL Server Express will be free. It also raises the database size limit to 4GB.

 

[dilettante]

The other (obvious?) point is that a multi-user MSDE applications requires a database server now, not just a file server as you might have used with your Jet solutions.

Hmm, what other "transitioning to MSDE" issues am I leaving out?

Anybody seen a good treatment of this topic by itself anyplace?

I found Microsoft Data Engine (MSDE) for Microsoft Visual Studio 6.0: An Alternative to Jet for Building Desktop and Shared Solutions for MSDE 1.0, and it makes a few notes re. MSDE 2.0 (2000) as well.

 

[MarQ]

If ever i use let's say..SQL server..is the coding for making the program the same or almost the same when using MSAccess? is using MSSQL server hard?...i mean how to manipulate it..im just not so much confident that i can handle MSSQL server at my stage in programming, but as you all said..do NOT use MS ACcess, Since one time.. i was able to open a database using SQL server and it doesnt look like an MSAccess that more or less easy to handle...and i do not know what to do with it...any comments? thanks in advance to all who are helping me out here.

 

[bjd4jc]

If you use enterprise manager to manage the database then you have a GUI that is similar in nature to Access.

If you're familiar with Access the you can also use Access to manage the database. All of the objects are still stored in SQL Server but managed from Access. This is called an Access Data Project (.adp). It allows you to connect to MSDE/SQL Server and manage it. You would connect in your code (VB) to the actual SQL Database and not Access. There are some limitations of managing through access but for the most part it will work for you.

Don't be intimidated by SQL Server in your VB Coding. IMO the largest difference lies in the connection string and the use of stored procedures (although you can have an sp in Access, most ppl don't know about this). There are many more differences that you'll find along the way but nothing that should stop your development. If you ever come to a hang up, you know this forum will be here to walk you through...

 

[MarQ]

Thanks a lot for the valuable help..i will do what you all suggested...and will keep in touch for the development..again..thank you all and god bless.

 

[PatrickIRL]

Apologies for jumping in but I thought MSDE was "free". However judging by some of the replies here it appears it ain't "free". What constitues "free" in this context??

Patrick

 

[fredericofonseca]

MSDE IS FREE in that you don't need to buy the software, nor do you need to sell user licenses to your final customer.

But as it is a server program it means that you can not have a Novel or a Unix server sharing the database.
This means either a Windows server machine or one of your users PC's acting like a server.

In terms of administering the MSDE server there are some tools around like TOAD, SmartDBA Cockpit and others that can do the same work (or almost) as SQL Server Enterprise Manager



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[dilettante]

Over the life of the MSDE product this has changed slightly, but for MSDE 2.0 (2000) the terms are spelled out at Appropriate Uses of MSDE FAQ.

Basically MSDE 2000 is free to anyone for his/her own use. It is also possible to obtain rights to redistribute MSDE 2000 with your applications in a number of ways, most commonly by licensing certain newer Microsoft development tools (VB6/VS6 are not included in this list). Users of other tools like VB6 or even 3rd party or homebrew tools (or just anybody) can register with Microsoft to obtain free redistribution rights as well.

The article also states that a VS6 license conveys redistribution rights for MSDE 1.0, an earlier product.

SQL Server Express is not yet released, but the betas have been free and licensing/redist terms may come to be similar to those of the earlier MSDE products.

 

[PatrickIRL]

Thanks for the info. I had MSDE in mind for a project, think I might move to MySQL instead.

Patrick

 

[dilettante]

I don't have a clue about MySQL. Is it a "file based" system like Jet or server-based like MSDE? I assume the latter, but as I said I've never even looked into it.

 

[shannanl]

Marq,

You did not say what kind of hospital system you are designing. I am curious at to what kind of app it is. Is it a full blow hospital information system or just a small app. for HR, etc? I also work at a hospital and write apps for them. I started using Access but quickly moved to SQL Server when my apps grew. Don't be intimidated by SQL Server. You will catch on quickly. Let me know what you will be designing. Maybe I can be of some help.

Shannan

 

[MarQ]

shannanl, its supposed to be a patient information connected to the inventory, and billing. This means that when the patient starts to be admitted, he fills-up the info and then while admitted, every medicine he gets will be monitored in the inventory from the pharmacy and nurses. At any time, if a patient wants to know his expenses, summarized report or daily expenses report can be given.So i guess i need to make this in a modular form..is this possible and then later connect it all..hope to talk with you more.. thanks