Solved

Automating data entry in Access97

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error 438 6 50
vb.net dbnull syntax 1 52
Add Underline to custom Caption on Label 4 34
Ms Access 2010 Setup (Executable file) 4 65
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

737 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