> 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
> --
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
>
>
> "Jon" wrote:
>
> > 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...
> >
> >