MS Access - Import data from Excel to Access using Criteria


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)
            'stop execution if nothing selected
        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
MsgBox "There was an Error: " & Err & ": " & Error(Err)
End Sub

Open in new window

Who is Participating?
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.

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.

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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.

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 :


Open in new window

Then they will hidden.

Dale FyeOwner, Dev-Soln 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.
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

Open in new window

WSStudentAuthor Commented:
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?
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


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.

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.