Simplify update function (Access 2000)

Topic

New Reply

I have a function for updating 2 tables and i have to enumerate all the fileds in the function.Is there any way to update all in the table
and thus simplify my function.I want to use this function for other similar cases and it will be very conventient just to give the command
for updating without bothering about the fileds.

Mu function is :
Public Function UpdateCallsClients()
Dim sql As String
sql = ” UPDATE CallsClients1 INNER JOIN CallsClients ON [CallsClients1].[CallID]=[CallsClients].[CallID] SET” & _
” CallsClients.FirstName = [CallsClients1].[FirstName] ” & _
” , CallsClients.LastName = [CallsClients1].[LastName] ” & _
” , CallsClients.Address= [CallsClients1].[Address] ” & _
” , CallsClients.city = [CallsClients1].[city] ” & _
” , CallsClients.CompanyName = [CallsClients1].[CompanyName] ” & _
” , CallsClients.title = [CallsClients1].[title] ” & _
” , CallsClients.WorkPhone = [CallsClients1].[WorkPhone] ” & _
” , CallsClients.WorkExtension = [CallsClients1].[WorkExtension] ” & _
” , CallsClients.MobilePhone = [CallsClients1].[Mobile] ” & _
” , CallsClients.EmailName = [CallsClients1].[EmailName] ” & _
” , CallsClients.LastMeetingDate = CallsClients1.LastMeetingDate ” & _
” , CallsClients.ContactTypeID = CallsClients1.ContactTypeID ” & _
” , CallsClients.ReferredBy = CallsClients1.ReferredBy ” & _
” , CallsClients.Notes = CallsClients1.Notes ” & _
” , CallsClients.CallDate = CallsClients1.CallDate ” & _
” , CallsClients.CallTime = CallsClients1.CallTime ” & _
” , CallsClients.subject = CallsClients1.subject ” & _
” , CallsClients.clientID = CallsClients1.ClientID ”

CurrentDb.Execute sql
End Function

Reply | Quote

Replies

It’s not quite true that you update ALL fields – you don’t update the field CallID on which you join the tables. Do you want to update all fields except the join field? And can you be sure that the source table and target table contain the same fields?

Reply | Quote

Thank you so much for your kind reply.It is true that the field CallId is not updated.I The two tables are absolutely identical in a sense that
the source table and the target table contain the same fields.Having this in mind, that the Callid is not updated, and that the two
tables contain the same fields, can you recommend a formula for updating the fields, or may be this is not possible ?

Best regards

Reply | Quote

The following general code uses DAO, so you must set a reference to the Microsoft DAO 3.6 Object Library in Tools | References… in the Visual Basic Editor.

Public Function UpdateTable(SourceTable As String, TargetTable As String, LinkField As String)
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 & “] ” & _
“SET ”

For Each fld In tdf.Fields
If Not (fld.Name = LinkField) Then
strSQL = strSQL & “[” & TargetTable & “].[” & fld.Name & “]=” & _
“[” & SourceTable & “].[” & fld.Name & “], ”
End If
Next fld

‘ Get rid of last “, ”
strSQL = Left(strSQL, Len(strSQL) – 2)
‘ Execute update query
dbs.Execute strSQL, dbFailOnError

ExitHandler:
‘ Clean up
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
‘ Report error, then go to cleaning up section
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Call this function in the following manner:

UpdateTable SourceTable:=”CallClients1″, TargetTable:=”CallClients”, LinkField:=”CallID”

or simply

UpdateTable “CallClients1”, “CallClients”, “CallID”

Reply | Quote

Thank you so much indeed !!!!!!!!!!!!!!!!

Reply | Quote