Tutorials and Tips

[Beacon]

I'm hoping people will add to this and that the thread will stay at the top as John said.

Here for all to see is my tutorial on storing Images in an Access Database. Last time i'm posting it.

Also here's the link to the latest VB Service Pack it's big so only get it if really needed and you have a good connection.

SP5

Also i've started an FAQ with links to posts for answers.
PM me to have an FAQ included or answered.

Ciao
Beacon

DATABASE - Quick Tips\FAQ

General

Q) How can i show a progress bar when retrieving records from a database?
A) This is using an ADODB Recordset(RS) and a progress bar(pb1).

Dim intRecCount As Long
    Dim intCounter As Long
 
RS.MoveLast
intRecCount = RS.RecordCount
pb1.Max = intRecCount
RS.MoveFirst
 
For intCounter = 1 To intRecCount
'add stuff to fill any other controls like list views, boxes or grids   
    pb1.Value = intCounter
 
RS.MoveNext
 
Next intCounter

Q) Where can i learn ADO?
A) Either Here Or www.vb-world.com

VB & Access

Q) Why does the size of my Access Database increase even when i'm deleting records?
A) Click Here


edit: The attachment below is now also available as a thread in the FAQ, here.

[John]

To discuss stuff about this tutorial, please go here:

http://161.58.186.97/showthread.php?s=&threadid=82984

[John]

I have been told that these two sites are very good if you are learning SQL -- be sure to check them out and then pop back here if you still have not found what you want!

http://www.sqlcourse.com/
http://www.sqlcourse2.com/

Here is an ADO tutorial, written by Beacon.
http://www.vbforums.com/showthread.php?threadid=153935

This shows you how to save binary data into a database:
http://www.vbforums.com/showthread.p...hreadid=109403

[Beacon]

SQL

BASIC
SELECT - Selects data from a table or field in a table.
Syntax: "SELECT fieldname FROM tablename"

WHERE - Adds a condition to your SELECT statement.
Syntax: "SELECT * FROM tablename WHERE field ='value'"

AND - Joins 2 or more conditions in a WHERE clause. If ALL conditions are met!
Syntax: "SELECT * FROM tablename WHERE field ='value' AND field2 ='value2'"

OR - Joins 2 or more conditions in a WHERE clause. If ANY conditions are met!
Syntax: "SELECT * FROM tablename WHERE field ='value' OR field2 ='value2'"

BETWEEN - Selects a range of data BETWEEN 2 conditions.
Syntax: SELECT * FROM tablename WHERE fieldname BETWEEN 'value1' AND 'value2'

DISTINCT - Returns each value once! Basically no duplicate values!
Syntax: SELECT DISTINCT fieldname FROM tablename

IS NULL / ISNULL - Used in Having/Where clause to return only the empty (Null) records of a particular field.
Syntax: Tablename.Field Is Null

HAVING - Similar to a "where clause" but used with Group By to filter on certain fields.
Syntax: Having Tablename.Field='Jones'

SORTING AND GROUPING

DESC - Descending Order.

ASC - Acsending Order.

GROUP BY - Groups by a given field.
Syntax: "SELECT * FROM tablename GROUP BY field2"

ORDER BY - Puts the records in a particular order by a given field.
Syntax: "SELECT * FROM tablename ORDER BY field2 DESC"

ADD RECORDS
INSERT INTO - Inserts new rows(records) into a table.
Syntax: INSERT INTO table_name (fieldname, fieldname2) VALUES (value1, value2)

TABLE & DATABASE DESIGN]

CREATION
CREATE - Used in such functions like CREATE TABLE and CREATE index etc.

CREATE DATABASE - Creates a database
Syntax: CREATE DATABASE database_name

CREATE TABLE - Creates a table. Syntax: CREATE TABLE tablename(fieldname datatype)
Syntax: CREATE TABLE tablename (fieldname1 varchar,fieldname2 varchar,fieldname3 varchar,fieldname4 int)

DELETION\DROP

DROP DATABASE - Deletes a database
Syntax: DROP DATABASE database_name

DROP TABLE - Deletes a table and all it's contents.
Syntax: DROP TABLE tablename

DELETE TABLE - Deletes all data in the table!
Syntax: DELETE TABLE table_name

ALTERING

ALTER TABLE - Alter Table allows you to alter the design of a table.
Syntax: ALTER TABLE tablename ADD fieldname datatype
Syntax: ALTER TABLE tablename DROP COLUMN fieldname Drop Column deletes that fieldname

ALIASES

COLUMN ALIAS - Allows you to use aliases.
Systax:SELECT fieldname AS aliasName FROM tablename 'Aliasname can be anything
E.G: SELECT FruitType AS Family, FruitName AS Name FROM tbl_fruity

TABLE ALIAS - As above but with tables!
Systax:SELECT fieldname FROM tablename AS aliasname 'Aliasname can be anything
E.G: SELECT FruitType, FruitName FROM tbl_fruity AS FRUIT

JOINS
JOIN - Used in LEFT JOIN, INNER JOIN AND RIGHT JOIN.

INNER JOIN - Joins two tables together returning ONLY those records that are in both tables with the same ID.
Syntax: ... From Tablename1 Inner Join Tablename2 On Tablename1.FieldID=Tablename2.FieldID

LEFT JOIN - As for Inner Join BUT returns all the records on the left table and only those that match from the right on the ID field. Those that don't match are set to Null int he returned fields.
Syntax: ... From Tablename1 Left Join Tablename2 On Tablename1.FieldID=Tablename2.FieldID

ON - Which fields the two tables involved are joined on. Can hold more than one field.
Syntax: ... On ((Tablename1.FieldID1 = Tablename2.FieldID1) AND (Tablename1.FieldID2 = Tablename2.FieldID2))

RIGHT JOIN - As for Left Join but the reverse. All the records in the right table and only those that match on the left are returned.
Syntax: ... From Tablename1 Inner Join Tablename2 On Tablename1.FieldID=Tablename2.FieldID

INDEXES
INDEX - An index is similar to a Primary Key in Access. Examples follow below!

CREATE INDEX - Creates a index ona table! This index ALLOWS duplicates to be created.
Syntax: CREATE INDEX index_name ON tablename (fieldname)

CREATE UNIQUE INDEX - Creates a UNIQUE index on the table. Meaning no 2 records can have the same index value!
Syntax: CREATE UNIQUE INDEX index_name ON table_name (fieldname)

DROP INDEX - Deletes an Index from a table.
Syntax: DROP INDEX tablename.index_name

FUNCTIONS - Built-in functions for SQL! Syntax for all is:
SELECT function(fieldname) FROM tablename

SUM - Returns the SUM of said field!
Syntax:SELECT SUM(fieldname) FROM tablename

COUNT - Returns the COUNT of said field!
Syntax:SELECT COUNT(fieldname) FROM tablename

AVG - Returns the AVERAGE value of said field!
Syntax:SELECT AVG(fieldname) FROM tablename

TOP - Returns the TOP ? records in table. Where ? is a number!
Syntax:SELECT TOP 5 FROM tablename

MAX - Returns the MAXIMUM value of said field.
Syntax:SELECT MAX(fieldname) FROM tablename

MIN - Returns the MINIMUM value of said field.
Syntax:SELECT MIN(fieldname) FROM tablename

---------------------------------------------------------------

Thanks to Vince for his help!