Solved

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?

Posted on 2008-10-11
19
1,628 Views
Last Modified: 2013-12-25
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
Comment
Question by:Chris Raisin
  • 10
  • 5
  • 4
19 Comments
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 62 total points
ID: 22697978
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 63 total points
ID: 22699134
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
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22699636
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
 
LVL 13

Author Closing Comment

by:Chris Raisin
ID: 31505391
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22701210
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
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22702077
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
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22821668
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22824690
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
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22828591
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Expert Comment

by:jmoss111
ID: 22828620
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
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22828727
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22828744
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
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22828773
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22828905
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
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22830891
OK...I am now closing this question (I think)
0
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22830959
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22833062
Yeah but it show us as point recipients. You'll need ZA
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22833574
crasin

Click the Request Attention button in your original post
0
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22836179
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now