Importing Spreadsheet in multiple tables

[celinesuzzarini]

Hi all,

I would like to import a spreadsheet into my access database, but in
multiple tables.
Here are my tables:
Students_tbl:
- studentID: autonumber
- firstname, lastname
Classes_tbl:
- classID: autonumber
- classname, credits
Students_and_Classes_tbl:
- studentID: foreign key to students_tbl
- classID: foreign key to classes_tbl
- quarter
- grade

Here is my spreadsheet:
- lastname
- firstname
- classname
- credits
- quarter

The main thing is to fill in Students_and_Classes_tbl with the right
IDs.
However, if the student doesn't exist, I need to create it in
Students_tbl, and then add the proper record in
Students_and_classes_tbl.
Same thing if a class doesn't exist.

One of my suggestions would be to import the spreadsheet into a table,
and then move the records to the corresponding table... however, I have
no idea how to match the names to import with the correct auto IDs.

Any suggestions?

Thank you so much,
Celine

 

[celinesuzzarini]

I also wanted to add:
If in Students_tbl I find more than one student with the same name,
then I want to ask the user which ones he wants to choose (showing the
address for example).

Thanks,
Celine

 

[dbahooker]

yeah.. my reccomendation is this:

a) dont hire anyone that uses excel for anything
b) get more database people
c) automate the crap out of your systems
d) rejoice that you don't need to carry around the weight of a bunch of
spreadsheet dorks.

-Aaron
ADP Nationalist

 

[celinesuzzarini]

Well, that didn't help me much... I did create the database, and
before, I use to do it all by hand... now, they just changed their
system, so I have the opportunity to automate the input of class
roster... better than before!

Any help anybody?

Thanks anyway!
Celine

 

[Ken Snell \(MVP\)]

Matching by name is not a recommended process. I would assume that each
student has a unique ID number in the spreadsheets, and that is what should
be used for matching -- and should be used as the primary key in your
students table.

Your idea of importing the data to a single table initially, and then to
copy the data to the correct tables, is most likely the best approach. Using
the unique ID for each student will allow you to match the data between the
tables.

 

[celinesuzzarini]

Thank you Ken for your answer.
Here is the thing with my database: we are currently a branch of the
main school and we have created our own database so that we can keep
track of our students and prospective students too. When a student is
entered in our database, he doesn't automatically have a Student ID
number given by the school. However, my database automatically gives
him an autonumber.
So in my spreadsheet, I might have students who are already in our
database, but whose student ID has not been updated yet... therefore,
searching on StudentID would not work in our case (this is also another
thing I need to do when importing my table: updating the Student ID!)

Currently, I am going with:
1- importing the datasheet in a new table in access
2- matching firstnames and lastnames with the autonumber (and also
creating a message if there is more than one student with the same
name)
3- matching classname with the autonumber
4- appending the record to my table.

I am trying to use recordsets, but still trying to figure out how they
work...
I am having trouble opening the database which is split and on a public
drive... any suggestions for that?
Here is my code:

Dim dbStudent As Database
Dim rsImport As Recordset

' P: is a shared drive on our workgroup network
Set dbStudent = OpenDatabase("P:\Database\students.mdb")
' "Import" is the name of the table I imported the spreadsheet in
Set rsImport = dbStudent.OpenRecordset("Import", dbOpenDynaset)

And I get the error message:
The database has been placed in a state by user 'Admin' on machine
"TheNameOfTheMachineWhereTheSharedDriveIs" that prevents it from being
opened or locked.

Any other suggestions?

Thanks,
Celine

 

[Ken Snell \(MVP\)]

If you will need to manually match records as you append them, that sounds
like you'll need to do a lot of customized programming to let the user view,
match, edit, and then append the records. Using names is fraught with errors
of mismatching, misidentification, etc. This will not be something
easily/quickly done just by opening a recordset -- it'll involve form to
display the entry, to show all possible matching names, to match the name to
an existing one or to say it's a new one, etc.

Is the autonumber field of which you speak in the temporary table where you
import the original data, or is it in the permanent table? or both, most
likely? If both, you won't be able to keep the same autonumber values
between the two tables.

I don't have a good mental picture of what you want to do, so my comments
above are a bit generic, I'm afraid.

As for the code error, it sounds as if the database is already open when you
try to connect to it, and the database is not set for shared use; or you
don't have read/write/create/delete privileges on the P drive so that the
database can create a locking (.ldb) file; or the database has user security
implemented on it (and your connection code doesn't provide the proper
password, etc.).
--

Ken Snell
<MS ACCESS MVP>

 

[celinesuzzarini]

Ken,

Thank you so much for all your useful input.
I think I have figured out how to do it:
1. Import the external datasheet into Import_tbl
2. Create a recordset with the Import_tbl
3. As I import each row to my Students&Classes table, I count:
a. the number of students existing with that firstname and lastname.
If there is one, then I continue. If there is 0 or more than one, I
export that record into a Not_Imported_tbl with the reason why it was
not imported
b. I do the same thing with the class
4. Finally, the user will be able to review the Not_Import_tbl and make
changes so that it will be imported properly into the database (either
manually, or by creating a new student...)

This will be my temporary solution as I know that for now, our database
doesn't have any name duplicates... but as more students come into our
program, I will change it so that it will be all automatic.

Thanks for everything,
Celine

 

[Ken Snell \(MVP\)]

Sounds like a workable solution. Good luck!