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:
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:
The list of type definitions i got from Field.Type Property
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
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:
Code:
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:
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