| Author |
Topic |
|
whill96205
Starting Member
46 Posts |
 Posted - 2004-07-06 : 16:34:54
|
| Aargh! Okay, I know I'm a newb and all, but this is ridiculous.I've got Access 2000 installed, and am trying to use it to design forms for a SQL Server 2000 database. When I launch Access, I see the usual Database screen with the Database Objects listed down the left side (Tables, Forms, Diagrams, etc.)1) I do NOT have Queries available in my Objects list. It is not an option in my pulldown menu View > Database Objects.2) I *do* have Views listed as an Object, but I can only create a new View in Design View, as there is no "Create using Wizard" option.3) When I try to create a View in Design View, I can add a table to the Diagram Pane, but cannot get all the columns to list out as they should within the table. My only option is to check the "ALL columns" box, as if '*' were the only column...To review: NO query tab available; NO wizard available for creating Views; NO column selecting capabilities in when creating Views. Strangely, the Northwind Database example that is included in every Access installation has NONE of these problems. (Oh wait -- Northwind's database screen doesn't even have Views available on it... OH, the inconsistency!)Can anyone enlighten me as to why I'm experiencing so many Microsoft Moments here?One more weird thing: "Create using a Wizard" is not available for my Tables either.... but it *is* available for Northwind!Thanks to any who can help me out here. I'm stuck and unable to produce a query/view/whatever until I understand what my Access thinks it is doing.--Whill |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-06 : 20:29:35
|
| Whill, What you've come across here is the difference b/w an Access Database and an Access Data Project. The latter does not store queries or data. All it contains is links to the SQL Server objects (tables, views, stored procedures), and forms for the user interface.It might pay to read up on ADP's as they can be much better than traditional Access databases for reliability and speed.HTH, Tim |
 |
|
|
whill96205
Starting Member
46 Posts |
Posted - 2004-07-07 : 10:38:02
|
| Timmy,I suppose what I've got then is an ADP. What the client uses currently is an Access interface on top of a SQL database, and I'm just expanding on that concept.SO... if I want to do queries, I need to do them directly in SQL Server?Or... How do I get the table columns to show up when I'm creating a View in Access?--Whill |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-07 : 19:51:32
|
| Whill, You will need to create all your views and stored procedures directly in SQL Server, although you can use Access's interface (although it's not perfect). Queries are replaced by stored procedures, which are a bit daunting at first but offer greater flexibility. Not too sure what you're referring to in your second question - can you clarify?Tim |
|
|
|
whill96205
Starting Member
46 Posts |
Posted - 2004-07-08 : 10:39:21
|
| When creating a View in Design View (rather than creating it with a Wizard, which is not an option for me), there are 3 panes: Diagram, Grid, and SQL. In the Diagram pane, you can display the tables that will be included in the query. You can choose to show the table as a Name Only, or show it with its column names. When I show the tables with column names, the only column I get is "* (All Columns)". There's a checkbox next to this, so I presume that if all the columns were to actually have been displayed each of them would have had a checkbox next to them as well, allowing me to pick and choose columns to be returned through the query's SELECT statement. Since all I get is "* (All Columns)", I can't do that. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-08 : 19:15:35
|
| Seems that Access isn't seeing the table designs properly. Does this happen with all tables?Did you add the table using the 'Add table' toolbar button? |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-08 : 19:31:28
|
| Or:Open a new Access .MDB file and link the tables in. Use the Access query designer to get the structure of the query right, then 'translate' into proper SQL.I find this easier than using the ADP View designer. |
|
|
|
whill96205
Starting Member
46 Posts |
Posted - 2004-07-13 : 10:37:01
|
| >>Open a new Access .MDB file and link the tables in...<<Timmy,I'm sorry to be so dense. What do you mean by "link them in"?--Whill |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-13 : 18:38:39
|
| By that I mean use the Access 'Linked Table' functionality.As you may be aware, in Access databases (not data projects), you can create tables in the database itself or create a 'link' to a table in another database.To link the tables, go to the 'Tables' tab in the database window, select 'New' and 'Linked Table'. It will prompt you for the type of db to link. Check out the Access help for more info. |
|
|
|
|
Screwy Database View Issue