Automatically creating table using VB?

Tawcan

Registered User.
Local time
Today, 00:44
Joined
Mar 19, 2003
Messages
58
I'm trying to create a table automatically (like when a button is clicked on the form), but am stuck. I'm not 100% sure if I need to use SQL or not to accomplish this. Really new to this stuff so please help me.

The follow is the code I have right now. When I tried to run this I get an error saying that the field INDIVIDUAL APR can't be found...:confused:

INDIVIDUAL APR is a form that the button is located. There are bunch data displayed on this particular form.

-----------------------------
'Session Average

Private Sub Session_Average()
On Error GoTo ErrorHandler
Dim tb20 As DAO.TableDef, table As DAO.Recordset, ENTRY_YEAR As String, YEAR As String
Dim session_year As String, yr As Integer, ssAvg As String, CURRENT_YEAR As Integer
tableName = Forms![INDIVIDUAL APR]!Box2
Set db = CurrentDb()
'Checking if table exists, if yes delete table
If ObjectExists(acTable, "SESSION AVERAGE") Then DoCmd.DeleteObject acTable, "SESSION AVERAGE"

'Create new table
Set tb20 = db.CreateTableDef("SESSION AVERAGE")
With tb20
.Fields.Append .CreateField("SESSION", dbText)
.Fields.Append .CreateField("SESSION AVERAGE", dbText)
End With

'Begin algorithm for calculating session average
ENTRY_YEAR = Form![INDIVIDUAL APR]!Box27
YEAR = Right$(ENTRY_YEAR, 2)
If YEAR = "00" Then
yr = 99
Else
yr = CInt(YEAR) - 1
End If

Do Until yr > yr + 5

'Algorithm for winter sessions
ssAvg = ""
session_year = ""
session_year = CStr(YEAR) + "W"
ssAvg = Round(DSum(" [GRADES] * [ACT CREDITS] ", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )") / DSum("[ACT CREDITS]", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)"), 2)
table.AddNew
table![SESSION] = session_year
table![SESSION AVERAGE] = ssAvg
table.Update

'Algorithm t summer sessions
ssAvg = ""
session_year = ""
session_year = CStr(YEAR) + "S"
ssAvg = Round(DSum(" [GRADES] * [ACT CREDITS] ", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )") / DSum("[ACT CREDITS]", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)"), 2)
table.AddNew
table![SESSION] = session_year
table![SESSION AVERAGE] = ssAvg
table.Update

yr = yr + 1
Loop
Exit Sub

ErrorHandler:
MsgBox Error(Err)
Exit Sub

End Sub
-----------------------------------------

Here's a similar code that actually works:

Private Sub AU_query()
On Error GoTo ErrorHandler
Dim qdf1 As QueryDef, tbl3 As DAO.TableDef, table As DAO.Recordset
Dim strSQL2 As String, AUREPORT As String
tableName = Forms![INDIVIDUAL APR]!Box2
Set db = CurrentDb()
If ObjectExists(acTable, "AU REPORT") Then DoCmd.DeleteObject acTable, "AU REPORT"

Set tbl = db.OpenRecordset("SELECT * FROM [" & tableName & "];")

'Create new table.
Set tbl3 = db.CreateTableDef("AU REPORT")
With tbl3
.Fields.Append .CreateField("REQ COURSE", dbText)
.Fields.Append .CreateField("ACT COURSE", dbText)
.Fields.Append .CreateField("OPTION", dbText)
.Fields.Append .CreateField("CREDITS", dbText)
.Fields.Append .CreateField("SESSION", dbText)
.Fields.Append .CreateField("YEAR", dbText)
.Fields.Append .CreateField("MATH AU", dbText)
.Fields.Append .CreateField("BAS SCI AU", dbText)
.Fields.Append .CreateField("COMP STUD AU", dbText)
.Fields.Append .CreateField("ENG DES AU", dbText)
.Fields.Append .CreateField("ENG SCI AU", dbText)

End With

