Temp table to solve bloat issue

[Jon]

I got a problem that I'm hoping some fresh minds can help me out here.
First some background:

I have a table in an Access database which I need to update with new
information - call the table "Raw". Said new information comes in the
form of an Excel spreadsheet. The gotcha with the spreadsheet is that
there is a very good chance that there can be information within the
spreadsheet that is identical to some records within Raw, so I cannot
simply append the info from the spreadsheet to the end of Raw. So
here's what I wrote (note it uses Ken Getz's code to use the the Open
File dialog box):

'make sure bounce tables are empty
CurrentDb.Execute "DELETE * FROM Raw_copy", dbFailOnError
CurrentDb.Execute "DELETE * FROM spreadsheet", dbFailOnError
'find file to import
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)",
"*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
'import new Excel sheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, _
"spreadsheet", strInputFileName, True
'append info to end of Raw
DoCmd.SetWarnings False
strSQL = "INSERT INTO Raw SELECT spreadsheet.* FROM spreadsheet;"
DoCmd.RunSQL strSQL
'move to Raw_Copy to clear out any dupe info
strSQL = "INSERT INTO Raw_Copy SELECT Raw.* FROM Raw;"
DoCmd.RunSQL strSQL
'move back to Raw
CurrentDb.Execute "DELETE * FROM Raw", dbFailOnError
strSQL = "INSERT INTO Raw SELECT Raw_Copy.* FROM Raw_Copy;"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

The table "Spreadsheet" is simply an empty copy of the Raw table, left
there to let the import wizard know what field format it's working
with. The table Raw_copy is identical to the table Raw, except it has
the duplicate fields set as primary keys.

That all works fine, except I find the file bloats up about 3 times
after it's run for the first time after a database compact, and I have
no inclination to discover how much bigger it'll bloat each subsequent
import operation after the first! So what I would like to do is to move
the assorted copy functions over to a temp database, using the info
found here: http://www.granite.ab.ca/access/temptables.htm

So here's what I've done so far:

'make sure bounce tables are empty
CurrentDb.Execute "DELETE * FROM Raw_copy", dbFailOnError
CurrentDb.Execute "DELETE * FROM spreadsheet", dbFailOnError
'find file to import
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)",
"*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
'create temp mdb to hold data while we work on it
Set wrkDefault = DBEngine.Workspaces(0)
strTempDatabase = Left$(CurrentDb.Name, Len(CurrentDb.Name) - 4) &
" temp.mdb"
If Dir(strTempDatabase) <> "" Then Kill strTempDatabase
Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase,
dbLangGeneral)
'copy spreadsheet table over
DoCmd.CopyObject strTempDatabase, , acTable, "spreadsheet"
'copy Raw_copy table over
DoCmd.CopyObject strTempDatabase, , acTable, "Raw_copy"
'copy Raw table over
DoCmd.CopyObject strTempDatabase, , acTable, "Raw"

Which works. Here's the newbie question: how do I tell the macro that
the next few operations from the "import the Excel spreadsheet" through
to "move to Raw_copy" are to be done with the tables in the temp
database and not with the ones in the main version?

Mind you, if someone can point out a cleaner way of doing all this, be
my guest...

 

[SteveS]

Hi Jon,

Which works. Here's the newbie question: how do I tell the macro that
the next few operations from the "import the Excel spreadsheet" through
to "move to Raw_copy" are to be done with the tables in the temp
database and not with the ones in the main version?

To be picky, it's not a "Macro", it's code. In any case, here is a link
to a site, "Rogers Access Library" that has a sample MDB that might help you:

http://rogersaccesslibrary.com/download3.asp?SampleName=ImportToTempDatabase.mdb


If the above example works for you, you could then link to the temp tables
in the temp MDB, compare the records deleting the duplicates from the temp
table, then append the remaining records to the production MDB.

I know the TransferSpreadsheet function is easy to use, but I don't like it
because of problems like you are running into.. So I "brute force" it using
code.

I have to import up to 2000 non duplicate new records (up to 22 columns
wide) at a time from an Excel spreadsheet. So I wrote a custom import
subroutine. First I review the Excel SS to check for gross errors. Then I
save it in CSV format. A button starts the import, validating each record. If
there is a duplicate record, it writes it out to an Errors.txt text file for
review/corrections/manual entry. More work in the beginning, but better
control (IMO). I also use Ken Getz's Open File code to select the CSV file.
(Thanks Ken) Also, using a CSV eliminates the temp tables and reduces bloat.

I am also looking into linking to the spreadsheet instead of using a CSV and
doing the validation on-the-fly, but still writing errors to a text file.


"The Access Web" website at

http://www.mvps.org/access/tables/tbl0015.htm


has subs (at the bottom of the page) for linking and unlinking tables using
code.

HTH

 

[Jon]

Thank you... exactly the info I needed. From you said I realized I
needed to do two things: make sure the code was looking at the proper
table by renaming the copied tables, and also that I attach the tables
from the temp mdb to the main mdb.