Solved

Automating data entry in Access97

Posted on 1998-07-24
5
201 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

691 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