Append All function (Access 2000)

Topic

New Reply

Append everything if a new customer exists

I have a function that appends only those customers that do not exist.It works,but i want to improve it,since i have to enumerate
all the fields,which sometimes are numerous.I want to have a common function to simplify the matter.
For a similar case, for updating i have received from the Moderator Hans in the present forum, an excellent function called Public Function UpdateTable(SourceTable As String, TargetTable As String, LinkField As String). I use this functioin with success but i cannot redesignit for the append function.Can somebody help me?

The append function i use is :

Dim StrAppendCustomer As String

StrAppendCustomer = “INSERT INTO customers (CustomerID,CompanyName) ” & _
“SELECT o1.customerid, o1.companyname ” & _
” FROM customers1 As o1 WHERE NOT EXISTS (SELECT * FROM Customers WHERE CustomerID=o1.CustomerID) ”

CurrentDb.Execute StrAppendCustomer
End Sub

In the above function i have mentioned only 2 fields,CustomerID and CompanyName, but actually the tablke contains a lot of fields
and that is why i want to avoid their enumeration.

Just for information the function received from Hans for updating
is the following:

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”

Regards,
Hans

Reply | Quote

Replies

This is actually a lot simpler that your previous question, because you want to insert all fields here, instead of all but one.

Function AppendToTable(SourceTable As String, TargetTable As String, LinkField As String)
Dim strSQL As String

On Error GoTo ErrHandler

strSQL = “INSERT INTO [” & TargetTable & “] ” & _
“SELECT * FROM [” & SourceTable & “] “& _
“WHERE Not Exists ” & _
“(SELECT * FROM [” & SourceTable & “] As T ” & _
“WHERE T.[” & LinkField & “]=[” & SourceTable & _
“] .[” & LinkField & “])”

‘ Execute append query
CurrentDb.Execute strSQL, dbFailOnError

ExitHandler:
Exit Function

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Function

Reply | Quote

Thank you very much for your reply.I receive the following message

invalid use of ‘.’,’!, or ‘(), in query expression

‘T.[customerid]=customers1].[customerid]’.

I cal the function in the following way :

AppendToTable “customers1”, “customers”, “customerID”

May be the wau i call the function is not right ?

Thank you in advance for your reply

Best regards

Reply | Quote

Hi Aral,

In the first place, there is an error in my function; the version I posted shouldn’t cause an error, but it will not append any records either, since the EXISTS part only looks at the source table. Please accept my apologies. See below for the corrected code.

The error message you get means that you must have omitted a left bracket [ when placing the function in your module. There should have been an opening bracket before customers1 in T.[customerid]=customers1].[customerid].

Did you know that you can select code in a post with the mouse, then copy it to the clipboard with Ctrl+C, and paste it into a module with Ctrl+V?

Here is the corrected code, I verified that it works in my test database:

Function AppendToTable(SourceTable As String, TargetTable As String, LinkField As String)
Dim strSQL As String

On Error GoTo ErrHandler

strSQL = “INSERT INTO [” & TargetTable & “] ” & _
“SELECT * FROM [” & SourceTable & “] ” & _
“WHERE Not Exists ” & _
“(SELECT * FROM [” & TargetTable & “] As T ” & _
“WHERE T.[” & LinkField & “]=[” & SourceTable & _
“].[” & LinkField & “])”

‘ Execute append query
‘ Uncomment the following line if you want to inspect the SQL.
‘MsgBox strSQL
CurrentDb.Execute strSQL, dbFailOnError
ExitHandler:
Exit Function

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Function

Reply | Quote

Thank you very much.It works perfectly now! With this approach i have quite a modern approach and i can spare a lot
of work. Just to think how powerful Access is.

I wish you a pleasant day

Best regards

Reply | Quote