Solved VBA Create Query With Long Text(Memo) Field Parameter Errors If More Than 255 Characters (2 Viewers)

[LGDGlen]

Hi All

I am logging changes to tables by copying updates to log table and have hit an issue with a memo/long text field in that when it is used as a parameter to a query built using VBA if the field it is copying from is more than 255 characters it throws an error.

I believe this is a bug with Access as from what i have found online is people saying that they can recreate it using earlier versions (2010, 2007 etc)

Is there any way to tell the query you are creating in VBA that the parameter is a memo/long text field or am i not going to achieve what i want.

the reason i started using parameter queries was originally i had issues with special characters in the text fields like quote markes or commas etc which were causing issues with the built SQL text.

Here is an example of what I am doing:

Private Sub Form_AfterUpdate()

    Dim SQL As String, qdf As QueryDef
    'log change
    SQL = "INSERT INTO [FACT-QCReports_LOG] "
    SQL = SQL & "(qa_report_date_updated,qa_report_user_updated,qa_report_where_updated, qc_report_id,qc_report_del_id,qc_report_del_prod_id,qc_report_comments,[qc_report_loss_%],qc_report_hyperlinks,qc_report_sent_to_supplier,qc_report_sent_to_supplier_date,qc_report_reporter,qc_report,qc_report_rag_status) "
    SQL = SQL & "VALUES (" & CSql(Now) & ", '" & TempVars!strUserName & "'" & ", '" & Me.Form.Name & "'," & Nz(Me.[qc_report_id], 0) & ", " & Nz(Me.[qc_report_del_id], 0) & ", " & Nz(Me.[qc_report_del_prod_id], 0)
    SQL = SQL & ", PARqc_report_comments, " & Nz(Me.[qc_report_loss_%], 0)
    SQL = SQL & ", PARqc_report_hyperlinks, " & Nz(Me.[qc_report_sent_to_supplier], 0) & ", " & Nz(CSql(Me.[qc_report_sent_to_supplier_date]), 0) & ", '" & Nz(Me.[qc_report_reporter], "") & "'" & ", PARqc_report, " & Nz(Me.[qc_report_rag_status], 0) & ")"
    Set qdf = CurrentDb.CreateQueryDef("", SQL)
    With qdf
        .Parameters("PARqc_report_comments") = Nz(Me.[qc_report_comments], "")
        .Parameters("PARqc_report_hyperlinks") = Nz(Me.[qc_report_hyperlinks], "")
        .Parameters("PARqc_report") = Nz(Me.[qc_report], "")
        .Execute
    End With

End Sub

the problem arrises when the field ME.qc_report_comments is longer than 255 characters. Here is the table definition for the log table i'm writing to.

One thing i've also noticed in investigating this issue is that hyperlinks are also marked as Memo fields when i do the following code:

Private Sub Command30_Click()

    Dim def As TableDef, wb As Object, xL As Object, lngRow As Long, f As Field
    
    Dim Dict_FieldTypes As Scripting.Dictionary: Set Dict_FieldTypes = New Scripting.Dictionary
    Dict_FieldTypes.Add key:=1, Item:="Boolean"
    Dict_FieldTypes.Add key:=2, Item:="Byte"
    Dict_FieldTypes.Add key:=3, Item:="Integer"
    Dict_FieldTypes.Add key:=4, Item:="Long"
    Dict_FieldTypes.Add key:=5, Item:="Currency"
    Dict_FieldTypes.Add key:=6, Item:="Single"
    Dict_FieldTypes.Add key:=7, Item:="Double"
    Dict_FieldTypes.Add key:=8, Item:="Date/Time"
    Dict_FieldTypes.Add key:=9, Item:="Binary"
    Dict_FieldTypes.Add key:=10, Item:="Text"
    Dict_FieldTypes.Add key:=11, Item:="Long Binary (OLE Object)"
    Dict_FieldTypes.Add key:=12, Item:="Memo"
    Dict_FieldTypes.Add key:=15, Item:="GUID"
    Dict_FieldTypes.Add key:=16, Item:="Big Integer"
    Dict_FieldTypes.Add key:=17, Item:="VarBinary"
    Dict_FieldTypes.Add key:=18, Item:="Char"
    Dict_FieldTypes.Add key:=19, Item:="Numeric"
    Dict_FieldTypes.Add key:=20, Item:="Decimal"
    Dict_FieldTypes.Add key:=21, Item:="Float"
    Dict_FieldTypes.Add key:=22, Item:="Time"
    Dict_FieldTypes.Add key:=23, Item:="Time Stamp"
        
    Set xL = CreateObject("Excel.Application")
    xL.Visible = True
    Set wb = xL.Workbooks.Add
    lngRow = 2
    For Each def In CurrentDb.TableDefs
        If def.Name Like "FACT*" Then
            For Each f In def.fields
                With wb.Sheets("Sheet1")
                    .Range("A" & lngRow).Value = def.Name
                    .Range("B" & lngRow).Value = f.Name
                    .Range("C" & lngRow).Value = Dict_FieldTypes(f.Type)
                    '.Range("D" & lngRow).Value = f.size
                    '.Range("E" & lngRow).Value = f.Required
                    '.Range("F" & lngRow).Value = f.DefaultValue
                    lngRow = lngRow + 1
                End With
            Next
        End If
    Next