db.TableDefs.Append tbl3
Set table = db.OpenRecordset("AU REPORT", dbOpenTable)
If (tbl.RecordCount = 0) Then

Exit Sub
Else

tbl.MoveFirst
Do Until tbl.EOF
table.AddNew
table![REQ COURSE] = tbl![REQ COURSE]
table![ACT COURSE] = tbl![ACT COURSE]
table![OPTION] = tbl![OPTION]
table![CREDITS] = tbl![ACT CREDITS]
table![SESSION] = tbl![SESSION]
table![YEAR] = tbl![YEAR]
calYear = session_edit(tbl![SESSION])
If (Not (calYear = "20" Or calYear = "19")) Then

Set Autable = db.OpenRecordset("SELECT * FROM [AU " & calYear & "];")
checkAU = "[COURSE] = '" & tbl![ACT COURSE] & "'"
Autable.FindFirst checkAU
If (Not (Autable.NoMatch)) Then

table![MATH AU] = Autable![MATH AU]
table![BAS SCI AU] = Autable![BAS SCI AU]
table![COMP STUD AU] = Autable![COMP STUD AU]
table![ENG DES AU] = Autable![ENG DES AU]
table![ENG SCI AU] = Autable![ENG SCI AU]

End If
End If
table.Update
tbl.MoveNext
Loop
End If

Exit Sub
ErrorHandler:
MsgBox Error(Err)
If ObjectExists(acTable, "AU REPORT") Then DoCmd.DeleteObject acTable, "AU REPORT"
Exit Sub

End Sub

---------------------

Not sure why there are bunch variables declared in the 2nd one that aren't even used in the sub.

Thanx everyone!
 
Some things you might consider.

(1) From your example, tableName has not been dimensioned. e.g. dim tableName as String

(2) Refer to your form and control as Forms![INDIVIDUAL APR]![Box2] vs. Forms![INDIVIDUAL APR]!Box2

(3) If neither of the above have the desired effect, consider building table "tblSessionAverage" manually (note that the naming convention prefixes with 'tbl' and there are no embedded spaces (which add an additional, unnecessary level of complexity)) and when running your code, stripping the records from the existing table rather than deleting and recreating the table.

Just some thoughts.

Please post back.

Bob
 
#1. tablename is declared in the module as string

#2. Tried but didn't work. :o

#3. What do you mean? Don't understand it completely.

Does my code make any sense at all? :confused:
 
I tried to use this code to create the table... seems to work. Right now trying to figure out how to actually insert data onto the table..

----------------------
Dim dbs As Database
Dim tbs As TableDef
Dim fld1 As Field
Dim fld2 As Field
'Checking if table exists, if yes delete table
If ObjectExists(acTable, "SESSION AVERAGE") Then DoCmd.DeleteObject acTable, "SESSION AVERAGE"

Set dbs = CurrentDb
Set tbs = CurrentDb.CreateTableDef("SESSION AVERAGE")
Set fld1 = tbs.CreateField("SESSION", dbText)
Set fld2 = tbs.CreateField("SESSION AVERAGE", dbText)

tbs.Fields.Append fld1
tbs.Fields.Append fld2
tbs.Fields.Refresh
dbs.TableDefs.Append tbs
dbs.TableDefs.Refresh

-------------------


Would this code work?

'Algorithm for winter sessions
ssAvg = ""
session_year = ""
session_year = CStr(YEAR) + "W"
ssAvg = Round(DSum(" [GRADES] * [ACT CREDITS] ", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )") / DSum("[ACT CREDITS]", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)"), 2)
table.AddNew
table![SESSION] = session_year
table![SESSION AVERAGE] = ssAvg
table.Update

:confused:
 
Tawcan, I know you think that you are firmly committed to this approach but believe me, it will take you less time to change your forms. You really can't make individual tables for EVERy student no matter what your boss says. It can't be faster to write hundreds of lines of code when none would be required if you simply normalized your tables.
 
