Daily Importing from excel to access main db

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a nutshell, here goes...
I receive a daily excel file from IT containing about 200 to 500 records.
These records are then audited/annotated and imported back into a master list
every day. My predecessor used excel for his master database, which
overtime, grew to over 20,000 records (I estimate closer to 100,000 this
year) and was completely unmanagable for any kind of analysis functions.
I know that Access is a much better repository for this master list but my
problem is that I'm unsure of the best way to import these daily files.
Normally I would simply import the daily excel file into a temp file on
access and do an apped query to the master - this was no problem for me.

But because of "our corp policies" I need kind of an idiot-proof method of
either VBA or SQL coding (or whatever...) that will let ANYBODY be able to
import these daily excel files into a master-list on access without a lot
(preferably any) knowledge of query design, etc... Maybe a control on a form
that will prompt for the excel filename to be imported, a point-and-click
query... I don't really know...

any help is appreciated.
 
I figured it out on my own... If anyone's interested, here's the code I
used. I'm new to this so it's really sloppy, but it works.

Dim strtable As String
Dim strfilename As String
Dim strDir As String
Dim strMonth As String

strtable = Form_FrmCapUpdater.Text3
strfilename = strtable & ".xls"
strMonth = Form_FrmCapUpdater.MonthFolder & "\"
strDir = "Y:\2005 Credit Card Validation Automation ADM017\Daily Reports\"

Dim strPath As String

strPath = strDir & strMonth & strfilename

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strtable,
strPath, True, "a:V"

DoCmd.Rename "TblTempCap", acTable, strtable

Dim strqry As String

strtable = "TblTempCap"

strqry = {my sql query string}

DoCmd.RunSQL strqry, False

DoCmd.DeleteObject acTable, "TblTempCap"
 
Back
Top