You can use the same code for a stored procedure. Actually, MS Access 2002 allows you to create them visually with the query builder. Same as you would a view.



Sal

Sal, how do you get Access to show you a SP in the dewign view?! I can not get them to show, only tables and views. This is something that I have been beeting my head against a wall for a long time now.

I really, really, really want to know!

Which version of access are you using?
You can use either Enterprise manager or an MS Access ADP. To start an ms Access ADP,
open MS Access,
Click on "Project(Existing Data)"
and a sort of wizard starts up. You tell it which server and database to connect to and then you will see the SQL Server Tables, View, Stored procedures. In Access 2002 the stored procedures and views show up under queries.
In 2000, just click on new under stored procedures, on Access 2002 click on new under query, then click on create stored procedure in design view.

It is almost the same as creating a query in access.

Sal

Ok, I got caught up in the joy of hope and did not read you message close enough. Yes, you can creatre a SP visually with the graphical design tool, BUT you just use them in a view or SP in the graphical designer, which is waht I thought you were saying. And which what I really wanted to be able to do.

Jeff, with respect to the creating or defining of foreign keys, I think that I have been doing that, let me see if I am doing it right:

Table 1: tblOrder_Header has OrderID as Key field, index

Table 2: tblOrder_Detail has OrderID also has it as a key field and is indexed.

All done when creating (or after it has been created) the tables. Not done elsewhere.

Is that correct?

Freakin' Access. I hate that it encourages people to name their tables with 'tbl' as a prefix. IMO it adds no useful information whatsoever, especially when the database is ported to a real relational database management system like SQL Server. ;)

Anyway, your tblOrder_Header appears to be correctly defined, with the OrderID as a primary key. A Primary key is always implemented via an index and you can't change that. You can decide whether that index is clustered or not, but that's another subject.
This statement I don't understand.

The OrderID cannot be a primary key here, unless every order only has 1 detail line. Your detail table should have the OrderID column as a foreign key into the Order_Header table, and something else as its primary key, like the combination of OrderID and a line number. Many people would define a DetailID column as a primary key to the Details table. This column would be an Identity column, which I generally don't like, but I don't want to get into that debate in this thread :D.

Take a look at the database diagram. Build the default one if you haven't already. Verify that a relationship exists between the two tables; between the Header table using the OrderID and the Detail table using its OrderID column. This will verify that you have a foreign key relationship between the two tables. Make sure similar relationships exist between all your tables in your query.

As I recall the upsizing wizard does not necessarily do a good job upsizing all the foreign key relationships correctly, so you should carefully review what it did.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com