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
(the current output from the program can be viewed on the web by going to:
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("Calen
Set CalFlds(0) = CalTd.CreateField("Subject
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("Reminde
r On/Off", dbBoolean)
Set CalFlds(7) = CalTd.CreateField("Reminde
r Date", dbDate)
Set CalFlds(8) = CalTd.CreateField("Reminde
r Time", dbDate)
Set CalFlds(9) = CalTd.CreateField("Meeting
Set CalFlds(10) = CalTd.CreateField("Require
d Attendees", dbText)
Set CalFlds(11) = CalTd.CreateField("Optiona
l Attendees", dbText)
Set CalFlds(12) = CalTd.CreateField("Meeting
Set CalFlds(13) = CalTd.CreateField("Billing
Set CalFlds(14) = CalTd.CreateField("Categor
Set CalFlds(15) = CalTd.CreateField("Descrip
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
'Open the Access database
'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, ""
'Release the pointers
Set fs = Nothing
Set accApp = Nothing
Set MyWs = Nothing