Populate an Access 2000 Table with data from DAO Recordsets

[BeckR]

Hello - Thanks for reading my post.

I am a newbie when it comes to VBA programming, but have managed to do what I need to do, until now.

I have an Access 2000 database (running WinXP Pro (SP3)).

I have successfully created 3 recordsets in my database. What I need to do is to take the data from those 3 recordsets and put them into a table so that I can run reports at a later time. I was able to create the recordsets and the table with all the fields that I need, but I do not know how to get the data from the recordsets into the table. Any assistance would be greatly appreciated.
Thank you

BeckR


Below is the code I am using.

    Dim dbs As DAO.Database
    Dim regsrst As DAO.Recordset
    Dim leaguerst  As DAO.Recordset
    Dim teamrst As DAO.Recordset
    Dim tfdnew As DAO.TableDef
    Dim fldnew As DAO.Field
    Dim ind  As DAO.Index
    Dim strSQLL, strSQLT, strSQLR  As String
    Dim intI As Integer
 
    strLeagueIdNo = Me!cmbChoose_League.Column(0)
    strLeagueName = Me!cmbChoose_League.Column(1)
 
    Set dbs = CurrentDb()
 
    Debug.Print strLeagueIdNo   
    Debug.Print strLeagueName   
 
    strSQLL = "SELECT * FROM Leagues " & _
      "Where League_Id_No = " & strLeagueIdNo & ""
      Debug.Print strSQLL
    Set leaguerst = dbs.OpenRecordset(strSQLL)
 
    strSQLR = "SELECT * FROM Registrations " & _
      "Where League_Id_No = " & strLeagueIdNo & ""
      Debug.Print strSQLR
      Set regsrst = dbs.OpenRecordset(strSQLR)
      regsrst.MoveLast
      NoTeams = regsrst.RecordCount
      regsrst.MoveFirst    
    strSQLT = "SELECT * FROM Teams " & _
      "Where Team_Id_No = " & TeamId & ""
      Debug.Print strSQLT
    Set teamrst = dbs.OpenRecordset(strSQLT)
 
    TeamName = teamrst("Team_Name").Value
    HeadCoach = teamrst("Head_Coach").Value
    Address = teamrst("Address").Value
    City = teamrst("City").Value
    State = teamrst("State").Value
    Zip = teamrst("Zip").Value
    HomePhone = teamrst("Home_Phone").Value
    WorkPhone = teamrst("Work_Phone").Value
    CellPhone = teamrst("Cell_Phone").Value
 
    DoCmd.DeleteObject acTable, "League_Schedules"
 
    Set tfdnew = dbs.CreateTableDef("League_Schedules")
 
    Set ind = tfdnew.CreateIndex("PrimaryKey")
 
    With ind
        .Fields.Append .CreateField("Receipt_No")
        .Unique = False
        .Primary = True
    End With
 
     ' Append the new TableDef object to the Athletics Database
    tfdnew.Indexes.Append ind
 
   With tfdnew
      ' The CreateField method will set a default Size
      ' for a new Field object if one is not specified.
      .Fields.Append .CreateField("Receipt_No", dbInteger)
      .Fields.Append .CreateField("ASA_No", dbText)
      .Fields.Append .CreateField("League_Id_No", dbInteger)
      .Fields.Append .CreateField("League_Name", dbText)
      .Fields.Append .CreateField("No_Days", dbInteger)
      .Fields.Append .CreateField("No_Games", dbInteger)
      .Fields.Append .CreateField("Game_Type", dbInteger)
      .Fields.Append .CreateField("Season_Start_Date", dbInteger)
      .Fields.Append .CreateField("Age_Type", dbInteger)
      .Fields.Append .CreateField("Day1", dbInteger)
      .Fields.Append .CreateField("Day2", dbInteger)
      .Fields.Append .CreateField("Day3", dbInteger)
      .Fields.Append .CreateField("Time1", dbInteger)
      .Fields.Append .CreateField("Time2", dbInteger)
      .Fields.Append .CreateField("Time3", dbInteger)
      .Fields.Append .CreateField("Time4", dbInteger)
      .Fields.Append .CreateField("Time5", dbInteger)
      .Fields.Append .CreateField("Location_1", dbInteger)
      .Fields.Append .CreateField("Location_2", dbInteger)
      .Fields.Append .CreateField("Location_3", dbInteger)
      .Fields.Append .CreateField("Location_4", dbInteger)
      .Fields.Append .CreateField("Location_5", dbInteger)
      .Fields.Append .CreateField("Team_Name", dbInteger)
      .Fields.Append .CreateField("Head_Coach", dbInteger)
      .Fields.Append .CreateField("Address", dbInteger)
      .Fields.Append .CreateField("City", dbInteger)
      .Fields.Append .CreateField("State", dbInteger)
      .Fields.Append .CreateField("Zip", dbInteger)
      .Fields.Append .CreateField("Home_Phone", dbInteger)
      .Fields.Append .CreateField("Work_Phone", dbInteger)
      .Fields.Append .CreateField("Cell_Phone", dbInteger)
   End With

[NeoPa]

BeckR, what do you mean when you say you have data stored in 3 recordsets?

A recordset is a dynamic code entity that refers to something that returns data. It doesn't fit in this context. Normally I'd assume you're simply referring to tables, but you refer to a table separately so I doubt that's the explanation in this case.

