• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1705
  • Last Modified:

Why doesn't "DoCmd.TransferText" method on an Access object import ALL the characters in a CSV file field into an Access Database memo field?

I am writing a calendar application for my Church as follows.
     1 Outlook exports its data to a CSV file in "Windows" format.
     2. I use VB6 code to transfer the data from the CSV file into an Access database using the
         subroutine I have written named: ImportCSVFile (see below).
     3. My program then filters the Access data to process only records between
         certain dates, writing each record out to a HTML file for eventual display in a
         browser.

         (the current output from the program can be viewed on the web by going to:
          www.StThomasTheApostle.org.au/calendar.html)

My problem is that the method ".DoCmd.TransferText" within my procedure "ImportCSVFile" appears to truncate one of the fields before placing it in the Table. The table destination field is a MEMO field (since I sometimes may have more than 256 characters).

I viewed the CSV file and confirmed that the field data being imported contains  471 characters, but the method is transferring only the first 261 characters into the MEMO field.

Can anyone explain why this is happening and whether there is a way to successfully transfer all the characters - perhaps my code is faulty????

As a matter of interest the field being imported from the CSV file is NOT the last field in each of the CR/LF delimited lines of that file.

I use Access 2003 interactively on my system, but the VB Code seems to create an earlier version of the Access database when it is created (using code such as follows (the memo field in question is the last one):

                  Set CalTd = myDB.CreateTableDef("Calendar")
                  Set CalFlds(0) = CalTd.CreateField("Subject", dbText)
                  Set CalFlds(1) = CalTd.CreateField("Start Date", dbDate)
                  Set CalFlds(2) = CalTd.CreateField("Start Time", dbDate)
                  Set CalFlds(3) = CalTd.CreateField("End Date", dbDate)
                  Set CalFlds(4) = CalTd.CreateField("End Time", dbDate)
                  Set CalFlds(5) = CalTd.CreateField("All Day Event", dbBoolean)
                  Set CalFlds(6) = CalTd.CreateField("Reminder On/Off", dbBoolean)
                  Set CalFlds(7) = CalTd.CreateField("Reminder Date", dbDate)
                  Set CalFlds(8) = CalTd.CreateField("Reminder Time", dbDate)
                  Set CalFlds(9) = CalTd.CreateField("Meeting Organizer", dbText)
                  Set CalFlds(10) = CalTd.CreateField("Required Attendees", dbText)
                  Set CalFlds(11) = CalTd.CreateField("Optional Attendees", dbText)
                  Set CalFlds(12) = CalTd.CreateField("Meeting Resources", dbText)
                  Set CalFlds(13) = CalTd.CreateField("Billing Information", dbText)
                  Set CalFlds(14) = CalTd.CreateField("Categories", dbText)
                  Set CalFlds(15) = CalTd.CreateField("Description", dbMemo)
Option Explicit
 
Public Sub ImportCSVFile(cFileName as string, cDatabaseFileName As String,        
                                        cTableName As String)
 
'This subroutine imports data from a comma delimited file (Windows Format)
'into an Access database.
'    The three required parameters are:
'        1. the name of the CSV file to be imported (cFileName)
'                        (e.g. "C:\MyData\Calendar.CSV")
'        2. the name of the database (cDatabaseFileName)
'                        (e.g. "C:\MyDatabases\MyDataBase.mdb")
'         3. the name of the Table within that database into which the
'             delimited file is to be imported (e.g. "Calendar")
 
Dim MyWs As Workspace             'Pointer to workspace area in which Access 
                                                    'database will open
 
Dim accApp As Access.Application  'Pointer to Access database
 
Dim fs As Object                             'File pointer for Import File (cFileName)
 
'Set up a workspace in which to open the Access Database
Set MyWs = DBEngine.Workspaces(0)
 
'Set up a pointer to the Access database
Set accApp = CreateObject("Access.application")
 
'If Access 10 (97) or later then we can turn off
'the security alert that annoyingly pops up
If accApp.Version >= 10 Then
   accApp.AutomationSecurity = 1 ' msoAutomationSecurityLow
End If
 
'Open the Access database
accApp.OpenCurrentDatabase cDatabaseFileName
 
'Set up a file pointer to the text file
Set fs = CreateObject("Scripting.FileSystemObject")
'If the file exists then import the data
If fs.FileExists(cFileName) Then
  accApp.DoCmd.TransferText acImportDelim, "", cTableName, cFile, True, ""
End If
 
'Release the pointers
Set fs = Nothing
Set accApp = Nothing
Set MyWs = Nothing
 
End Sub

Open in new window

0
Chris Raisin
Asked:
Chris Raisin
  • 10
  • 5
  • 4
2 Solutions
 
jmoss111Commented:
Try exporting to Excel; I remember something about a bug doing this type of transfer. And Excel will only capture something like 1000 to 1300 characters.
0
 
Jeffrey CoachmanCommented:
craisin,

Why not simply Link the Calendar data directly into Access, thus eliminating the whole: Outlook-->CSV-->Access, process?

To Link Outlook to Access simply click:

File-->Get External Data-->Link tables
Select: "Outlook" from the "Files of Type" dropdown
Expand the object called "Personal Folders"
Select: "Calendar"

JeffCoachman
0
 
Chris Raisin(Retired Analyst/Programmer)Author Commented:
Jeff,
 
Is there a way I can do that with code? Perhaps using the "Access" engine?
If so, and it work, then that is the quick solution.
Ideally of course I should write the program with the assumption that the user may not have Microsoft Access nor Microsoft Excel on board. If I write code creating a "Class" object and then simply read each line of the CSV file in, assigning the delimited values to the appropriate class properties, I should successfully be able to deal with the MEMO problem.
I will go down that path anyway (being a purist) but if you know of a way that you can get an Access object to perform the "Get External Data" method that would be handy to know about.
I have decided to award you BOTH half points since I am sure the "Excel" method would work too. Again that is not a total solution since Excel has that limiting bug on the size of the import.
Thanks to you both!
I will come back and add a sample of my solution code using a class object as soon as I can.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Chris Raisin(Retired Analyst/Programmer)Author Commented:
Thanks for your help in trying to overcome this obvious bug in Microsoft Access.
Of course the best solution is for Microsoft to do the fix, but I might reach my 100th birthday before then :-) .... The best solution is hard coding via a class object (a lot more work) but greater control and no dependence on external "buggy" programs!
0
 
