473,890 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

FYI - Issue with TransferText in Access - VBA

This is a recommendation based on my research on an issue with the
transfer text functionality. The comments below were from a tech I
approched for help.

"The transfer text process blocked after importing 6139 records each
time on my side. After further research, there maybe a memory leak in
TransferText. When the number of records exceeds its limitation, the
transfer process blocked. Therefore, please abandon TransferText and
write own code to read text file line by line."

I was writing code to continually monitor a folder and import any text
files. Below is the recommended solution. Works much more quickly,
although the memory use still seems to reach extremely high levels when
left running for extended periods of time.

J
Private Sub ProcessFrame_Cl ick()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.

Dim ErrCounter
On Error GoTo Err_Errorclear

mfquit = False
Me![CurrentStatus] = "Processing ..."
Me![ESCMessage] = "Press ESC to Halt Message Processing."

StartProcess:
Do While Me![ProcessFrame] = 1 And mfquit = False

'Allow keystokes to process
DoEvents

Dim strFldr As String
Dim strFile As String
Dim intInputData As Integer
Dim rst1 As ADODB.Recordset

Dim fl As Long
Set rst1 = New ADODB.Recordset
rst1.Open "tblRawBroadcas tMessage", CurrentProject. Connection,
adOpenKeyset, adLockOptimisti c

Dim MyChar As String
Dim MyString As String

strFldr = Me![ImportFolderDes t]
strFile = Dir(strFldr)
intInputData = FreeFile()

If Len(strFile) > 0 Then
Open Me![ImportFolderDes t] & strFile For Input As #intInputData
Do While Not EOF(intInputDat a) ' Loop until end of file.
rst1.AddNew
f1 = LOF(intInputDat a)

MyChar = Input(f1, #intInputData) ' Get one character.
rst1("Message") = MyChar
rst1.Update
Loop
'delete the file (consider moving it to an Archive folderinstead.)
Close #intInputData
FileCopy Me![ImportFolderDes t] & strFile, Me![ProcessFolderDe st] &
strFile
Kill Me![ImportFolderDes t] & strFile

strFile = ""

rst1.Close
Set rst1 = Nothing
End If

'If there is more than one days worth of production in table on Monday,
export archive file and clear table (files older than 7 days)
If Weekday(Now) = 2 Then
If DCount("[DateCreated]", "tblRawBroadcas tMessage",
"[DateCreated]<>null") > 37350 Then
mydate = Now
DoCmd.TransferT ext acExportFixed, "ExportTextSpec ",
"qryExportText" , "C:\BroadcastPr ocessing\SCE Messages\Archiv e\" &
Me![SystemName] & "-EBroadcastArchi ve-" & Format(mydate, "yyyymmdd") &
".txt", False, ""
DoCmd.SetWarnin gs False
DoCmd.OpenQuery "qryDeleteD ata"
DoCmd.SetWarnin gs True
End If
End If
Loop
Me![ProcessFrame] = 0
MsgBox "Process Cancelled. Messages will not be Imported.", vbCritical
Me![CurrentStatus] = "Process Cancelled!"
Me![ESCMessage] = ""
GoTo EndProcess

Err_Errorclear:
Err.Clear
Resume StartProcess
EndProcess:
End Sub

Feb 15 '06 #1
0 1421

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
3059
by: Vladislav Moltchanov | last post by:
I have discovered a couple of years ago, that import with DoCMD.TransferText for CSV text file doesn’t work in Acc2000, while it works perfectly in ACC97. which has been discussed on this newsgroup forum, so this fact was fixed. Since I have to transfer files from VMS( SAS ) to WIN (Access), I still keep using ACC97 as a simplest and reliable tool. However, final products, such as distributable data entry system for multi central...
4
8075
by: axelleforever | last post by:
Bonjour, La première colonne de mon fichier texte (séparateur points-virgules) contient des valeurs alphanumériques (1,2a,3u etc). Je constate que seules les valeurs numériques sont importées dans la première colonne de ma table "MaTable". Expression vba utilisée : DoCmd.TransferText acImportDelim, , "MaTable", "C:\fichier.txt", True
3
12719
by: holdemfoldem | last post by:
Hi. I'm new to this board and have a few questions about using the method referred to in the topic of this message. I have manually transferred over 1/2 million records from a text file into my Access table in under 1 minute, using File>Get External Data>Import, then specifying .txt as the data source, selecting the text file, and walking through the resulting Wizard. I'm thinking I can do the same thing with DoCmd.TransferText, but...
3
9438
by: Oliver Gabriel | last post by:
Hi, i want to export a table for later import, using vba. That´s my code: export: filename = "C:\HVOtabelle.txt"
2
4634
by: carrionk | last post by:
Hi I'm trying to export an ADO Recordset generated at Runtime to a txt file. I know that you have two options for exporting Access information: 1) TransferText 2) Reading / Writing Text Files Does Transfertext allow usage of Recordsets as source of information?
3
2557
by: Daron | last post by:
I am using this command to import text file: DoCmd.TransferText acImportDelim, str_SpecName, str_TblName, str_FileName, False - The import will always be missing the first line in the text file. - These text files are coming out of Access. - The first line is formatted exactly the same and end with the same CR/LF that the rest of the file has.
1
5326
by: Ryan | last post by:
Hello. I was hoping that someone may be able to assist with an issue that I am experiencing. I have created an Access DB which imports an Excel File with a particular layout and field naming. Next the user can go into a Form which basically a dynamic query with a friendly interface that eventually outputs a table that is stored in the DB as well as exported to a CSV file. The CSV file is then used with a vendor solution to fill in...
0
2039
by: Sean Howard | last post by:
I have a strange problem linking tab delimited text files in Access 2000 (I am running Windows XP), please try this and let me know if I am going mad. Step 1. Create the tab-delimited text file below in WordPad and call the file "HeaderYES.txt" (the space between the two fields is actually a tab):- "Column1" "Column2" 1 "Line1"
7
6033
ollyb303
by: ollyb303 | last post by:
Hi, I am having a bit of a problem with TransferText macro. I am using TransferText, Export Delimited (no field names) to export the results of a query as a .csv file. The query is based on a linked table and uses a CDate() expression to convert the timestamp field (Nom_Date) from the table to a date type and the criteria: Between (Now()-183) And Now() to narrow results to the last 6 months. The query works just fine on its own and...
0
9978
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9822
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11232
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9633
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7169
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5851
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6045
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4676
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3277
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.