Importing Forms - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Importing Forms

Author

Topic

whill96205
Starting Member

46 Posts

Posted - 2004-07-06 : 12:45:51

As I work on a client's SQL database to make some improvements, I need to modify an existing Access Form to work with my test database. I am trying to import an Access form that is currently being used with the old SQL database, mainly so I don't have to start from scratch. I've imported it (File > External Data > Import...) but now I can't seem to find how to "point" the text boxes to my SQL tables. It's not that they are still pointed at the old SQL tables -- they're not pointed at anything, so I get a lot of #name? errors in Form View. Right-clicking and viewing Properties for a text box gives me no options in the data source pulldown menu. What do I do?

Argh.

--Whill

whill96205
Starting Member

46 Posts

Posted - 2004-07-06 : 12:50:23

Hold on, folks. I think I figgered it out. I can select a data source for the ENTIRE FORM by right-clicking the upper-left corner of the form in Design View.

Now... does anyone know how I could do that and get data from TWO different tables onto a form?
--Whill

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-07-06 : 15:39:58

I suggest you create a query for the two tables using a join. And point your form to it.

Jim
Users <> Logic

whill96205
Starting Member

46 Posts

Posted - 2004-07-06 : 16:02:37

Thanks, Jim. I am trying to do that now, via the Access 2000 interface. I've tried two approaches:

1) Creating a form combining the appropriate fields from LOT and AUCTION, which could then be used as a subform. PROBLEM: When I stipulate that I want one column from AUCTION and two columns from LOT, I get an error: "This key is already associated with an element of this collection" -- and I can go no further. What is causing this? (The primary key of LOT is in the AUCTION table as a foreign key, if that helps.)

2) Creating a query... Uh, strangely, my Access 2000 interface does not have a query tab, nor is Query even listed in my View > Database Objects pulldown menu. I do have a Views tab...

--Whill

P.S. When you say to use a query with a join... you mean if I were writing my query from scratch rather than using the Access interface? Where would I do that, if not through Access? (Getting too late for me to think...)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-07-07 : 11:45:29

A View Is a querry.
Open a new view.
Right click on the work area.
Select your tables.
Pay attention to the link between the tables be sure this is correct some times when converting DBs Access trys to be a little to helpfull and screws up associations.
Select your fields.

But

If you are trying to do a form/sub-form IE Lot/Auction you would not need to join the tables, just make set each form tio its table and set the child and master field settings in the propertys of the sub form.

Jim
Users <> Logic

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-07 : 20:13:44

Whill,

Access data projects are a bit different as far as form datasources go. You can use any one of the following for a form data source:
Table (usually the best and most reliable option)
View (may make the form read-only, depending on the view)
Stored Procedure (will be read-only)
Embedded Query (this is the same as traditional Access. You create an SQL Statement and insert it into the form's datasource property)

If you're using a view or an embedded query, you can make the form read/write as long as you specify the unique table (this form property doesn't exist in normal Access 2000 mdb's). The unique table specifies the 'parent' table in the query/view.

Hope this explains things a bit better. The Access doco in reference to ADP's is a bit wanting, so I had to learn most of this the hard way. Best of luck, anyway.

Tim

whill96205
Starting Member

46 Posts

Posted - 2004-07-13 : 10:42:19

>>If you're using a view or an embedded query, you can make the form read/write as long as you specify the unique table (this form property doesn't exist in normal Access 2000 mdb's). The unique table specifies the 'parent' table in the query/view.<<

Okay, I see that the Unique Table pulldown shows the list of tables that were used to create the View. Selecting a Unique Table for the View *does* make it read/write, but then you are able to edit ONLY those fields of the View that were selected from the table that's designated as the Unique Table... right? In other words, the best I can hope for is to be able to update only SOME but not ALL of the fields in the View...???

--Whill

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-13 : 18:31:14

No - you can update the whole table by setting the Unique Table parameter (otherwise there's no point in having it...)

Your view may be designed in such a way that you can't edit the fields from the child tables.

Can you post some DDL for your tables and view?

whill96205
Starting Member

46 Posts

Posted - 2004-07-14 : 10:51:20

Okay, here's a long post -- DDL from all 3 tables as well as the View.
--------------------------------
DDL from the ORDER table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ORDER-DETAIL_ORDER]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ORDER-DETAIL] DROP CONSTRAINT FK_ORDER-DETAIL_ORDER
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ORD-PMT_ORDER]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ORD-PMT] DROP CONSTRAINT FK_ORD-PMT_ORDER
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ORDER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ORDER]
GO

