• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Automating data entry in Access97

Does anyone know if there's a way to have access automatically add to a table from a standard csv text file?  Something along the lines where the user clicks a button, finds the data file, then access does the rest of the work.
0
Beans0063
Asked:
Beans0063
1 Solution
 
deightonCommented:
Yes you can create a Basic module, in this module open your text file with Open and read  the file using Line Input #1 or input #1.

You'll have to devise code to process your file and the send it to your form using SendKeys

0
 
peroveCommented:
I think it is a better solution to link the csv file into the Db and then treat it as if it is a table. Then U can use Dlookup or search or whtever you want in code on thet recordset
perove
0
 
Beans0063Author Commented:
That's exactly what I want to do - Link the entire csv file to an existing table.  Everything is real standard here... the import wizard can do the job in one click.  Is there simply a way to hide this process from the user?
0
 
HelicopterCommented:
Have a look at the Transfer Text method in help. You can specify a filename (which you could get from a common dialog control) and a destination table.
0
 
cymbolicCommented:
Basically, you manually import the file one time, using the advanced button on the import window to define and name a transfer spec.  Then you can automatically import the text file in a vba module, kicked off by a macro from the command line, if necessary.  Here's a copy of a vba module that imports a number of files at one time that I wrote:

Option Compare Database
Option Explicit
Declare Function GetDOSName& Lib "kernel32" Alias "GetShortPathNameA" _
(ByVal lpszLongPath As String, ByVal lpszShortPath As String, ByVal cchBuffer _
As Long)

Function ImportAll()
 Dim dbs As Database, x$, y$, baldt$
 Dim tdf As TableDef, i As Integer
 Dim rst As Recordset
 Set dbs = CurrentDb
 
' Set dbs = OpenDatabase(x$ + "COMMON.MDB")
Application.Echo True, "Cleaning Out Database"
DoEvents
 With dbs
  For Each tdf In .TableDefs
   If InStr(tdf.Name, "_ImportErrors") > 0 Then
    x$ = "Drop Table " & tdf.Name
    dbs.Execute x$
'   ElseIf InStr(tdf.Name, "MSys") = 0 And InStr(tdf.Name, "XOrg") = 0 Then
'    x$ = "Delete * from " + tdf.Name
'    dbs.Execute x$
   End If
  Next tdf
 End With
 x$ = dbs.Name        'get the path
 x$ = DosName$(x$)
 While Right$(x$, 1) <> "\"
  x$ = Left$(x$, Len(x$) - 1)
 Wend
 
 If IsFile(x$ + "Patient.LOD") Then
  dbs.Execute "Delete * From Patient"
  DoCmd.TransferText acImportFixed, "Patient", "Patient", x$ + "Patient.LOD"
  DoEvents
  Kill x$ + "Patient.LOD"
  Application.Echo True, "Building Patient ID"
  Set rst = dbs.OpenRecordset("Patient", dbOpenDynaset)
  With rst
   While Not .EOF
    If IsNull(!FirstName) Then
     y$ = Left$(!Lastname, 6)
    Else
     y$ = Left$(!Lastname, 3) + " " + Left$(!FirstName, 2)
    End If
    .Edit
    !ID = y$
    .Update
    .MoveNext
   Wend
   .Close
  End With
  Set rst = Nothing
 End If
 If IsFile(x$ + "Admit.LOD") Then
  dbs.Execute "Delete * From Admission"
  DoCmd.TransferText acImportFixed, "Admit", "Admission", x$ + "Admit.LOD"
  DoEvents
  Kill x$ + "Admit.LOD"
 End If
 If IsFile(x$ + "Art.LOD") Then
  dbs.Execute "Delete * From BegBalDtl"
  DoCmd.TransferText acImportFixed, "Art", "BegBalDtl", x$ + "Art.LOD"
  DoEvents
  Kill x$ + "Art.LOD"

  'make begbal from begbaldtl