[BeckR]

Neopa

My apologies for the confusion.

I have created 3 DAO Recordsets which return data from 3 tables in the database. I then create a new table which needs to contain the information from the 3 DAO Recordsets. I have the recordsets and the table created, but I am not sure how to place the data from the recordsets into the new table. Once the new table is created, it then generates League Schedules using the new table data.

Thanks Neopa


BeckR

[NeoPa]

Still a bit of confusion here I'm afraid. Are all the four tables of the same format? I assume they must be as you don't mention it.

If so, do you want to append ALL the records from ALL the three original tables into the fourth?

[BeckR]

That's because I don't speak "VBA" very well. :-)

I have 5 tables in the database. There a 3 of them that I need to pull certain information from. The 3 main tables are "Leagues", "Registrations", and "Teams". For example I need to pull the Coach's information: i.e. Name, address, city, state, zip from the Teams table. I need to pull the League information from Leagues table and with the Registrations, I need to pull all Registration records where the League Id No = a combo box value on a form. I then need to count how many Registration records have been pulled from the Registration table.

Once I have the data pulled from all 3 tables (into DAO Recordsets?)
I then want to be able to put that information into a New table called League_Schedules. I will then generate reports from the newly created League_Schedules table.

What I am having problems with, is how do I get the information from the recordsets into the new table League_Schedules? Do I need to use the INSERT INTO with VALUES?

I hope this makes more sense.. :-)

[Stewart Ross]

Hi BeckR. I hope you don't mind me asking for clarification here; why are you using VBA to do any of this?? It only makes sense to me if you have a front end which is entirely coded in Visual Basic or somesuch. If you have Access 2000 as a front end, why are you not creating the table the way most tables are created in Access - in table design view?

For your updates and inserts, the norm is again not to be doing these in code as such (unless you have a specific requirement that demands it) - Access queries (which are just variants of SQL under the design front-end) will do all the hard work for you.

I can't help asking if there is a reason for all the code? It is an extraordinary effort to write a one-off process to create a table, when it takes just a few minutes to do the same task in the table design view of Access itself.

-Stewart

[NeoPa]

...Continuing on from Stewarts thought provoking questions, do you really want to create a new table or is a simple query more likely to be an appropriate method?

Let us know your thoughts on these.

[BeckR]

Hi

The reason I am using VBA is because of where and how I generate the league schedules. I have a combo box (that lists all Leagues) on a form for the user. They choose which league they wish to generate a schedule for, they then click the command button to generate the desired schedule. It is in this code (On-Click) of command button that I am trying to determine which schedule to run. In order to generate the correct schedule, I need to know the No_Days, No_Games, and No_Teams that are currently selected. The No_Teams is a count of the records found in the Registrations Table that match the League chosen in the combo box. This information will change every time a different League is chosen. For example, the selected league plays 2 days per week, 21 games per season, and there are 6 teams in this particular league. (There could be anywhere from 4 teams – 10 teams in a League). Based on the information obtained, I will run a report named: 06 Teams - 11 Games - 2 Days. I want to use the newly created League_Schedules table to run each of the different schedule reports. Am I going about this incorrectly? If there is a better way to get the schedules generated, I am certainly open to any suggestions.

Thank you for your time…

[Stewart Ross]

Hi BeckR, and thank you for your explanation. I am sure now that you are not abstracting your table designs sufficiently, as you are in effect building bespoke tables to reflect particular league circumstances instead of designing a set of tables adaptable enough to cover any number of matches and any number of days etc.

If you look at the field list for the new table you are creating in post 1 you will see several repeated groups - times, locations, and days jump out. That your table includes such repeated groups means it is not in what is known as First Normal Form, and it reflects a lack of abstraction in table design.

Each of the repeated groups should be taken out into a separate table. We have a good introductory article on Database Normalisation and Table Structures in our HowTo section which you may find helpful.

A league recording database should be able to cope with any number of teams playing any number of matches over a season of any defined length. It is up to the designer of the system to make sure it does, not by generating bespoke tables on the fly but in designing a structure of related tables able to cope with all the situations you would expect to find in a typical league. The design of the tables is generally a one-off task (barring modifications and enhancements). By getting the design right from the start you can concentrate on the user interface and facilities you provide for your users, instead of what must have taken a fair amount of effort to code the generation of a bespoke non-relational table in VBA.

By the way, once your tables are in place you can use Access queries to provide views of your data joined in whatever way is appropriate. It is the norm to use queries as the basis for reports, as you can prepare queries which 'stick back together' the groups separated out in a normalised table structure.

-Stewart

[NeoPa]

I strongly recommend this advice.

However heavy the workload of doing this change of design may appear to you now, you will certainly be saving yourself work (AND HEADACHES) by so doing.

[BeckR]

Yes, I see what you mean. Thank you for the link to the Database Normalisation paper. I understand the design needs to change. The good news is that this is an additional "module" that I am adding to the basic registration system, so the design change will not need to be a complete re-design. All the other tables I use have a 1-to-many relationship. The League table is the one that needs to change. This is the most complex database I have worked on in quite some time, so I am a little rusty. Many thanks for your suggestions and your time!!

BeckR

[NeoPa]

Always happy to help :)

...and the more people that read and understand the Normalisation article the better. The questions are always of a different nature once someone understands that.

Welcome to Bytes!