Solved

Import data to Access from Excel predefining key

Posted on 2016-09-23
8
68 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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 will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

735 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