Solved

Automating data entry in Access97

Posted on 1998-07-24
5
192 Views
Last Modified: 2012-08-13
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
Comment
Question by:Beans0063
5 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 1957249
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
 
LVL 9

Expert Comment

by:perove
ID: 1957250
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
 

Author Comment

by:Beans0063
ID: 1957251
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
 
LVL 8

Expert Comment

by:Helicopter
ID: 1957252
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
 
LVL 9

Accepted Solution

by:
cymbolic earned 50 total points
ID: 1957253
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now