Jeffrey CoachmanCommented:
Once you link the table, no further user interatction in this regard is required.
The most up to date Calendar info will always be available.
0
 
Chris Raisin(Retired Analyst/Programmer)Author Commented:
Thanks for that Jeff.
 
I will investigate whether I can link via VB Code. If I can I will give it a try. Any success I will post to this discussion.
Cheers
Chris
 
0
 
Chris Raisin(Retired Analyst/Programmer)Author Commented:
I have solved the problem using the following method:
      1. Export the data from Outlook into a file in XML format
      2. Create a database with code to define the large fields being imported as MEMO
      3. Import the produced XML into the Access database.
 
When using "DoCmd.TransferText", behind the scenes the data is imported into an access database table which has ONLY "String" fields and so is subject to the size restriction for strings.
Using the Access object method "ImportXML" we can import into an already created Access table in which you have defined each field as either "string" or "memo". The only stipulation is that the field names in the table must match those in the XML file and be in the same order. They must also be either string or MEMO (do not define Integer or any other type). All imported XML is string in nature, but if the field accepting the data is MEMO in the access table, the entire contents of the large string field being imported is added to the table. NO DATA IS TRUNCATED!
I have attached the code I wrote to perform these functions. Simply change the name of the access table where required and the code should work for any situation after tweaking to suit the programmers requirements.
Cheers everyone!  :-)

Option Explicit
 
Public Function ExportCal2XML(cXMLFile As String, Optional datStart As Date, Optional datEnd As Date) As Integer
Const NODE_PROCESSING_INSTRUCTION = 7
Const NODE_ELEMENT = 1
Dim cTemp As String
 
