 |
 |
|
09-11-2002, 04:45 AM
|
 |
Lost Soul
Super Moderator
* Guru *
|
|
Join Date: May 2001
Location: Vorlon
Posts: 18,883
|
|
Frequently Asked DATABASE Questions
Last edited by loquin; 09-05-2007 at 10:10 AM.
Reason: Added a link.
|
09-21-2002, 12:26 AM
|
|
Lost Soul
Super Moderator
* Guru *
|
|
Join Date: May 2001
Location: Vorlon
Posts: 18,883
|
|
|
09-25-2002, 07:04 AM
|
 |
Disillusioned Code Poet
Retired Moderator
* Guru *
|
|
Join Date: Apr 2002
Location: Tennessee, USA
Posts: 12,808
|
|
__________________
Laura
Ita erat quando hic adveni.
Last edited by lebb; 09-23-2005 at 05:08 PM.
Reason: Removed dead link
|
02-14-2003, 11:04 PM
|
 |
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
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 07:34 AM.
|
04-07-2003, 08:39 AM
|
|
Iron-Fisted Programmer
Retired Moderator
* Guru *
|
|
Join Date: Jul 2001
Location: Fayetteville Arkansas USA
Posts: 18,127
|
|
|
06-21-2003, 11:33 AM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
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
|
11-27-2003, 03:54 AM
|
|
Lost Soul
Super Moderator
* Guru *
|
|
Join Date: May 2001
Location: Vorlon
Posts: 18,883
|
|
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 03:25 AM.
Reason: Tidied up links and headings
|
12-10-2003, 01:17 PM
|
|
Disillusioned Code Poet
Retired Moderator
* Guru *
|
|
Join Date: Apr 2002
Location: Tennessee, USA
Posts: 12,808
|
|
JET Architecture Automation for Access
__________________
Laura
Ita erat quando hic adveni.
Last edited by Timbo; 03-07-2007 at 03:13 AM.
Reason: Updated link
|
01-02-2004, 07:09 AM
|
|
Disillusioned Code Poet
Retired Moderator
* Guru *
|
|
Join Date: Apr 2002
Location: Tennessee, USA
Posts: 12,808
|
|
__________________
Laura
Ita erat quando hic adveni.
|
01-27-2004, 02:29 PM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
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! |
__________________
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 08:03 PM.
Reason: added files to post
|
08-16-2004, 12:19 PM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
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 03:32 PM.
Reason: update link
|
08-26-2004, 12:31 PM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
__________________
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 01:49 AM.
|
08-27-2004, 04:48 PM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
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 01:58 PM.
|
09-10-2004, 05:58 AM
|
|
Steppe Walker
Retired Moderator
* Expert *
|
|
Join Date: Jul 2002
Location: Ukraine/Russia/Belgium
Posts: 7,227
|
|
__________________
"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 09:38 AM.
Reason: spelling
|
10-21-2004, 05:06 PM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
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 01:25 PM.
|
12-07-2004, 11:32 AM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
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 08:47 AM.
|
02-19-2005, 09:05 PM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
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 01:41 AM.
|
05-04-2005, 01:27 PM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
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
|
06-22-2005, 09:42 AM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
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 extreme experts 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
|
10-10-2005, 04:46 PM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,378
|
|
MSDE - Micro soft 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 09:26 AM.
Reason: fix broken links
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
|
|
 |
|