I know, but that means reconstructing the whole database and I don't have a lot of time. I'll write a recommendation for the next person that works on the database.

Thinking creating only 1 SESSION_AVERAGE that gets updated each time...

Back to the topic... now I have created a new table...how do I insert the data I want onto the table field? I don't think my algorithm works. :o
 
Tawcan:

Here is an example of SQL to insert records into a table:

INSERT INTO tblEmployee ( UserID, EmpSS, EmpFNm, EmpLNm, EmpAdd )
SELECT tblEmployee.UserID, tblEmployee.EmpSS, tblEmployee.EmpFNm, tblEmployee.EmpLNm, tblEmployee.EmpAdd
FROM tblEmployee;

But Pat is correct. I believe I had looked at a copy of your database and given you the same recommendation. The individual that previously created the db, felt it was necessary to create a table for each student every time a Report was run. Additionally, they created seperate tables for each year and course. As time goes on, the additions that you are making are going to possibly complicate things more. You may want to consider your idea of redesigning the database to ensure Normalization. It might take a little extra time now, but it will be much more beneficial in the future.

Just a suggestion.

HTH
 
Sorry, don't really know how to use SQL though I know it's super simple... :o :o :o

Now I can create table successfully using:

Dim dbs As Database
Dim tbs As TableDef
Dim fld1 As Field
Dim fld2 As Field
'Checking if table exists, if yes delete table
If ObjectExists(acTable, "SESSION AVERAGE") Then DoCmd.DeleteObject acTable, "SESSION AVERAGE"

Set dbs = CurrentDb
Set tbs = CurrentDb.CreateTableDef("SESSION AVERAGE")
Set fld1 = tbs.CreateField("SESSION", dbText)
Set fld2 = tbs.CreateField("SESSION AVERAGE", dbText)

tbs.Fields.Append fld1
tbs.Fields.Append fld2
tbs.Fields.Refresh
dbs.TableDefs.Append tbs
dbs.TableDefs.Refresh

How do I actually insert data onto the table field using VB?

I just took a look at the crystal reports they receive and yep all the student info is on one table. Don't know why they create new tables for each student... :scratch: I'm for sure going to write up a long section of recommendation on why normalization is badly needed for future modification. :)
 
What is the name of the table and the fields that you want to append into the new table you created?
 
Basically I want to create a new table called SESSION AVERAGE. Then do some calculation, and stick the session year into SESSION, and session average into SESSION AVERAGE. Then use this table for SESSION AVERAGE REPORT. (Yes I know, if I normalized the table I could do it easily.... :p)

I tried using this following code after the code I posted on last post...

ssAvg = ""
session_year = ""
session_year = CStr(YEAR) + "W"
ssAvg = Round(DSum(" [GRADES] * [ACT CREDITS] ", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )") / DSum("[ACT CREDITS]", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)"), 2)
table.AddNew
table![SESSION] = session_year
table![SESSION AVERAGE] = ssAvg
table.Update

But that doesn't seem to work. :o

(I'm really clueless when it comes to VB :p ).
 
You want to use the the follwoing:

dbs.Execute "INSERT into Tablename ( Fields separated by commas) " & _
"SELECT TableName2.Fieldname1, TableName2.FieldName2, etc)" & _
"From Tablename2;"

A good way to ensure that you have the correct SQL is to create an Append Query and copy the SQL into the module.

The other thing would be to create an append query and use the docmd.OpenQuery method to run the query and append the data.

HTH
 
I can't believe that you are STILL using " = Null " in your queries. Maybe if someone else tells you that WILL NOT WORK in newer versions of Access, you'll believe them since you obviously don't believe me. :mad:
 
Pat Hartman said:
I can't believe that you are STILL using " = Null " in your queries. Maybe if someone else tells you that WILL NOT WORK in newer versions of Access, you'll believe them since you obviously don't believe me. :mad:


???? That was a copy and paste from earlier before you told me about that.... I've changed it on the current database.... :)
 

Users who are viewing this thread

Back
Top Bottom