Dim objDOM As Object, _
    objCalendar As Object, _
    objCal As Object, _
    objP As Object, _
    objData As Object, _
    olkItems As outlook.Items, _
    olkAppt As outlook.AppointmentItem, _
    intCount As Integer, _
    cStartDate As String, _
    cStartTime As String, _
    cEndDate As String, _
    cEndTime As String
 
intCount = Day(Date) - 1
If IsMissing(datStart) Then
  datStart = DateAdd("d", -intCount, Date)
  datEnd = DateAdd("m", 3, datStart)
End If
 
' Create the main xml node '
Set objDOM = CreateObject("MSXML2.DOMDocument")
Set objCalendar = objDOM.createNode(NODE_PROCESSING_INSTRUCTION, "xml", "")
objDOM.appendChild objCalendar
   
' Create the Parent Node - "calendar" '
Set objCalendar = objDOM.createNode(NODE_ELEMENT, "Calendar", "")
   
' Create a child node - "cal" '
Set objCal = objDOM.createNode(NODE_ELEMENT, "cal", "")
   
' Get the Outlook calendar items '
Set olkItems = Session.GetDefaultFolder(olFolderCalendar).Items.Restrict("[Start] > '" & Format(datStart & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(datEnd & " 23:59pm", "ddddd h:nn AMPM") & "'")
olkItems.Sort "[Start]"
For Each olkAppt In olkItems
    DoEvents
    Set objP = objDOM.createNode(NODE_ELEMENT, "Calendar", "")
    objCal.appendChild objP
    
    ' Add Subject '
    Set objData = objDOM.createNode(NODE_ELEMENT, "Subject", "")
    If Len(olkAppt.SUBJECT) = 0 Then olkAppt.SUBJECT = Space$(10)
    objData.Text = olkAppt.SUBJECT
    objP.appendChild objData
    
    ' Add Start Date '
    Set objData = objDOM.createNode(NODE_ELEMENT, "StartDate", "")
    objData.Text = Left(olkAppt.Start, 10)
    objP.appendChild objData
    
    ' Add Start Time '
    Set objData = objDOM.createNode(NODE_ELEMENT, "StartTime", "")
    cTemp = Mid$(olkAppt.Start, 11)
    If Len(cTemp) = 0 Then cTemp = Space$(10)
    objData.Text = cTemp
    objP.appendChild objData
    
    ' Add End Date '
    Set objData = objDOM.createNode(NODE_ELEMENT, "EndDate", "")
    objData.Text = Left(olkAppt.End, 10)
    objP.appendChild objData
    
    ' Add End Time '
    Set objData = objDOM.createNode(NODE_ELEMENT, "EndTime", "")
    cTemp = Mid$(olkAppt.End, 11)
    If Len(cTemp) = 0 Then cTemp = Space$(10)
    objData.Text = cTemp
    objP.appendChild objData
    
    ' Add All Day Event '
    Set objData = objDOM.createNode(NODE_ELEMENT, "AllDayEvent", "")
    objData.Text = olkAppt.AllDayEvent
    objP.appendChild objData
    
    ' Add Description '
    Set objData = objDOM.createNode(NODE_ELEMENT, "Description", "")
    If Len(olkAppt.Body) = 0 Then olkAppt.Body = Space$(10)
    objData.Text = olkAppt.Body
    objP.appendChild objData
    
    ' Add Categories '
    Set objData = objDOM.createNode(NODE_ELEMENT, "Categories", "")
    cTemp = olkAppt.CATEGORIES
    If Len(cTemp) = 0 Then cTemp = Space$(10)
    objData.Text = cTemp
    objP.appendChild objData
    
    'Add Location
    Set objData = objDOM.createNode(NODE_ELEMENT, "Location", "")
        If Len(olkAppt.LOCATION) = 0 Then olkAppt.LOCATION = Space$(10)
    objData.Text = olkAppt.LOCATION
    objP.appendChild objData
    ' Add the data to the Cal node '
    
    objCal.appendChild objP
    Set objP = Nothing
    intCount = intCount + 1
Next
   
 ' Append "Cal" to "Calendar" '
 objCalendar.appendChild objCal
 Set objCal = Nothing
   
 ' Append "Calendar" to the XML Dom Document '
 objDOM.appendChild objCalendar
 Set objCalendar = Nothing
   
 ' Change the name and path of the output file.'
 objDOM.Save cXMLFile
  
 ' Cleanup '
 Set objDOM = Nothing
 Set objCalendar = Nothing
 Set objCal = Nothing
 Set objP = Nothing
 Set objData = Nothing
 Set olkItems = Nothing
 Set olkAppt = Nothing
 
 ExportCal2XML = intCount
End Function
 
'=======================================================================
 
 
Option Explicit
 
Public Function CreateDatabase() As Workspace
'Note: the creation of the indexes at the end of the
'function are not really required since the data being imported
'will already be in date and time order (the way Outlook exports its data)
 
Dim myDB As Database, MyWs As Workspace
Dim CalTd As TableDef
Dim CalFlds(21) As Field
Dim CalIdx As Index
Dim CalQuery As QueryDef
Dim SQLstr As String
Dim fs as object
Dim x As Integer
 
'Set up a file pointer to the database file
Set fs = CreateObject("Scripting.FileSystemObject")
'If the file exists then erase the file
If fs.FileExists("C:\StThomasTheApostle.mdb") Then
  Kill "C:\StThomasTheApostle.mdb"
End If
 
Set MyWs = DBEngine.Workspaces(0)
 
Set myDB = MyWs.CreateDatabase("C:\StThomasTheApostle.mdb", dbLangGeneral, dbVersion30)
 
Set CalTd = myDB.CreateTableDef("Calendar")
 
Set CalFlds(0) = CalTd.CreateField("Subject", dbText)
Set CalFlds(1) = CalTd.CreateField("StartDate", dbText)
Set CalFlds(2) = CalTd.CreateField("StartTime", dbText)
Set CalFlds(3) = CalTd.CreateField("EndDate", dbText)
Set CalFlds(4) = CalTd.CreateField("EndTime", dbText)
Set CalFlds(5) = CalTd.CreateField("AllDayEvent", dbInteger)
Set CalFlds(6) = CalTd.CreateField("Categories", dbText)
Set CalFlds(7) = CalTd.CreateField("Description", dbMemo)
Set CalFlds(8) = CalTd.CreateField("Location", dbMemo)
For x = 0 To 8
  CalTd.Fields.Append CalFlds(x)
Next
myDB.TableDefs.Append CalTd
 
SQLstr = "Select * from Calendar where [StartDate] >= " + CStr(frmMain.CalendarFrom.Value) + _
         " and [StartDate] <= " + CStr(frmMain.CalendarTo.Value) + " order by [StartDate],[StartTime],[Categories]"
 
CreateIndexes
End Function
 
Public Function CreateIndexes()
Dim myDB As Database, MyWs As Workspace
Dim CalTd As TableDef
Dim CalIdx As Index
Dim x As Integer
 
Set MyWs = DBEngine.Workspaces(0)
 
Set myDB = MyWs.OpenDatabase("C:\StThomasTheApostle.mdb")
Set CalTd = myDB.TableDefs(0)
 
With CalTd
  ' Create new index, create and append Field
  ' objects to its Fields collection.
  Set CalIdx = CalTd.CreateIndex("Calendar")
 
  With CalIdx
    .Fields.Append .CreateField("StartDate")
    .Fields.Append .CreateField("StartTime")
    .Fields.Append .CreateField("Categories")
  End With
 
  ' Add new Index object to the Indexes collection
  .Indexes.Append CalIdx
  .Indexes.Refresh
End With
End Function
 
'=======================================================================
 
 
Option Explicit
 
Public Function ImportXMLFile(cXMLFile As String, cDatabaseFileName As String, cTableName As String) As Integer
 
'This subroutine imports data from a comma delimited file (Windows Format)
'into an Access database.
'    The three required parameters are:
'        1. the name of the XML file to be imported (cFileName)
'                        (e.g. "C:\MyData\Calendar.XML")
'        2. the name of the database (cDatabaseFileName)
'                        (e.g. "C:\MyDatabases\MyDataBase.mdb")
'         3. the name of the Table within that database into which the
'             delimited file is to be imported (e.g. "Calendar")
 
Dim MyWs As Workspace             'Pointer to workspace area in which Access
                                                    'database will open
 
Dim accApp As Access.Application  'Pointer to Access database
 
Dim fs As Object                             'File pointer for Import File (cFileName)
 
'Set up a workspace in which to open the Access Database
Set MyWs = DBEngine.Workspaces(0)
 
'Set up a pointer to the Access database
Set accApp = CreateObject("Access.application")
 
'If Access 10 (97) or later then we can turn off
'the security alert that annoyingly pops up
If accApp.Version >= 10 Then
   accApp.AutomationSecurity = 1 ' msoAutomationSecurityLow
End If
 
'Open the Access database
accApp.OpenCurrentDatabase cDatabaseFileName
 
'Set up a file pointer to the text file
Set fs = CreateObject("Scripting.FileSystemObject")
'If the file exists then import the data
If fs.FileExists(cXMLFile) Then
  accApp.ImportXML cXMLFile, acAppendData
End If
 
accApp.CloseCurrentDatabase
 
'Release the pointers
Set fs = Nothing
Set accApp = Nothing
Set MyWs = Nothing
 
End Function

Open in new window

0
 
Jeffrey CoachmanCommented:
If you solve your own issue and provide the procedure (both of which you have done), you are allowed to accept your own solution.

I have no issue with this.
You can click the "Request Attention" button to have this changed, if you like.

Congratulations!
;-)

