Solved

Import data to Access from Excel predefining key

Posted on 2016-09-23
8
79 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 33

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 48

Expert Comment

by:Dale Fye
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:shogun5
ID: 41813620
Rob,

Sorry, I'm a little confused with your statement. Can you provide some examples or screenshots?
0
 

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 48

Accepted Solution

by:
Dale Fye 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

622 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