End Sub

The list of type definitions i got from Field.Type Property

Table	Field	Type
FACT-QCReports_LOG	qc_report_log_id	Long
FACT-QCReports_LOG	qa_report_date_updated	Date/Time
FACT-QCReports_LOG	qa_report_user_updated	Text
FACT-QCReports_LOG	qa_report_where_updated	Text
FACT-QCReports_LOG	qc_report_id	Long
FACT-QCReports_LOG	qc_report_del_id	Long
FACT-QCReports_LOG	qc_report_del_prod_id	Long
FACT-QCReports_LOG	qc_report_comments	Memo
FACT-QCReports_LOG	qc_report_loss_%	Double
FACT-QCReports_LOG	qc_report_hyperlinks	Memo
FACT-QCReports_LOG	qc_report_attachment
FACT-QCReports_LOG	qc_report_sent_to_supplier	Long
FACT-QCReports_LOG	qc_report_sent_to_supplier_date	Date/Time
FACT-QCReports_LOG	qc_report_reporter	Text
FACT-QCReports_LOG	qc_report	Text
FACT-QCReports_LOG	qc_report_rag_status	Long

I'm rattling all of the above just to make sure i cover all of the coding and info i can.

What i'm after is anyone's advice/guidance as to how to resolve this issue in the most efficient manner possible as this example is the first place that we've had the issue but again running the code to get the list of fields and their types there are a number of places where this is a potentially a problem waiting to happen.

If you need anything else, code, table defs etc let me know i'll endeavour to provide so as to resolve this issue

thanks

Glen

 

[ebs17]

First: A little parameter query, a little form reference - this mixture is not convincing.
For a parameter query, you can, and should, define the parameters. General:

PARAMETERS
   pAA Date,
   pBB Long,
   pCC Text(255),
   pDD Memo
;
INSERT INTO
   TableX(
      AA,
      BB,
      CC,
      DD,
      TimestampField
   ) VALUES(
   pAA,
   pBB,
   pCC,
   pDD,
   Now()
)

This query can and should be saved as a saved query. There is no reason to assemble them in VBA.

Executing the query for example with ...

ExecuteParamQuery CurrentDb, "NameParamQuery", "pAA", Me.txtAA, "pBB", Me.txtBB, "pCC", Me.txtCC, "pDD", Me.txtDD

Public Function ExecuteParamQuery(ByVal MyDB As DAO.Database, ByVal AnyQuery, _
                                  ParamArray QueryParams() As Variant) As Long
    Dim qd As DAO.QueryDef
    Dim i As Long

    If UBound(QueryParams) Mod 2 = 1 Then             ' Paare Parameterfeld-Wert vorhanden
        If QueryExists(MyDB, AnyQuery) Then
            ' gespeicherte Abfrage
            Set qd = MyDB.QueryDefs(AnyQuery)
        Else
            ' SQL-Anweisung
            Set qd = MyDB.CreateQueryDef(vbNullString, AnyQuery)
        End If
        For i = 0 To UBound(QueryParams) Step 2
            qd.Parameters(QueryParams(i)) = QueryParams(i + 1)
        Next
        qd.Execute dbFailOnError
        ExecuteParamQuery = qd.RecordsAffected
        qd.Close
        Set qd = Nothing
    End If
End Function

Private Function QueryExists(ByVal MyDB As DAO.Database, ByVal QueryName As String) As Boolean
    Dim qd As DAO.QueryDef
    For Each qd In MyDB.QueryDefs
        If qd.Name = QueryName Then
            QueryExists = True
            Exit For
        End If
    Next
End Function

 

[LGDGlen]

@ebs17 thank you for your suggestions and guidance i am still on a learning path with all of this so the vba building was how i initially did the queries before they were parameters and then when the issue of special characters came up i just extended them

