Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions
Go Back  Xtreme Visual Basic Talk > > > Frequently Asked DATABASE Questions


Closed Thread
 
Thread Tools Display Modes
  #1  
Old 09-11-2002, 05:45 AM
Flyguy's Avatar
FlyguyFrequently Asked DATABASE Questions Flyguy is offline
Lost Soul

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Vorlon
Posts: 19,164
Exclamation Frequently Asked DATABASE Questions



Last edited by loquin; 09-05-2007 at 11:10 AM. Reason: Added a link.
  #2  
Old 09-21-2002, 01:26 AM
Flyguy's Avatar
FlyguyFrequently Asked DATABASE Questions Flyguy is offline
Lost Soul

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Vorlon
Posts: 19,164
Default

Some Microsoft pages:
  #3  
Old 09-25-2002, 08:04 AM
lebb's Avatar
lebbFrequently Asked DATABASE Questions lebb is offline
Disillusioned Code Poet

Retired Moderator
* Guru *
 
Join Date: Apr 2002
Location: Tennessee, USA
Posts: 12,808
Default

__________________
Laura

Ita erat quando hic adveni.

Last edited by lebb; 09-23-2005 at 06:08 PM. Reason: Removed dead link
  #4  
Old 02-15-2003, 12:04 AM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default PostgreSQL.org

FYI we've mentioned PostgreSQL as a possible low-cost Database Server option with a good feature list - here's their site; also a link directly to documentation. Note that if you like the looks of it, several companies are selling implementations (which include support) including Red Hat.

PostgreSQL

PostgreSQL Documentation

(a printed set of documentation overflows a three-inch binder, btw.)

PostgreSQL Wikipedia article

A very good "Intro to PostgreSQL", although a bit dated, (v7.x) and slanted towards linux users, is still useful for those who are thinking about trying PostgreSQL.
Edit: Update
Native Win32 PostrgreSQL is now available. v8.0 or greater (Now at v8.2.5) is available for download at the above website.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 11-15-2007 at 08:34 AM.
  #5  
Old 04-07-2003, 09:39 AM
ThinkerFrequently Asked DATABASE Questions Thinker is offline
Iron-Fisted Programmer

Retired Moderator
* Guru *
 
Join Date: Jul 2001
Location: Fayetteville Arkansas USA
Posts: 18,127
Default

Extensive tutorial and demonstration program on using VB6/ADO with
SQL Server/MSDE and stored procedures.
http://www.xtremevbtalk.com/show...threadid=71581
__________________
Posting Guidelines
  #6  
Old 06-21-2003, 12:33 PM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

Optikal has placed an ADO Tutorial in the Tutors Corner forum.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown
  #7  
Old 11-27-2003, 04:54 AM
Flyguy's Avatar
FlyguyFrequently Asked DATABASE Questions Flyguy is offline
Lost Soul

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Vorlon
Posts: 19,164
Default Miscrosoft Access - multi-user topics


Edit by loquin: Comment:The MS article is very good, but they're (IMO) wildly optimistic in their estimate of 25-50 concurrent users supported by a shared file .mdb. In fact, MSDE was throughput throttled at 5 (v1) and later 8(v2) concurrent connections to simulate Access' performance.

Last edited by Timbo; 03-19-2007 at 04:25 AM. Reason: Tidied up links and headings
  #8  
Old 12-10-2003, 02:17 PM
lebb's Avatar
lebbFrequently Asked DATABASE Questions lebb is offline
Disillusioned Code Poet

Retired Moderator
* Guru *
 
Join Date: Apr 2002
Location: Tennessee, USA
Posts: 12,808
Default JET Architecture Automation for Access

__________________
Laura

Ita erat quando hic adveni.

Last edited by Timbo; 03-07-2007 at 04:13 AM. Reason: Updated link
  #9  
Old 01-02-2004, 08:09 AM
lebb's Avatar
lebbFrequently Asked DATABASE Questions lebb is offline
Disillusioned Code Poet

Retired Moderator
* Guru *
 
Join Date: Apr 2002
Location: Tennessee, USA
Posts: 12,808
Default

