How to import data from MS Excel to MS Access QUERIES or FORMS instead of tables

[yashuaking]

I have school database with a table which contain the list of all subjects, Scores and grades. Because each teacher assess only one subject, I have created a query to separate the various subjects for the teachers so that each teacher would see only his subject in the form based on the query.

To make thing easier I want them to be able to upload an excel file for each subject.
Instead of importing into the main table, I want the import to be done into the query for each teacher. I am using this code but I realize data is not uploading into the query or form but when I tried using a table, it works perfectly.

Please can someone help me with how to import data from Excel to Access Query instead of Tables?

I can deal with the that of tables but am not getting success with the query or forms.

This is the code am using

strTable = "Form3C1Term1"
 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
      strTable, strPathFile, blnHasFieldNames, "F1:F5"

[zmbd]

What you ask for is not that simple...

One would not normally do what you are describing with the data in a properly normalized table - and it really sounds as if your database isn't normalized at this point ( Please see: >> Database Normalization and Table Structures. )

The main issue with data import from excel is the data typing. All to often dates and numeric values are treated as text and that causes issues... if you will use the search box at the top of this page (orange with magnifying glass) and search on excel import to access you will get a ton of threads describing the various VBA and external data methods for importing. The docmd.transferspreadsheet is among those results.

Some terms:
Tables - where the data is stored
Queries - snapshots of the tables
Forms - used to manipulate the table data, can be used with queries
Reports - Even though they look like forms (and now can kind of act like them) are use to report the data either directly from the tables or more commonly from a query that pulls only the desired data
Normalization - a Method of segregating data so that data manipulation and reporting is usually more efficient.

I would be happy to send you a boilerplate with some tutorials and other reference links, just PM me

-z

(just a thought on your tables - and this is at 4am local, neighbor dog was barking went to go feed it and open its dog house door, the wind blows it shut (^_^) good dog, just noisy.)

tbl_teachernames
tbl_subjectnames
tbl_studentnames
tbl_schoolterms
tbl_termprogram
tbl_programeenrollment
tbl_ConvertfromLetter4Pt5ptTo100score
tbl_grades

(you might need other tables depending on your application... for this model I am only looking a final course grade for the indicated term)

SchoolTerms could be the year, or by quarter,etc...
TermProgram would link teachers to subject and the term
ProgrmEnrollment links the students to the termprogram
tbl_grades links the student enrolled in a termprogram and the grade received via the ProgramEnrollment table.

The ConvertfromLetter4Pt5ptTo100score table allow you to store the grade in the standard 100point scale while allowing teachers to assign grades by letter on either 4pt or 5pt scale. You can then re-assign cut off values without needed to recode the database

The form should be straight forward to design and the teacher should enter directly. Split the database and multiple teachers should be able to enter at the same time.

To import from speadsheet,
Use the file dialog (Select a File or Folder using the FileDialog Object) to allow the teacher to find excel file.
(personally I would use code to generate this file from the database in the proper format or you will be programing from now till the cows come home to handle each teacher's whim). Then use automation to read the data... ( Application Automation )