CREATE TABLE [dbo].[ORDER] (
[OrderID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[CustomerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustPhone] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderDate] [datetime] NULL ,
[ShipQuote] [money] NULL ,
[Declared] [money] NULL ,
[ShipNotes] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CallNotes] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderStatus] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaidStatus] [bit] NULL ,
[Refund] [money] NULL ,
[RefundMethod] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipMethod] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PickedUpBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipAddress1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipAddress2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipPhone1] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipPhone2] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipPhone3] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipState] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipZIP] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TypeChange] [bit] NULL ,
[TypeChangeDate] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ORDER] ADD
CONSTRAINT [PK_ORDER] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
GO

--------------------------------
DDL from the LOT table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_AUCTION_LOT]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AUCTION] DROP CONSTRAINT FK_AUCTION_LOT
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ORDER-DETAIL_LOT]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ORDER-DETAIL] DROP CONSTRAINT FK_ORDER-DETAIL_LOT
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LOT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LOT]
GO

CREATE TABLE [dbo].[LOT] (
[LotNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Client] [int] NULL ,
[Description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WarehouseLoc] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Container] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClientPrice] [money] NULL ,
[RinnerCkDate] [datetime] NULL ,
[RinnerCkNum] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LOT] ADD
CONSTRAINT [PK_LOT] PRIMARY KEY CLUSTERED
(
[LotNum]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LOT] ADD
CONSTRAINT [FK_LOT_CLIENT] FOREIGN KEY
(
[Client]
) REFERENCES [dbo].[CLIENT] (
[ID]
)
GO

--------------------------------
DDL from the AUCTION table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_AUCT-PMT_AUCTION]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AUCT-PMT] DROP CONSTRAINT FK_AUCT-PMT_AUCTION
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AUCTION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AUCTION]
GO

CREATE TABLE [dbo].[AUCTION] (
[EbayNum] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LotNum] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EndDate] [datetime] NULL ,
[WinBid] [money] NULL ,
[Winner] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaidStatus] [bit] NULL ,
[Refund] [money] NULL ,
[RefundMethod] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AUCTION] ADD
CONSTRAINT [PK_AUCTION] PRIMARY KEY CLUSTERED
(
[EbayNum]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AUCTION] ADD
CONSTRAINT [FK_AUCTION_LOT] FOREIGN KEY
(
[LotNum]
) REFERENCES [dbo].[LOT] (
[LotNum]
)
GO

--------------------------------
DDL from the View:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[order-details]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[order-details]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.[ebay-lot-descr-loc]
AS
SELECT dbo.[ORDER].OrderID, dbo.AUCTION.EbayNum, dbo.LOT.LotNum, dbo.LOT.Description, dbo.LOT.WarehouseLoc
FROM dbo.AUCTION INNER JOIN
dbo.LOT ON dbo.AUCTION.LotNum = dbo.LOT.LotNum INNER JOIN
dbo.[ORDER-DETAIL] ON dbo.LOT.LotNum = dbo.[ORDER-DETAIL].LotNum INNER JOIN
dbo.[ORDER] ON dbo.[ORDER-DETAIL].OrderID = dbo.[ORDER].OrderID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--------------------------------

--Whill

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-14 : 19:19:09

Whill,

You missed the CLIENT table....

And the ORDER DETAIL table too

TIm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources