Database Development FAQs & Tutorials [updated May 13 2011]
Database Development FAQ
The following is a list of frequently asked questions for working with databases, including how to use databases from VB, and creating/designing/managing databases.
Sections in this Page
For all languages/databases:General
Database Design
SQL (not SQL Server, but the query language for all database systems!)
Installation
Programming language specific:VB.Net (VB2002 and later) (includes "VB Express")
Classic VB (VB6 and earlier) - ADO
Classic VB (VB6 and earlier) - Data-bound controls (eg: Data Control/ADODC/Data Environment)
Database system specific:
Note that if you have a question which isn't answered here, it may be in another of our FAQ threads, such as the Classic VB FAQs, or the Office Development FAQs
If you can think of a "frequent" question (and preferably an answer to it as well) that you think should be added to these FAQs, please see this thread.
____________________________________________________________
Recent Additions____________________________________________________________
- May 13 2011 : [General] How can I run an ADODB based program that was compiled on Windows 7 SP1?
- Mar 8 2011 : [General] How to connect to a database on the internet
- Mar 29 2010 : [General] Why dont AutoNumber/Identity/etc re-use deleted numbers?
General
- What does this error mean, and how do I fix it?
- How can I run an ADODB based program that was compiled on Windows 7 SP1?
see: http://support.microsoft.com/kb/2517589
- How can I use a database in my program?
For Classic VB (VB6 and earlier) see the Classic VB - ADO section.
For VB.Net (VB2002 or later, and Express) see VB.Net section.
- How can I get the auto-generated AutoNumber/Identity value for the record just added?
- Why don't AutoNumber/Identity/etc re-use deleted numbers?
- Which database system should I use?
- How to connect to a database on the internet
- What is an Index, and how does it help with searching/sorting?
See the replies in this thread (especially posts #2 and #4).
- Where can I find a tool to query a database?
See Sybase database edit tool in the UtilityBank forum.
Database design
- What is Database Normalization?
Why multiple tables are a good thing, and how to set them up
- What names should I NOT use for tables/fields/views/stored procedures/...?
(includes listings of Reserved Words)
- Why is it not recommended to store Date or Time values in a Text/Char based data type?
(note that there are some exceptions, but read it before making that decision)
SQL
- Where can I find a tutorial/reference guide for SQL?
- General (includes explanations and examples of several types of SQL statements)
- multi-table queries
- complex Updates
- Reference for SQL with Access: Tutorial / Reference / more info (also see this article for how to read data from other databases)
- Reference for SQL with SQL Server: Reference
- Reference for SQL with MySQL: Reference
- Why should I use Parameters in my program instead of putting values directly into my SQL string?
(aka "how do I protect against SQL Injection attacks")
- How do I use values (numbers, strings, dates) in SQL statements?
- How do I put the ' character into an SQL string?
- How can I put the value of a variable/control into my SQL statement?
- How can I find out why my SQL statement isn't working?
- Stored procedures: How can I add optional parameters to a Where clause, without building the statement in a string?
- How can I work with parent-child data, where a child can also be a parent? (SQL Server example)
Installation
- Where can I get MDAC, or Drivers/Providers for Sql Server/Access/Excel?
- Where can I get a Driver/Provider for MySql?
See apt section of the MySQL web site, here.
- How can I Re-install MDAC 2.8 RTM (Window XP SP2)?