JeffCoachman
0
 
Chris Raisin(Retired Analyst/Programmer)Author Commented:
Jim,
Would you be agreeable for me to take the action suggested by Jeff above?
Thanks, by the way, Jeff, very kind of you. If Jim is also agreeable I will click the "Request Attention"  button as suggested.
Cheers
Chris
0
 
jmoss111Commented:
Hi Chris, I have absolutely no problem with that; maybe I need to award you some points because you shared with me a solution.

Jim
0
 
Chris Raisin(Retired Analyst/Programmer)Author Commented:
Thanks Guys,
I have clicked the "Needs Attention" button hoping that the "powers that be" will allow me to award the solution to myself (I often talk tyo myself, but awarding myself the solution seems strange!....LOL)
When I finally retire I might try to become a "guru" like you all seem to be. I think it is a GREAT way for a programmer/analyst to spend his time helping others and I still think that my annual payment to EE is the best money I spend each year.
I await some response from someone sometime...maybe.
 
Cheers
Chris
 
0
 
jmoss111Commented:
Chris, Points are fun but I find that I get as much or more than I give. I'm a beginner here but Jeff is the real thing. I've been a member since 2001 but just really started participating a couple of months ago.

The current financial debacle has taken care of my retirement, I'll be working til I'm 420...

Regards,

