Solved

Import data to Access from Excel predefining key

Posted on 2016-09-23
8
41 Views
Last Modified: 2016-10-02
Dear Experts,

Need some assistance in importing data from an Excel spreadsheet into a table in Access.  I need to be able import students into a class. When I click in 'create a new class' a dialogue box opens up when a new class record started as seen by the last screenshot below. I need to import students to 'that' particular class. I am familiar with the VBA code docmd.TransferSpreadsheet command and can 'import' students but need to know how to do this for a 'specific' class.
screenshotscreenshotsc4
Thanks!
0
Comment
Question by:shogun5
  • 5
  • 2
8 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41812240
In the Excel sheet, you can use Advanced Filter to create a separate sheet with filtered entries based on the StudentClassID.

Then use the TransferSpreadsheet on the separate sheet.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41812274
If you are using Access for the database, why are you using Excel for the list of students?

Generally, when I import from Excel, I link to the Excel spreadsheet, I don't import it.  Then I create a staging table that import the data from the linked spreadsheet into.  By using a staging table, I'm able to add additional fields that I use for error checking, and generally a Yes/No field [IncludeThis].  Then I can display data from this staging table and allow a user to select records from that table by simply clicking in the checkbox associated with the [IncludeThis] field.
0
 

Author Comment

by:shogun5
ID: 41813619
Dale,
 This Access database charts students progress throughout the year. It has a specific function. It's not like the overall district student database that we use. So what we want to do is to export students from that district database and import those students via Excel spreadsheet into that  access database.  Linking will not work because as soon as that spreadsheet gets moved from the current file location or if a teacher brings the access database home then the link is severed.  So I need a way to import all the students into the table. I can do this easily by creating a new table but I need to  import students  and assign each student a foreign key that matches the primary key of the new class that I'm creating and putting the students in.  I just don't know how I would add that foreign keyvalue into the import from excel to the student table. I hope this makes sense.
0
 

Author Comment

by:shogun5
ID: 41813620
Rob,

Sorry, I'm a little confused with your statement. Can you provide some examples or screenshots?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:shogun5
ID: 41813622
As you can see from the example above the class ID is 21 so when I import students into the student table I have to assign a foreign key of 21 that will match that pussy for the class.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 41813635
shogun,

I did not mean that you shouldn't import the students into Access, I just meant that you should initially link to the Excel spreadsheet, then import them into a Students table in Access.  If the Excel file does not already have a unique StudentID field, then you can create an Autonumber field in tbl_Students.

Then, when you select which students to add to the class, you append the classID and the StudentID to your StudentClasses table.

The form you are showing above looks like it would be the StudentClasses form, where the students are already assigned to the class.  I would also have a form for assigning students to the class.  To do this, I generally have a combo box for the Class, two listboxes (StudentsInClass, StudentsNotInClass), and two command buttons (Assign, Remove).  The query for listbox StudentsInClass would be something like:
SELECT Students.StudentID, Students.LastName, Students.Firstname
FROM Students
INNER JOIN StudentClasses On Students.StudentID = StudentClasses.StudentID
WHERE StudentClasses.ClassID = [Forms]![frm_AssignStudents].cbo_Class

Open in new window


The query for the listbox StudentsNotInClass would look like:
SELECT Students.StudentID, Students.LastName, Students.Firstname
FROM Students
LEFT JOIN (
SELECT StudentClasses.*
FROM StudentClasses
WHERE StudentClasses.ClassID = [Forms]![frm_AssignStudents].cbo_Class
) InClass
 On Students.StudentID = InClass.StudentID
WHERE InClass.ClassID IS NULL

Open in new window

When you change the Class in the combo box, you would requery each of these listboxes.

Then you have the Assign and Remove buttons.  I initially disable both of these and use the Click event of each of the listboxes to Enable one button and Disable the other, so that when list StudentsInClass has the focus, the Remove button is disabled, ...

The code behind the Assign command button looks something like:

Private Sub cmd_Assign_Click

    Dim strSQL as string

    strSQL = "INSERT INTO StudentClasses (StudentID, ClassID) " _
           & "Values (" & me.lst_StudentsNotInList & ", " & me.cbo_ClassID & ")"
    currentdb.execute strsql

    me.lst_StudentsInList.Requery
    me.lst_StudentsNotInListRequery

End Sub

Open in new window


The code behind the Remove button would look like:

Private Sub cmd_Remove_Click

    Dim strSQL as string

    strSQL = "DELETE FROM StudentClasses " _
           & "WHERE ([StudentID] = " & me.lst_StudentsInList & ") " _
           & "AND ([ClassID] = " & me.cbo_ClassID & ")"
    currentdb.execute strsql

    me.lst_StudentsInList.Requery
    me.lst_StudentsNotInListRequery

End Sub

Open in new window


Hope this helps
0
 

Author Comment

by:shogun5
ID: 41819079
Dale,

Thank you for taking the time to provide the code above! Actually I'd like to make this real simple for the end user. Rather than create a form to assign students that I have imported I'd like to simply add students to temp table then run an append query through VBA 'reading the ClassID value from the open Class form and use that ClassID value in the append query. I think I can assign the ClassID value to the studentClassID value of the student record when append the records to the tblStudent table from the temp table then delete all values from the temp table when done.
This way the user simply navigates to a .xlsx file on their computer, and continue. The VBA code will them import all Excel student records into the Temp Table. I'll use the Append Query to move all temp records to the Student Table while tacking on the ClassID to StudentClassID in the Append Query. I am working on that now to see if I can get it to work.
Any suggestions on this process would be appreciated!  
Thanks!
0
 

Author Closing Comment

by:shogun5
ID: 41825720
Dale,

Thanks again for taking the time to post that. While I did not use the code I might be able to use in the future so points awarded.

I ended up creating a append action query and called  the query after I grabbed the file path of the excel file. worked great.

Thanks again though.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now