VB Coding Help Needed (1 Viewer)

[deafmetal]

I am building an Access database for easier student administration. I have the front end and interfaces built.

What I'm trying to do is have 2 default tables for student info and grades. I want a user to be able to "create" new tables within the database for the current class from default tables.

We name everything by class# (02040 for ex.). So basically I need to build code that would use a prompt to enter in the 02040 to name them when pasted. But the code would copy the default tables and paste them as whatever was entered. And since the two tables cannot be named the same class#, I'd want them named 02040-info and 02040-grades.

But the -info and -grades portion of the table name would have to transparent to the user, and be done in bulk with the use of one prompt.

So I, as the user, enter 02040 at the prompt. The code would copy and paste tables default-info and default-grades as 02040-info and 02040-grades.

Then these tables could be filled in, edited, looked up, and printed as a report. That I can do with list boxes of the existing tables within the database, etc...

I just can't figure out the code for the copy/paste aspect.

 

[ritchieroo]

I'm assuming that you have a good business reason for needing to create physically separate tables, rather than just adding a the class# column to two tables (modelling logical tables). Your approach may cause you a lot a grief, and become difficult to maintain at some point in the future.

That said, the easiest approach would be to use DDL in a query:


Public Function Create_TableX(ByVal ClassNumber As Variant)
Dim sSql As String
sSql = "CREATE TABLE [" & ClassNumber & "-info] (MyKeyId Counter CONSTRAINT PK_0 primary key, MyValue text(30))
CurrentDB.Execute sSql
sSql = "CREATE TABLE [" & ClassNumber & "-grades] (MyKeyId Counter CONSTRAINT PK_0 primary key, MyValue text(30))
CurrentDB.Execute sSql

 

[deafmetal]

The goal is to maintain student info and grades for multiple classes and to easily be able to locate, maintain, and view them. Thought that rather than having 15 classes with 20 students each all in one table, that having separate tables would be easier to digest. I may very well be wrong.

 

[Fizzio]

I would certaily mirror what richieroo says. Look a little closer at your structure. You should not need to create tables for each class.

tblStudentInfo
------------------
StudentID
StudentName
DOB
StudentAddress
etc...

tblClasses (LookupTable)
-------------
ClassID
ClassCode
ClassName

tblStudentGrades
----------------------
StudentID (FK to tblStudentInfo)
ClassID (FK to tblClasses)
DateJoinedClass
DateLeftClass
Grade

This will be easier to manage in the long run. Set up relationships for StudentId between tblStudentInfo and tblStudentGrades and for ClassID between tblClasses and tblStudentGrades.
With this structure (with inclusion of the date fields) you can create a class list for any point in time and view the greades by class/ student/ date etc etc.

HTH