By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,291 Members | 1,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,291 IT Pros & Developers. It's quick & easy.

TransferText method msaccess - Imported table corrupted

P: n/a
Hi,
I'm trying to import some text files in a simple access table, but
sometimes it miss some rows after import.
here is the code:
Public Function OpenMyApplicationData() As Boolean
Dim Upid As Boolean
Dim LastBD As Integer
Dim dbs As Database
Dim fileN As String
Dim tableN As String
Set dbs = CurrentDb
On Error GoTo Err_OpenMyApplicationData
Dim strMyApplicationDataFile As String
Dim strExitMessage As String
'windows common dialog- open file mode
strMyApplicationDataFile = OpenSaveCommonDialog("Open",
GetAppDir() & "MyDataSubdirectory", _
"Open MyApplication Data Files", "",
"MyApplication Data Files(*.txt)", "*.txt")
LastBD = find_last(strMyApplicationDataFile, "\")
fileN = "#" + Mid(strMyApplicationDataFile, LastBD + 1)
tableN = Mid(strMyApplicationDataFile, LastBD + 1,
Len(Mid(strMyApplicationDataFile, LastBD + 1)) - 4)
'test response
If (strMyApplicationDataFile = "") Then
OpenMyApplicationData = False
strExitMessage = "Attempt to open MyApplication Data Files
cancelled."
Else
'import the text file
OpenMyApplicationData = True
strExitMessage = ""
dbs.Execute ("DELETE * FROM pivoti;")
DoCmd.TransferText acImportFixed, "ImpSpec", "pivoti",
strMyApplicationDataFile, False
dbs.Execute ("DELETE * FROM pivoti WHERE field1 = '' or
field1 is NULL;")
' dbs.Execute ("UPDATE pivoti set ID=count(*);")
dbs.Execute ("INSERT INTO pivoti (ID,field1) values (1,'" +
fileN + "');")
Upid = Update_ID
dbs.Execute ("loadprop")
MsgBox "Table loaded correctly!"

End If
Exit_OpenMyApplicationData:
If Not (OpenMyApplicationData) Then
MsgBox strExitMessage, vbInformation, "MyApplication Software"
End If
Exit Function

Err_OpenMyApplicationData:
'on error fail the function
OpenMyApplicationData = False
strExitMessage = "Error during attempt to open MyApplication
Data Files (error# " & Err.Number & " " & Err.Description & ")"
Resume Exit_OpenMyApplicationData
End Function

the missing part (example)
SYMPATHIE=Simpatia <--- imported
#AccountInterestFeesCharges.jsp

#ACCOUNT_CHARGES
LIBCHARGES=SPESE
AVIS=Avviso
RELEVES=Estratti conto
MOUVEMENTS=Movimenti
EXTRAITS=Estratti
ANNUEL=Annuali
BOUCLEMENT=Chiusura processo
CDG=CDG
COM_BOUCLEMENT=Commissioni su chiusura processo
COM_MOUVEMENT=Commissioni su movimenti
APPL_TARIF=Prezzo applicato
Y/N=Y/N* (*:Par defaut)
YN = Y/N* (*:Par defaut)

#ACCOUNT_FEES
LIBFEES=Commissioni su chiusura processo
SANS=Senza
DEBITMAX=Addebito massimo
DEBITMOY=Addebito medio
TOTALDEB=Totale addebiti
MARGEPLFD=Margine massimo
PLAFOND=Su massimo
DEPASPLFD=Massimo superato
METHODE=Metodo 5+6
CREDITMOY=Accredito medio

#ACCOUNT_INTERESTS <---- imported

thank you for helping
regards

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.