Automating Access To Import Files

[kleinma]

Anyone have any code or know any examples of automating access using whatever method (i think it would be DAO) to import a text file using a predefined record setup into a table in access... kinda like using that import wizard.. but i want to be able to do it all through either VB or VBA code.... ANY IDEAS???

[darre1]

i know how to open a connection to an excel file if that helps???

[swoozie]

Name for function or module

DoCmd.SetWarnings False

Filename = "Place the location of file here\Filename here.txt"


DoCmd.TransferText acImportDelim, "If you have an import specification named saved it goes here", "Table name here", Filename, 0


MsgBox "Import is Completed"

DoCmd.SetWarnings True

End Sub

[kleinma]

swoozie i will check this out and let you know how it works.. thanks..

[Chick]

Here is a sample of code I use to import a text file into a Access table. I use pipes to seperate each field so the code is looking for the first, second, third etc pipes until there are no more and each time it reads in the field between each pipe and copies it into a table:
GOOD LUCK!


If Mid(strData, 1, 1) = "|" Then 'Make sure we have a good file!

nPos = 1
For i = 0 To 200
'Strip off leading & Ending | on fields
nStart = InStr(nPos, strData, "|") 'Find position beginning |
nEnd = InStr(nStart + 1, strData, "|") 'Find position ending |
If nEnd = 0 Then Exit For 'End of record
nLen = nEnd - nStart - 1
work = Mid(strData, nStart + 1, nLen) 'Strip leading & ending |
If cr = Mid(work, 1, 1) Or lf = Mid(work, 1, 1) Then Exit For 'Look for end of record.
'=======================================================

'IF THIS IS THE FIRST RECORD :

If bFirst_DAT_Record Then ' Create DAT Table

bFirst_DAT_Record = False
mDAT_Table = "Packs" + mLabelType + mShopOrder
strSQL = "Select * into [;database=" +
mPCDataBase + "]." + _
mDAT_Table + " from Packs_Data"
Else 'Bad DAT file
Close #1
Import_Dat_File = False
Exit Function
End If
'Open the PC DB
On Error Resume Next
dbLODPC.TableDefs.Delete mDAT_Table 'Delete Table
dbLODPC.TableDefs.Refresh
dbLODPC.Recordsets.Refresh
On Error GoTo 0
dbLOD.Execute strSQL 'Copy an empty DAT table from server db to PC db
'Setup to start adding records to the DAT Table for this shop order.
Set rsDatTable = dbLODPC.OpenRecordset
(mDAT_Table)
rsDatTable.AddNew 'Create a new blank record.
End If
'==================================================================

'COPY EACH FIELD INTO TABLE:

If (i + 1) < 10 Then
strFldNumber = "00" + Trim(Str(i + 1))
Else
strFldNumber = "0" + Trim(Str(i + 1))
End If
strFldName = "FLD" + strFldNumber

nPos = nEnd
nSize = rsDatTable(i + 1).Size 'Get size of field
If Len(work) > nSize Then
rsDatTable(strFldName).Value = Mid(work, 1, nSize) 'Truncate trailing data
ElseIf Len(work) = 0 Then
rsDatTable(strFldName).Value = " " 'If no data set to 1 blank
Else
rsDatTable(strFldName).Value = work
End If
Next i
Else
Close #1
Import_Dat_File = False 'Bad file do not process!
Exit Function
End If
rsDatTable("LabelPrinted") = False
rsDatTable.Update
rsDatTable.MoveLast

nRequested = nRequested + Val(rsDatTable("FLD004")) 'Get total labels requested.

strData = Get_DAT_Record(dotdat, bGotDATRecords)
If strData <> "*****" Then rsDatTable.AddNew
Loop
rsDatTable.MoveFirst
Import_Dat_File = True
End Function

[kleinma]

thanks but the file is a download from our mainframe so there is no way i would be able to insert pipes in it.. it is about 200000 records per download... we have it set up to import now and everything.. but we have to do it manually.. that is why i want to import it using code...

[kleinma]

thanks swoozie that worked like a charm...

[swoozie]

I am glad I could help for a change.