Solved

Automating data entry in Access97

Posted on 1998-07-24
5
193 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

932 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

14 Experts available now in Live!

Get 1:1 Help Now