Query with two tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have designed a query with 2 tables called "Customers" and "Tanks". The
query has all the fields from tanks which our company rents to customer and
the customer ID [accountnumber] and ship name from customers. Can this query
be used for data entry if a form is made from the query? If it can and is,
how will it affect the main tables? Would appreciate any help and assistance.
 
a query is showing selected records & fields of the main tables.....any
changes will change the data in the main tables...
 
. Can this query
be used for data entry if a form is made from the query? If it can
and is, how will it affect the main tables?
Click to expand...

Depends on exactly which fields you include and can see... often you just
have to experiment with Access to see which queries are updateable and
which are not.

In principle:

If the query contains the foreign key (i.e. Tanks.RentedTo) then you can
change _which_ customer is renting the tank but not details within that
customer record;

If you want to update fields within the Customer record, then the query
must contain the referenced key (i.e. Customers.ReferenceID).

But it's not always predictable.

Hope that helps


Tim F
 
Depends on exactly which fields you include and can see... often you just
have to experiment with Access to see which queries are updateable and
which are not.

In principle:

If the query contains the foreign key (i.e. Tanks.RentedTo) then you can
change _which_ customer is renting the tank but not details within that
customer record;

If you want to update fields within the Customer record, then the query
must contain the referenced key (i.e. Customers.ReferenceID).

But it's not always predictable.
Click to expand...

I think it is predictable, we just haven't been told what the criteria
are (the old chestnut: still no spec for Jet).

To translate into standard SQL, the question is, 'When is a VIEW
updateable?' to which the answer may be found in the SQL-92 standard.

Although SQL-92 compliance is not claimed for Jet, the two are not
mutually exclusive: the Information Schema for VIEWs includes an
IS_UPDATABLE attribute (21.2.8 VIEWS) which is exposed in Jet using
the ADODB.Connection.OpenSchema method with the OLE DB provider.
Unfortunately, in practice the attribute isn't reliable for Jet :(

Here's a quick example of a VIEW which is not updateable (because the
omitted column does not have a DEFAULT) but is exposed in the
Information Schema as IS_UPDATABLE = TRUE:

Sub Test_ViewIsUpdatable()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test1 (" & _
" key_col INTEGER NOT NULL UNIQUE);"
.Execute _
"CREATE TABLE Test2 (" & _
" key_col INTEGER NOT NULL," & _
" data_col INTEGER NOT NULL," & _
" UNIQUE (key_col, data_col));"
.Execute _
"CREATE VIEW View1 AS" & _
" SELECT T1.key_col, T2.key_col" & _
" FROM Test1 AS T1, Test2 AS T2" & _
" WHERE T1.key_col = T2.key_col;"

Dim rs
Set rs = .OpenSchema(23, _
Array(Empty, Empty, "VIEW1"))
MsgBox _
"IS_UPDATABLE = " & _
IIf(rs.Fields("IS_UPDATABLE").value, _
"TRUE", "FALSE")

On Error Resume Next
.Execute _
"INSERT INTO View1 VALUES (1, 1);"
MsgBox "Error: " & Err.Description
On Error GoTo 0
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
Back
Top