Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

MS Access - Import data from Excel to Access using Criteria

Avatar of WS
WSFlag for United Arab Emirates asked on
Microsoft AccessVBA
15 Comments1 Solution314 ViewsLast Modified:
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