Windows 7 64bit 
Access 2003
Hi,
New member, so allow me to introduce myself before throwing my problem at you. I have been working with Access since version 1, in a government, forest research setting, and developed a fairly complex database for research data. Part of this development is an "Importer", which takes bulk data, renames fields, converts values and then distributes the data to multiple tables.
I am now in the process of fine-tuning a CSV-format import, but seem to have run into a tiny little snag. The snag: I cannot import a temporary copy of the data CSV, because it was created just prior to the step to import it and is still open. The solution is probably very simple, but it evades me.
The following code takes mstrFileName, strips the first row, copies the remaining data to a temp file, then imports the temp file:
'================================================= ===
Dim fInput
Dim fOutput
Dim fs
Dim sline As String
Dim strTempTable As String
strTempTable = "temp.csv"
Set fs = CreateObject("Scripting.filesystemobject")
Set fInput = fs.opentextfile(mstrFileName) ' mstrFileName is previously defined object name for the original data CSV to be imported
Set fOutput = fs.createtextfile(strTempTable)
sline = fInput.readline ' skip the first line, as it contains field names (not needed)
Do While fInput.atendofstream <> True
sline = fInput.readline
fOutput.writeline sline
Loop
' HERE I NEED to CLOSE the temp csv file before the next step - open doc prevents import
' this next step imports the temp.csv - this works great if temp.csv already exists and I skip the code above
DoCmd.TransferText acImportDelim, "", strTable, strTempTable, False, ""
'================================================= ===
Any suggestions would be welcome!
Thanks,
Frank :-)
Reply With Quote
!
