MS Access - Import data from Excel to Access using Criteria

Hi,

I have a form that import data from excel from Access. I have written a code that Import data but i want to Import data on the basis of criteria as if the field datatype has text as "Cap" it should import that data to table "tblCap", if it have text as "Gen" it should import all those rows to "tblGen", is there any way to do that?

Attach is a code for import.

Thank you.
Function selectFile()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
    With fd
        If .Show Then
            selectFile = .SelectedItems(1)
        Else
            'stop execution if nothing selected
            End
        End If
    End With
 
    Set fd = Nothing
End Function


Private Sub Command0_Click()
On Error GoTo ErrorHandler
    'disable ms access warnings
    DoCmd.SetWarnings False
 
    'Delete existing data in table
    'This is optional. If you are combining spreadsheets
    'in your table, don't run this query
    'DoCmd.RunSQL "DELETE * FROM names"
 
    'load spreadsheet
    DoCmd.TransferSpreadsheet acImport, 8, "tblCapacity", selectFile(), True
 MsgBox "Import Successful"
    're-enable ms access warnings
    DoCmd.SetWarnings True
 
Exit Sub
 
ErrorHandler:
MsgBox "There was an Error: " & Err & ": " & Error(Err)
End Sub

Open in new window

LVL 1
WSStudentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Don't import, but link the Excel data (use acLink).

Then use this linked tables as source in a simple select query where you filter (and modify) data as needed for clean-up.

Now use this query as source when you import the data.

/gustav
WSStudentAuthor Commented:
@Gustav, in Linking how will i will be able to pick file?

Also how to filter in query?  Like how will i separate data for example 5 records are for tblCap and 15 for tblGen so how i am going to append those to their original or main table?
Gustav BrockCIOCommented:
>  Linking how will i will be able to pick file?

Same as for import.

>  how to filter in query?

Well, like any other query. You have the data and know what you wish include or exclude and to import. So that's up to you.

/gustav
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

WSStudentAuthor Commented:
@Gustav, As i need to append via query so in my operational db there are 5 tables in that case i have to make 5 query , isn't there a way that this all can be done via VBA code?

Also how i will apply criteria?
Gustav BrockCIOCommented:
Yes, but - given from experience - you will need to create those five queries anyway for debugging and checking the collected data from your sample data.
So, when success, you will have the five select queries ready for use as source in your append queries.

/gustav
WSStudentAuthor Commented:
isn't there any other way? as my user don't want to have so much queries.
Gustav BrockCIOCommented:
You can prefix the queries with Usys :

UsysFilterQuery1
UsysFilterQuery2
UsysAppendQuery1
UsysAppendQuery2
etc.

Open in new window

Then they will hidden.

/gustav
Dale FyeOwner, Developing Solutions LLCCommented:
"as my user don't want to have so much queries"

Not sure what this means.  Running these append queryies would simply be part of the import process.  You first link to the Excel file, then execute these 5 append queries right behind each other.

You might also want to reconsider this line of code:

    DoCmd.TransferSpreadsheet acImport, 8, "tblCapacity", selectFile(), True

Personally, I'd create a variable (strFilename) and then do something like:
Dim strFilename as string
strFilename = SelectFile()
if strFilename = "" then 
    msgbox "Invalid file name"
    Exit sub
end if
DoCmd.TransferSpreadsheet acImport, 8, "tblCapacity", strFilename, True

Open in new window

This would prevent an error if the user fails to select a file (Cancels) in the SelectFile.
PatHartmanCommented:
I hate to be picky but do you even know what "8" means in the TransferSpreadsheet command?  ALWAYS use the version name so that next year or in 10 years, your successor won't have to try to figure out what version of Excel "8" refers to.  The TransferSpreadsheet gives you intellisense so there is no need to hard-code a number.  Pick from the available list and make sure that you choose an option that will be compatible  with all spreadsheets you need to import so newer rather than older will be better.  acSpreadsheetTypeExcel12XML is the newest format.  Don't make the common mistake of choosing acSpreadsheetTypeExcel12.  It only works for binary format workbooks

print acSpreadsheetTypeExcel12xml
 10 

Open in new window

WSStudentAuthor Commented:
@Dale,
Running these append queryies would simply be part of the import process.
if that's the case then it's fine but how i could do that, how 5 append queries would run behind each other?

@Pathartman, yeah you are right it shouldn't be 8.

Also my question from all of you will again be how i will apply criteria? Like the code i wrote it is exporting data but how to apply criteria that on the basis on field data type with specific text in it it should append data?
WSStudentAuthor Commented:
I hope this may explain more what i am trying to achieve. Attach is a sample DB with sample Excel file. You can have a look at form Import and have a look at field Datatype. In the field Datatype there are records for "Table1","Table2", and "Table3", how will i apply the criteria to append record to their respective tables?
Wsm93--1-.accdb
Book1---Copy.xlsx
Gustav BrockCIOCommented:
In the first query, filter on: DataType = "Table1", in the second: DataType = "Table2", etc.

Also, apply any conversion or additional filtering and/or renaming of field names to match those of your target table as needed.
Study the output and adjust. When you are happy, save the query.

Create five straight append queries for each table using the above queries.

Call them like this:

CurrentDb.Execute "AppendQuery1"
CurrentDb.Execute "AppendQuery2"
CurrentDb.Execute "AppendQuery3"
CurrentDb.Execute "AppendQuery4"
CurrentDb.Execute "AppendQuery5"

Open in new window

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WSStudentAuthor Commented:
@Gustav, how can i show a message box that after all this the data is added. I wrote below calling as MsgBox "Data Added Successfully!" but it's not working, any idea?
Gustav BrockCIOCommented:
Can't tell. That line of code will always work.

/gustav
WSStudentAuthor Commented:
Thank you everyone.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.