i will use what you have given above and make amendments to the initial area of problem the QC reports that i am logging and if that resolves it (which i am sure it will) then i will go through the other areas and update them, no sense "fixing" everything before i know the fix works i guess but i'm sure it will just small steps

hope that makes sense, and thank you so much for your help whatever the outcome i always appreciate this communities help assistance and guidance it has taken me so far with Access development

 

[LGDGlen]

@ebs17 I have a bit of an issue with the solution you have supplied, not the solution as such but i guess my implementation. Every time i use the parameter query to write to the table any field that appears after the first memo field ends up being blank. i have stepped through the code and looked at the values and they are all there throught the ExecuteParamQuery function but once the line "qd.Execture dbFailOnError" runs and the record is written to the table the data is not written to the record after the memo field

there are 2 memo/long text fields in the record so i removed the first one from the process and the data is written correctly to the record until the second one a bit further along in the record but again after that one nothing gets written.

i'm so close with this and i can see what i should have been doing from the start but just need some advice on what might be the issue here as it is stopping me using the new method of doing things and i really want to use it as its a much more elegant solution

here is the SQL from the query i have created, it might be (probably) something wrong with the query itself as the VBA seems to be ok as i can see the data in all the fields present up to the execute command:

PARAMETERS PARqa_report_date_updated DateTime, PARqa_report_user_updated Text ( 255 ), PARqa_report_where_updated Text ( 255 ), PARqc_report_id Long, PARqc_report_del_id Long, PARqc_report_del_prod_id Long, PARqc_report_comments LongText, PARqc_report_loss_percent Long, PARqc_report_hyperlinks LongText, PARqc_report_sent_to_supplier Long, PARqc_report_sent_to_supplier_date DateTime, PARqc_report_reporter Text ( 255 ), PARqc_report_rag_status Long;
INSERT INTO [FACT-QCReports_LOG] ( qa_report_date_updated, qa_report_user_updated, qa_report_where_updated, qc_report_id, qc_report_del_id, qc_report_del_prod_id, qc_report_comments, qc_report_loss_percent, qc_report_hyperlinks, qc_report_sent_to_supplier, qc_report_sent_to_supplier_date, qc_report_reporter, qc_report_rag_status )
SELECT PARqa_report_date_updated AS Expr1, PARqa_report_user_updated AS Expr2, PARqa_report_where_updated AS Expr3, PARqc_report_id AS Expr4, PARqc_report_del_id AS Expr5, PARqc_report_del_prod_id AS Expr6, PARqc_report_comments AS Expr7, PARqc_report_loss_percent AS Expr8, PARqc_report_hyperlinks AS Expr9, PARqc_report_sent_to_supplier AS Expr10, PARqc_report_sent_to_supplier_date AS Expr11, [PARqc_report_reporter] AS Expr12, [PARqc_report_rag_status] AS Expr13;

i guess the only thing is once i saved the SQL i entered Access modified it to look like the above with al the fields as "Exprnn"

when running the process i can see:

this is the parameters section of the query def "qd" but when the records is written to the database i can only see:

hope that all makes sense, i appreciate any help thank you

Glen

 

[ebs17]

Personally, I hardly ever use Memo as a data type - atomic information doesn't take up that much space - so I have little practical experience with it.

What irritates me: There are two variants for append queries.

INSERT INTO ... VALUES(...)
INSERT INTO ... SELECT ... FROM ...

You switched variants for no apparent reason. The SELECT variant requires a FROM part in Jet for append. Without this, however, the query itself should not work.

Let's put it this way: Finding an error in an attempted solution is one thing, solving a task with possibly a different way is another. Your content from the form results in exactly one new data record. You can write this into the table just as quickly and easily using a recordset. It would be worth a try:

Dim rs As DAO.Recordset
Set rs = CurrentDb.Openrecordset("SELECT [FieldList] FROM FACT-QCReports_LOG WHEWRE False", dbOpenDynaset)     ' open recordset empty
With rs
   .AddNew
   !qa_report_date_updated = Me.txtText1
   !qa_report_user_updated = Me.txtText2
   ' other Fields
   .Update
   .Close

End With

 

[LGDGlen]

@ebs17 appreciate your response, not sure i understand the first bit about the query as i tried to follow your suggestion from a previous comment but Access when i saved the query created what i had above with the select from bits, anyway i think the whole "simplest solution is probably the best" is where i should be going and whilst i had initially an AddNew/Update setup with lots of other stuff and migrated to an insert query in this instance i should stick to that

thank you once again for your response i appreciate it