'  While Not IsDate(baldt$)
'   baldt$ = InputBox("Please Enter an Ending Date For A/R Balance Aging", "Enter A/R Balance Ending Date", Format$(Date$, "mm/dd/yy"))
'  Wend
'  For i = 0 To 90 Step 30
'   baldt$ = Format$(CDate(baldt$) - 30, "mm/dd/yy")
'   y$ = "INSERT INTO BegBal ( OrgSysNum, AdmitSysNum, PlanId, TranDate, BegBalAmt, GLdDate )"
'   y$ = y$ + " SELECT OrgSysNum, AdmitSysNum, PlanId, max(TranDate), sum(Amount), #" + baldt$ + "#"
'   y$ = y$ + " FROM BegBalDtl GROUP BY OrgSysNum, AdmitSysNum, PlanId "
'   y$ = y$ + "Where TranDate<=#" + baldt$ + "#"
'   Application.Echo True, "Creating Beginning Balances as of " + baldt$ + "..."
'   DoEvents
'   dbs.Execute y$
'   DoEvents
'  Next i
 End If
 If IsFile(x$ + "Finance.LOD") Then
  dbs.Execute "Delete * From Finance"
  DoCmd.TransferText acImportFixed, "Finance", "Finance", x$ + "Finance.LOD"
  Application.Echo True, "Please Wait, Building FinRespPty..."
  DoEvents
  Kill x$ + "Finance.LOD"
 End If
 If IsFile(x$ + "Frp.lod") Then
  'make FinRespPty from Admission
  Application.Echo True, "Creating Responsible Party..."
  dbs.Execute "Delete * From FinRespPty"
  dbs.Execute "Delete * from FinRespPtyPol"
  DoEvents
  y$ = "INSERT INTO FinRespPty ( FrpId, RelCode, OrgSysNum, AdmitSysNum )"
  y$ = y$ + " SELECT PatSysNum, '01', 1, AdmitSysNum FROM Admission"
  dbs.Execute y$
  DoEvents
  'Add to FinRespPty from Finance
  Application.Echo True, "Adding To Responsible Party..."
  DoEvents
  y$ = "UPDATE finresppty INNER JOIN finance ON finresppty.AdmitSysNum = finance.AdmitSysNum "
  y$ = y$ + "SET finresppty.RelCode = finance.rel,"
  y$ = y$ + "finresppty.LastName = finance.lastname,"
  y$ = y$ + "finresppty.FirstName = finance.firstname,"
  y$ = y$ + "finresppty.MidName = finance.miname,"
  y$ = y$ + "finresppty.Addr1 = finance.addr1,"
  y$ = y$ + "finresppty.Addr2 = finance.adr2,"
  y$ = y$ + "finresppty.City = finance.city"
  dbs.Execute y$
  DoEvents
  y$ = "Insert Into Finrespptypol (FrpID,AdmitSysNum,OrgSysNum,RelCode,PlanID,PlanSeqNum,"
  y$ = y$ + "ElgBegDat,CvgBegDat,EmpGrpPln,BilSeq) select FrpID,AdmitSysNum,OrgSysNum,RelCode,'SELF PAY',"
  y$ = y$ + "1,EffBegDat,EffBegDat,'N',1 from FinRespPty"
  dbs.Execute y$
  DoEvents
  y$ = "UPDATE finrespptypol INNER JOIN finance ON finrespptypol.AdmitSysNum = finance.AdmitSysNum "
  y$ = y$ + "Set finrespptypol.GroupName=Finance.Grp,finrespptypol.GroupNumber=finance.PolNum,"
  y$ = y$ + "finrespptypol.HicNumber=Finance.PolNum,finrespptypol.PlanID=finance.PyrAbb"
  dbs.Execute y$
  y$ = "UPDATE finrespptypol INNER JOIN Admission ON finrespptypol.AdmitSysNum = Admission.AdmitSysNum "
  y$ = y$ + "Set finrespptypol.CvgBegDat=Admission.AdmDate"
  dbs.Execute y$
  Kill x$ + "FRP.LOD"
 End If
 If IsFile(x$ + "payor.lod") Then
  dbs.Execute "Delete * From Ref_FndSrc"
  DoCmd.TransferText acImportFixed, "Payor", "REF_FNDSRC", x$ + "Payor.LOD"
  DoEvents
  Kill x$ + "Payor.LOD"
 End If
 If IsFile(x$ + "ub.lod") Then
  dbs.Execute "Delete * From UniVrslBil"
  DoCmd.TransferText acImportFixed, "UB", "UNIVRSLBIL", x$ + "UB.LOD"
  DoEvents
  Kill x$ + "UB.LOD"
  'Update univrslbil from finance
  Application.Echo True, "Please Wait, Updating UniVrslBil..."
  DoEvents
  y$ = "UPDATE UNIVRSLBIL INNER JOIN Finance ON UNIVRSLBIL.AdmitSysNum = Finance.AdmitSysNum "
  y$ = y$ + "SET UNIVRSLBIL.PlanId =finance.pyrabb,"
  y$ = y$ + "UNIVRSLBIL.ValCod1 =finance.valcod1,"
  y$ = y$ + "UNIVRSLBIL.ValCod2 =finance.valcod2,"
  y$ = y$ + "UNIVRSLBIL.ValCod3 =finance.valcod3,"
  y$ = y$ + "UNIVRSLBIL.ValCod4 =finance.valcod4,"
  y$ = y$ + "UNIVRSLBIL.ValCod5 =finance.valcod5,"
  y$ = y$ + "UNIVRSLBIL.ValAmt1 =finance.valamt1,"
  y$ = y$ + "UNIVRSLBIL.ValAmt2 =finance.valamt2,"
  y$ = y$ + "UNIVRSLBIL.ValAmt3 =finance.valamt3,"
  y$ = y$ + "UNIVRSLBIL.ValAmt4 =finance.valamt4,"
  y$ = y$ + "UNIVRSLBIL.ValAmt5 =finance.valamt5"
  dbs.Execute y$
  DoEvents
 End If
 If IsFile(x$ + "empl.lod") Then
  dbs.Execute "Delete * From Ref_Empl"
  DoCmd.TransferText acImportFixed, "Empl", "Ref_Empl", x$ + "Empl.LOD"
  DoEvents
  Kill x$ + "Empl.LOD"
 End If
 If IsFile(x$ + "lic.lod") Then
  dbs.Execute "Delete * From Ref_EmplLic"
  DoCmd.TransferText acImportFixed, "EmpLic", "Ref_EmplLic", x$ + "Lic.LOD"
  DoEvents
  Kill x$ + "Lic.LOD"
 End If
 If IsFile(x$ + "Empr.lod") Then
  DoCmd.TransferText acImportFixed, "Empr", "Ref_EmplLic", x$ + "Empr.LOD"
  DoEvents
  Kill x$ + "Empr.LOD"
 End If
 If IsFile(x$ + "doctor.lod") Then
  dbs.Execute "Delete * From Ref_Phy"
  DoCmd.TransferText acImportFixed, "Doctor", "Ref_Phy", x$ + "Doctor.LOD"
  DoEvents
  Kill x$ + "Doctor.LOD"
 End If
 If IsFile(x$ + "Facil.lod") Then
  dbs.Execute "Delete * From Ref_Fac"
  DoCmd.TransferText acImportFixed, "Facil", "Ref_Fac", x$ + "Facil.LOD"
  DoEvents
  Kill x$ + "Facil.LOD"
 End If
 If IsFile(x$ + "service.lod") Then
  dbs.Execute "Delete * From Ref_Svc"
  DoCmd.TransferText acImportFixed, "Service", "Ref_Svc", x$ + "Service.LOD"
  DoEvents
  Kill x$ + "Service.LOD"
  If IsFile(x$ + "servfee.lod") Then
   dbs.Execute "Delete * From Ref_SvcFeeCst"
   DoCmd.TransferText acImportFixed, "ServFee", "Ref_SvcFeeCst", x$ + "ServFee.LOD"
   DoEvents
   Kill x$ + "ServFee.LOD"
  End If
 End If
 If IsFile(x$ + "Medical.lod") Then
  dbs.Execute "Delete * From PlnOfTrt"
  DoCmd.TransferText acImportFixed, "Medical", "PlnOfTrt", x$ + "Medical.lod"
  DoEvents
  Kill x$ + "Medical.lod"
 End If
 If IsFile(x$ + "CertDiag.lod") Then
  dbs.Execute "Delete * From CertDiag"
  DoCmd.TransferText acImportFixed, "CertDiag", "CertDiag", x$ + "CertDiag.lod"
  DoEvents
  Kill x$ + "CertDiag.lod"
 End If
 If IsFile(x$ + "MedText.lod") Then
  dbs.Execute "Delete * From CertText"
  DoCmd.TransferText acImportFixed, "MedText", "CertText", x$ + "MedText.lod"
  DoEvents
  Kill x$ + "MedText.lod"
 End If
 If IsFile(x$ + "BegBal.lod") Then
  dbs.Execute "Delete * From BegBal"
  DoCmd.OpenForm "ArAging"
  DoEvents
  Kill x$ + "BegBal.Lod"
 End If
 ImportAll = True
 dbs.Close
 'Shell x$ + "compact.bat"
 DoEvents
 'End
End Function
Private Function IsFile(fil$) As Boolean
 If Len(Dir$(fil$)) > 0 Then
  IsFile = True
 Else
  IsFile = False
 End If
End Function
Private Function DosName$(LongName$)
'Use api call to get short DOS Path/File Name
'Declare Function GetDOSName& Lib "kernel32" Alias "GetShortPathNameA" _
'(ByVal lpszLongPath As String, ByVal lpszShortPath As String, ByVal cchBuffer _
'As Long)
Dim sn$, ln$
Dim cchb As Long
ln$ = LongName$ + Chr$(0)
sn$ = Space$(65)
cchb = 65
cchb = GetDOSName&(ln$, sn$, cchb)
DosName$ = Left$(sn$, cchb)
End Function


0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now