Pardon my few simple questions but I'm new at this...



First Problem



I have an Access Database with multiple tables. Among those, these 3 

tables:



PRICES                  MATERIALS         COLORS

  PriceID                 MaterialID        ColorID

  MaterialID              Description       Color

  OptionAColorID          InStock

  OptionBColorID

  OptionCColorID



Notice that the 3 PRICES.OptionXColorID fields contains key to the same 

table COLORS.

I need to query the PRICES table displaying only the records WHERE 

MATERIALS.InStock is TRUE.

I can get this far but I cannot get to access with the same query the 

COLORS.Color field

I can successfully display the OptionXColorID field but I cannot expand it 

to print which COLORS.Color it correspond to.





Second Problem.



I've created this Access Database to be used on my wife website with ASP 

pages.

The database serve at main 2 purposes: store a table catalog of the 

photographs she want to show on her site and store a couple of tables 

containing website user data (such ecards details the user send and 

receive). The ecards tables point to the photographID on the main catalog 

table. I need to update and upload updated catalog tables from time to 

time without affecting the current ecards tables.

I feel like I need to split the database in 2 and link the ecards table to 

the photograph catalog table. Correct? If so, how and how to connect to 

them in the ASP pages?





Third Problem.



For my first foray in the ASP/Database world I used the Beginner's ASP 3.0 

Store example.

Every asp page open the connection (including the "clssfd.asp" page) but 

never close such connection as described in other brief examples in 

previous chapter of the book.

What is the best practice in such case? 





Thanks a lot in advance.



Luca

problem 1 :



SELECT 

	p.*, 

	m.Description, 

	cA.Color AS OptionAColor,

	cB.Color AS OptionBColor,

	cC.Color AS OptionCColor

FROM	Prices p 

	LEFT JOIN Materials m

		ON (p.MaterialID = m.MaterialID

		    AND InStock = true)

	LEFT JOIN Colors c

		ON p.ColorID = c.ColorID





problem 2:



sounds like 3 tables to me :



USERS		PHOTOS	ECARD

id		id		id

name		name		userID

email		location	photoID

etc...	etc...	etc...



you would use joins (as in problem one) to link them..





problem 3:



leaving the connection open leaves it in the connection pool, which is

faster.

setting your objConn = nothing throws it away and it's no longer available

when need and has to be recreated from scratch..



shane

I haven't read the original post, so I may be talking BS, but AFAIK, this 

is not true.



Whenever you close and / or "set to nothing" a connection, it is returned 

to the pool. That is why it is advised that you open and close a connection 

on each page where you use it.

Leaving it open (in a session for example) prevents the connection from 

going back to the pool.

So the general rule is: create a connection on your page, use it throughout 

that page, and close and kill (set to nothing) it at the end again.



You might want to read a little further on this subject:



http://msdn.microsoft.com/library/psdk/dasdk/impr8l2m.htm

(near the end, under Use ADO Like an Apartment Model)



http://msdn.microsoft.com/library/default.asp?URL=/library/techart/pooling2.htm



http://msdn.microsoft.com/library/periodic/period99/basics0599.htm







Imar







>problem 3:

>

>leaving the connection open leaves it in the connection pool, which is

>faster.

>setting your objConn = nothing throws it away and it's no longer available

>when need and has to be recreated from scratch..

>

>shane

sorry... things changed from ado 2.0...



ado 2.1 leaves the connection in the pool when you set = nothing...



pages 327-8 in wrox's ADO 2.1 Programmer's Reference..



a thousand apologies for being out of date...



shane