Results 1 to 2 of 2
2003-08-10, 18:18 #1
- Join Date
- Nov 2001
- Thanked 0 Times in 0 Posts
Update tables function (Access 2000)
I have a very useful function, given to me be Hans, a moderator in the present Forum, which works excellent for all the tables except
two, namely for the tables order details and the table customers.There i receive the mesage order details.orderid not updatable
and customers.customerid not updatable.How can i solve this problem? I cannot explain myself why
this happens with 2 tables and not with the others. i will be very grateful if i receive some advice.Otherwise the
function suggested to me is very effective and easy to handle
Public Function UpdateTables()
UpdateTable "orders1", "orders", "orderid"
UpdateTable "orderdetails1", "orderdetails", "orderid"
UpdateTable "customers1", "customers", "customerid"
UpdateTable "TblClients1", "TblClients", "ClientID"
UpdateTable "CallsClients1", "CallsClients", "CallID"
UpdateTable "CallsCustomers1", "CallsCustomers", "CallID"
Public Function UpdateTable(SourceTable As String, TargetTable
As String, LinkField As String)
' Call this function in the following manner:
'UpdateTable "CallsClients1", "CallsClients", "CallID"
Dim strSQL As String
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
On Error GoTo ErrHandler
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(SourceTable)
strSQL = "UPDATE [" & TargetTable & "] INNER JOIN [" &
SourceTable & "] " & _
"ON [" & TargetTable & "].[" & LinkField & " ]=[" & SourceTable
& "].[" & LinkField & "] " & _
For Each fld In tdf.Fields
If Not (fld.Name = LinkField) Then
strSQL = strSQL & "[" & TargetTable & "].[" & fld.Name & "]=" & _
"[" & SourceTable & "].[" & fld.Name & "], "
' Get rid of last ", "
strSQL = Left(strSQL, Len(strSQL) - 2)
' Execute update query
dbs.Execute strSQL, dbFailOnError
' Clean up
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
' Report error, then go to cleaning up section
MsgBox Err.description, vbExclamation
2003-08-10, 20:56 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Update tables function (Access 2000)
In Access 2000 and up, a query involving more than one table usually requires a unique index on the link field on the "one" side of the join in order to be updateable.
If the OrderDetails table is like the one in the Northwind sample database, it has a composite primary key, so OrderID by itself is not unique. That means that the function I provided will not work. You will have to create a new one for tables with a composite primary key.
(Am I correct in assuming that you are a student? If so, it will be a useful exercise to try and create the modified function yourself; you will learn more that way than if you just copy it from a post)
Check the Customers and Customers1 tables. If CustomerID is the primary key there, I don't understand why the function fails.