1

Goal:
From Microsoft Outlook, I want to run append query located in Microsoft Access database, which will create record in "log" table, containing information about sent e-mail. (appending is triggered by event "Application_ItemSend")

Problem:
When I run query, Access appends only two out of seven fields: variable "strSenderEmail" and function "now()" which is inserted in query -> by that I mean that in table "Log" I have only information about sender and date, but subject, receiver etc. are empty.

Additional Info:
- names of the parameters -> I have no information that something is incorrect or missing
- all parameters are input
- I tried to pass parameter alternatively: "qdf.parameters(1)=test" but does not work
- if I run this query from access and insert parameters "from hand" everything works.

Question:
Why is my query not appending properly?
Alternatively I'll try SQL command to insert data or add recordset into table.

Function Code: 

strBackendPath - path of backend access
Qapp_001 - name of the append query in access

Set dbs = DBEngine.OpenDatabase(strBackendPath) 'set connection with backend 
Set qdf = dbs.QueryDefs(Qapp_001)               'set update merged processes query 
<br>
qdf![strSenderEmail] = GetOriginalSenderAddress(objMailItem)  'this is external function, this variable is actually working properly -> information are added in new record
qdf!strReceiverEmail = "test1"      'insert receiver 
qdf!strSubject = "test2"            'subject of the e-mail 
qdf!struserUID = "test3"            '(Environ$("Username"))         
qdf!bytCommunicationType = 1        'define type of communication 
qdf!bytStatus = 1                   'define status

qdf.Execute                         'run append query 
<br>
Set qdf = Nothing   'clear query 
Set dbs = Nothing   'clear database


Columns in Log table:

ID  SenderEmail ReceiverEmail   Subject PreparationDateAndTime  userUID CommunicationType

SQL Code of Append query (from Access): 

    PARAMETERS 
strSenderEmail LongText, strReceiverEmail LongText, strSubject LongText, struserUID Text ( 255 ), bytCommunicationType Byte, bytStatus Byte;    
    INSERT INTO 
Tbl_02_Log ( SenderEmail, ReceiverEmail, Subject, PreparationDateAndTime, userUID, CommunicationType, Status )
    SELECT 
[strSenderEmail] AS Expr1, [strReceiverEmail] AS Expr2, [strSubject] AS Expr3, Now() AS Expr4, [struserUID] AS Expr5, [bytCommunicationType] AS Expr6, [bytStatus] AS Expr7;
braX
  • 11,506
  • 5
  • 20
  • 33
Hronic
  • 155
  • 1
  • 1
  • 7
  • 1
    Not sure if this is the main problem, but `LongText` parameters don't really work. See https://stackoverflow.com/a/37052403/3820271 and https://stackoverflow.com/questions/39106471/ms-access-2013-calling-insert-queries-from-vba-with-strange-errors . I would use `RecordSet.AddNew` instead of a parameterized INSERT query. – Andre Oct 07 '19 at 09:58
  • I agree with Andre, inserting using a recordset would be preferred. You can also try scrapping the explicit parameter declaration to have the DB engine determine type automatically. Parameter types are a pain because there are many synonym schemes and they're not compatible with eachother. As an alternative, you can try swapping `LongText` with `Memo`. – Erik A Oct 07 '19 at 13:03

1 Answers1

0

Just to sum up. I did not find solution to run built-in access append query without errors.

I wrote an SQL code which I run using database engine, there are some global/private variables but the idea of solution is as follows:

Dim dbe As Object                       'dao database
Dim dbs As Object                       'access database with actual data
Dim strSender As String                 'address of group mail, from which e-mail is being sent
Dim strReceiver As String               'consolidated string of recipients: To + CC + Bcc
Dim strSubject As String                'subject of sent e-mail
Dim strUID As String                    'uid of the user who sent e-mail
Dim strSQL As String                    'SQL code of append query, which adds new log record to database

Set dbe = CreateObject("DAO.DBEngine.120")  'version on win7 and win10 is 3.6
Set dbs = dbe.OpenDatabase(GC_BackendPath, False, False, ";pwd=" & GC_Password & "") 'set connection with backend

strSender = "'" & G_OriginalSender & "'"        'get sender e-mail - group mailbox
strReceiver = G_MailReceiver                    'assign recipient list
strSubject = G_MailSubject                      'assign subject of email

strUID = "'" & CreateObject("wscript.Network").UserName & "'"   'get user UID and assign under variable

strSubject = "'" & Replace(Mid(strSubject, 2, Len(strSubject) - 2), "'", "") & "'"
strSQL = "INSERT INTO " & Tbl_02 & " ( SenderEmail, ReceiverEmail, Subject, PreparationDateAndTime, userUID, CommunicationType, Status) SELECT " & strSender & " AS SenderEmail, " & strReceiver & " AS ReceiverEmail, " & strSubject & " AS Subject, Now() AS PreparationDateAndTime, " & strUID & " AS userUID, " & bytCommunication & " AS CommunicationType, " & bytStatus & " AS Status;"

Set dbs = dbe.OpenDatabase(GC_BackendPath)      'set database which will be updated with new record log

dbs.Execute strSQL                              'run SQL code for given database
dbs.Close                                   'close database
Set dbs = Nothing
Hronic
  • 155
  • 1
  • 1
  • 7