Jim

0
 
Chris Raisin(Retired Analyst/Programmer)Author Commented:
Adjudicator....
There a a couple of very minor  "changes" I would like to make to the code (just to clarify) but I don't seem to be able to do that....It seems I would have to post the entire code again as an attachment it, I think.
Is that correct, or is there a way to amend the code after you have posted it?
Cheers
0
 
Jeffrey CoachmanCommented:
jmoss111 ,

"Points are fun but I find that I get as much or more than I give."
Same here.
;-)

"I'm a beginner here but Jeff is the real thing."
There have been scores of Experts who came online after me, and promptly surpassed me in points in a matter of months (DatabaseMX comes to mind right away)

I am sure you can do the same, if you wanted to.
;-)

Jeff
0
 
Chris Raisin(Retired Analyst/Programmer)Author Commented:
OK...I am now closing this question (I think)
0
 
Chris Raisin(Retired Analyst/Programmer)Author Commented:
I cant see how to add a comment so that I can accept my comment as a solution, as per instructions.
It just appears that the question is only open to discussion....
 
Please help
 
0
 
jmoss111Commented:
Yeah but it show us as point recipients. You'll need ZA
0
 
Jeffrey CoachmanCommented:
crasin

Click the Request Attention button in your original post
0
 
Chris Raisin(Retired Analyst/Programmer)Author Commented:
There seems to be a time limitation on the "RA" button...it says I have to wait a while.
 
I will come back in a week.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 10
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now