Filtering Code

[George Too]

I have this code to import an Excell spreadsheet:

'--- Declare variables
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim rst As Recordset
Dim Msg, BoxTitle, DlgStyle As String

'--- open the workbook
Set objXL = New Excel.Application
objXL.Visible = False

'--- Instructions passed to Excel
Workbooks.Open Filename:=Me!txtPath

'--- turn warnings off
DoCmd.SetWarnings False

'--- Import a spread sheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblMain", [Forms]![frmImportData]![txtPath], True

Now:
Where do I insert filtering code so that records dont duplicate in tblMain (the destination table)?
And, what would this code look like?

I posted a similar topic some time ago but that did not work for me as this is an automatic-one-button-pushing-deal.

Thanks for your responses.

 

[jjturner]

First off, in order to carry out your TransferSpreadsheet method, automation isn't necessary (you don't need to create the Excel object and open the file, etc). But for what you want to do, you will have to use automation.

The cleanest solution that I can think of is to capture all your Excel data (presuming it's in 'list' form) into an array. Once you have that data in an array variable, you can iterate through each element of that array and compare it to tblMain records to determine if you have a duplicate. If the array 'row' is not a duplicate based on your criteria, then you can AddNew into the recordset of tblMain.

HTH,
John

 

[George Too]

Ok, that sounds more like what I need. The other problem is that I don't have much knowledge of VBA so I have no idea where to start. Any pointers? Or where can I read more about it and have sample code?

Thank you.

 

[jjturner]

I must admit, my tendency is to envision the most complex solution, and gradually work my way down to a simple one.

After having another think about what you're trying to do, I realized that it is possible to achieve a 'prevent duplicates' import from Excel just by using the TransferSpreadsheet method. (In fact I've done this quite a bit.)

If your table in Access has a PrimaryKey or an index that requires Unique records, then this/these table definitions are sufficient to screen and prevent duplicates from being imported into it. You just have to be clear on what your criteria for a duplicate, or conversely, a unique record is.

So all you need as far as code would be the following:

'--- Import a spread sheet 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblMain", [Forms]![frmImportData]![txtPath], True

While running, your table index contraints will be enforced and Access will inform you if it encountered any key violations during your import (i.e. - duplicate records).

This solution will save you much time and effort, rather than trying to pick up (learn) a bunch of code and programming concepts on the fly.

Regards,
John

 

[George Too]

Yeah, you are perfectly right. I was suggested that approach some time ago but I couldn't implemented at that point in time but I'll certainly give it a try again.
Now, regarding that "bunch of code and programming concepts", if you have them I'm willing to assimilate them.

Thank you.

 

[jjturner]

Well, as far as VBA goes, I went to the bookstore and picked up one of those big thick books that has an accompanying CD of sample DB's - can't remember the name exactly (s.t. like 'VBA for Access 97', its put out by WROX publishing, I believe, but I don't have it at hand at the moment). Between that book, MS help files, and this forum, I'm awash in resources. Work your way through the chapters in one of those 'big' books (they're all pretty much the same IMO as far as the basics go) and you'll be well on your way to having a good grasp of what you can do, and what would work best for the projects you're trying to develop.

Also note the posts by some of the senior members here. Their understanding of Database architecture/design really helps to keep one from getting carried away with needless code or ill-advised designs.

HTH,
John