A nice article on how ADO uses cursors (also see Thinker's link above re: ADO/stored procedures for details on this subject):
http://www.sqlmag.com/Articles/Index...leID=5913&pg=1

Thanks to Shurik12 for this link.
__________________
Laura

Ita erat quando hic adveni.
  #10  
Old 01-27-2004, 03:29 PM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

There's a very nice CBT (Computer Based Training) module (flash) on Referential Integrity at the PostgreSQL site. The information provided is general enough to be useful on any database which truly supports R.I.

In addition, a handy Sequences flash tutorial is also available. For those unfamiliar with PostgreSQL (or Oracle) sequences are a means for generating guaranteed unique keys for a field (or fields) in your database. They can be considered as equivalent to AutoNumbers (MS Acess,) or Identities (SQL Server,) but are more flexible than either.

EDIT: Both of these tutorials are now available as links in this post!
Attached Files
File Type: swf ReferentialIntegrity.swf (214.4 KB, 17 views)
File Type: swf Sequences.swf (291.6 KB, 10 views)
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by webbone; 12-11-2008 at 09:03 PM. Reason: added files to post
  #11  
Old 08-16-2004, 01:19 PM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

A common question concerns migrating an Access database to a SQL Server database. Refer to this MS page. While this article deals with upsizing Access data file to SQL Server 7 databases, the techniques discussed are all valid for upsizing to SQL Server 2000 as well. Keep in mind that while MSDE includes DTS, it doesn't include a GUI front end like Enterprise manager nor does it include a graphically based Query Analyzer.

However, serviceable low-cost or even free add-on tools, such as MSDE Admin and Query Express, provide much of the same functionality. Another admin tool, free for personal use, is available at White Bear Consulting. The White Bear free download does some of the same things that MSDE Admin does, but in a different manner. It's task scheduling is much better, in fact, and IMO, should be downloaded specifically for that purpose.

About a year ago, I downloaded a free version of a database administrator called DbaMgr2K, which at that time, had some significant bugs. However, I downloaded it recently, and it seems to have all the bugs ironed out, and it works very nicely. I can recommend it to you now. It should do most of what you need to do, as it supports adding tables graphically, in a similar fashion to Access or Enterprise Manager. Query Express still has the easiest to use query tool, and the White Bear tool has the best scheduling outside of Enterprise Manager. I would recommend downloading these three free tools. MSDE Admin, the only tool on this list that is not free, has probably the best means of updating server security settings, and is still a good choice. It WILL require you to learn more SQL than the DbaMgr2K, however. (But, that is really not a bad thing...)

*****
Reference links - Upgrading from MS Access to SQL Server/MSDE

Access --> SQL Server: Differences between Access and SQL Server?

Access --> SQL Server: How to Migrate from Access to SQL Server 2000 (aspfaq.com)

Access --> SQL Server: How to Migrate from Access to SQL Server 2000 (MS TechNet)

Access --> SQL Server: Moving Your Access 2002 Database to SQL Server (MSDN)

Access --> SQL Server: Notes on upsizing to SQL Server

Edit by loquin: the white bear consulting website seems to be down permanently; I've added a direct link to their free utility instead.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 02-19-2008 at 04:32 PM. Reason: update link
  #12  
Old 08-26-2004, 01:31 PM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

A common thread in many posts here deals with MS Access and it's succeptability to file corruption. Here are a series of articles from TechRepublic that discuss the issue:

Why does it Happen

How can I make it STOP

How do I Fix it when it does happen
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 09-19-2005 at 02:49 AM.
  #13  
Old 08-27-2004, 05:48 PM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default SQL Tutorial

Interestingly, one of the very first posts in the Tutors Corner is a link to a pretty thorough
SQL language tutorial.

A few more useful Tutorial/Help sites for SQL are

Finally, a VERY handy reference for anyone working with SQL, but especially for someone who is knowledgable in at least one SQL dialect, and who needs to expand their knowledge to another version, is the reference book, SQL in a Nutshell.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 04-24-2007 at 02:58 PM.
  #14  
Old 09-10-2004, 06:58 AM
Shurik12Frequently Asked DATABASE Questions Shurik12 is offline
Steppe Walker

Retired Moderator
* Expert *
 
Join Date: Jul 2002
Location: Ukraine/Russia/Belgium
Posts: 7,227
Default

In my view a decent source (to start with) for those who's interested in using XML in VB/ASP:
http://www.topxml.com/xml/articles/xmlvbasp/
__________________
"A diaper is not like a computer that makes satisfying burbling noises from time to time, hinting at great inner complexity." Malcolm Gladwell

"I'm sitting here completely surrounded by no beer." Onslow, 'Keeping up appearances'

Last edited by loquin; 02-07-2006 at 10:38 AM. Reason: spelling
  #15  
Old 10-21-2004, 06:06 PM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

IF you have database specific questions that we cannot answer here, a good place to check for answers is another of the iNet interactive sites: dbForums Whereas our focus is centered on Visual Basic, with some database support, their focus is almost exactly reversed.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 05-04-2005 at 02:25 PM.
  #16  
Old 12-07-2004, 12:32 PM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

Here's a few useful tips:

When working with ADO, it's often useful to trap your errors using an error handler, rather than let your app crash, or even worse, ignoring any errors that may be raised. Here's an ADO Error Code List.

Since DAO has been obsoleted by Microsoft for over 6 years, and most new development uses ADO, it can be a good idea to port your project from DAO to ADO. This Microsoft link points to an article which outlines how to port a project from DAO to ADO.

And, despite the advantages of porting your app to ADO, if you still need to locate a DAO error list, a quick google search located the following: A copy of the DAO error file from Microsoft's DAO SDK.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 12-08-2004 at 09:47 AM.
  #17  
Old 02-19-2005, 10:05 PM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

Now that the good folks at PostgreSQL have released their first native Win32 version of PostgreSQL (v8.0), more and more folks will be looking to interface to it from VB. The following is an example of a DSN-Less connection string. Obviously, you'll need to adjust the server name, Database name, User ID, and Password to fit your specific application.
Edit by loquin: The folks at PostgreSQL.Org are now up to release 8.3

Code:
Const strPG As String = "Driver={PostgreSQL}; Server=localhost; PORT=5432; Database=test; UID=UserName; PWD=YourPassword"
or
Code:
Const strPG As String = "Driver={PostgreSQL Unicode}; Server=localhost; PORT=5432; Database=test; UID=UserName; PWD=YourPassword"
or
Code:
Const strPG As String = "Driver={PostgreSQL ANSI}; Server=localhost; PORT=5432; Database=test; UID=UserName; PWD=YourPassword"
Code:
Const strPG As String = "Provider=PostgreSQL OLE DB Provider;Password=YourPassword;User ID=UserName;Data Source=localhost;Location=DefaultDatabaseName"
The second and third examples uses the latest version of the PostgreSQL ODBC drivers, which are UNICODE or ANSI specific, respectively. Normally, you would use the UNICODE driver.

The fourth option is the to use the OleDB provider.

If the server is not local, replace localhost with the IP address of the host, or the server name.

If your username or machine is defined as a trusted connection in the pg_hba.conf configuration file, you may dispense with the password portion of the connection string.

Note: One quirk of PostgreSQL is case handling of object names (table/schema/database/field); By default, it uses lower case; if upper case is used at all in a name, the object name(s) must be quoted.

In addition, I've located a good online e-book. Although it's specific to v7.x, most of the information provided is applicable to v8. Of course, if you've installed the Windows version, some of the linux utilities may not apply. In any case, be sure to download the version 8 documentation (PDF.) The official documentation is some 1500 pages in length, however, so be sure to use the Table of Contents and index!

Frank Wiles has published a very well-written tutorial titled http://revsys.com/writings/postgresql-performance.html. Recommended.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 01-28-2007 at 02:41 AM.
  #18  
Old 05-04-2005, 02:27 PM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

Here's a nice discussion dealing with database selection - In particular, when would you choose a desktop database over a server product, and Visa Versa.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown
  #19  
Old 06-22-2005, 10:42 AM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

A common question asked is: Should I (or How do I) store my images in my [SQL Server/Access/Oracle/MySQL...] database, or just store pointers (paths) to the images that are stored in the file system. The folks at extremeexperts do a pretty good job of explaining why to choose one approach over the other here.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown
  #20  
Old 10-10-2005, 05:46 PM
loquin's Avatar
loquinFrequently Asked DATABASE Questions loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

MSDE - Microsoft Desktop Engine, is a "lite" version of SQL Server, which supports most of SQL Server's features. MSDE was designed to provide a true client-server based database engine for development and deployment of "low-end" database applications. By providing a free database server that is one hundred percent upgradeable to SQL Server with no code changes required (and which uses the same database engine as SQL Server) Microsoft allows developers to avoid the real shortcomings of distributing data applications based on MS Access, which can be challenging to upgrade or to scale up. The limitations of MSDE (File size, workload governer) were imposed so that the performance of MSDE would roughly approximate that of MS Access in a networked environment.

The older version of MSDE, based on SQL Server 7, included a connection limit governer - when the number of concurrent connections exceeded five, the server engine began slowing down. The newer version of MSDE, based on SQL Server 2000, employs a workload governer, where processing slows down when the number of concurrent workloads exceed eight. No data is ever lost, or queries not processed - they just slow down. One of the other limitations of SQL Server is it's database file size - it is limited to 2 GB per database file. Like Access, however, you can use multiple database files per desktop.

MSDE is not licensed to allow the use of Enterprise Manager, the GUI development front end, unless you have purchased SQL Server. Several free or low cost alternatives do exist, though.

The latest version of MSDE is called SQL Server Express. It removes the workload governer, and increases the maximum file size to 4 GB.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 12-20-2006 at 10:26 AM. Reason: fix broken links
Closed Thread


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions
 
Frequently Asked DATABASE Questions
Frequently Asked DATABASE Questions
 
-->