470,815 Members | 1,281 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,815 developers. It's quick & easy.

Populate an Access 2000 Table with data from DAO Recordsets

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


Below is the code I am using.
Expand|Select|Wrap|Line Numbers
  1.     Dim dbs As DAO.Database
  2.     Dim regsrst As DAO.Recordset
  3.     Dim leaguerst  As DAO.Recordset
  4.     Dim teamrst As DAO.Recordset
  5.     Dim tfdnew As DAO.TableDef
  6.     Dim fldnew As DAO.Field
  7.     Dim ind  As DAO.Index
  8.     Dim strSQLL, strSQLT, strSQLR  As String
  9.     Dim intI As Integer
  11.     strLeagueIdNo = Me!cmbChoose_League.Column(0)
  12.     strLeagueName = Me!cmbChoose_League.Column(1)
  14.     Set dbs = CurrentDb()
  16.     Debug.Print strLeagueIdNo   
  17.     Debug.Print strLeagueName   
  19.     strSQLL = "SELECT * FROM Leagues " & _
  20.       "Where League_Id_No = " & strLeagueIdNo & ""
  21.       Debug.Print strSQLL
  22.     Set leaguerst = dbs.OpenRecordset(strSQLL)
  24.     strSQLR = "SELECT * FROM Registrations " & _
  25.       "Where League_Id_No = " & strLeagueIdNo & ""
  26.       Debug.Print strSQLR
  27.       Set regsrst = dbs.OpenRecordset(strSQLR)
  28.       regsrst.MoveLast
  29.       NoTeams = regsrst.RecordCount
  30.       regsrst.MoveFirst    
  31.     strSQLT = "SELECT * FROM Teams " & _
  32.       "Where Team_Id_No = " & TeamId & ""
  33.       Debug.Print strSQLT
  34.     Set teamrst = dbs.OpenRecordset(strSQLT)
  36.     TeamName = teamrst("Team_Name").Value
  37.     HeadCoach = teamrst("Head_Coach").Value
  38.     Address = teamrst("Address").Value
  39.     City = teamrst("City").Value
  40.     State = teamrst("State").Value
  41.     Zip = teamrst("Zip").Value
  42.     HomePhone = teamrst("Home_Phone").Value
  43.     WorkPhone = teamrst("Work_Phone").Value
  44.     CellPhone = teamrst("Cell_Phone").Value
  46.     DoCmd.DeleteObject acTable, "League_Schedules"
  48.     Set tfdnew = dbs.CreateTableDef("League_Schedules")
  50.     Set ind = tfdnew.CreateIndex("PrimaryKey")
  52.     With ind
  53.         .Fields.Append .CreateField("Receipt_No")
  54.         .Unique = False
  55.         .Primary = True
  56.     End With
  58.      ' Append the new TableDef object to the Athletics Database
  59.     tfdnew.Indexes.Append ind
  61.    With tfdnew
  62.       ' The CreateField method will set a default Size
  63.       ' for a new Field object if one is not specified.
  64.       .Fields.Append .CreateField("Receipt_No", dbInteger)
  65.       .Fields.Append .CreateField("ASA_No", dbText)
  66.       .Fields.Append .CreateField("League_Id_No", dbInteger)
  67.       .Fields.Append .CreateField("League_Name", dbText)
  68.       .Fields.Append .CreateField("No_Days", dbInteger)
  69.       .Fields.Append .CreateField("No_Games", dbInteger)
  70.       .Fields.Append .CreateField("Game_Type", dbInteger)
  71.       .Fields.Append .CreateField("Season_Start_Date", dbInteger)
  72.       .Fields.Append .CreateField("Age_Type", dbInteger)
  73.       .Fields.Append .CreateField("Day1", dbInteger)
  74.       .Fields.Append .CreateField("Day2", dbInteger)
  75.       .Fields.Append .CreateField("Day3", dbInteger)
  76.       .Fields.Append .CreateField("Time1", dbInteger)
  77.       .Fields.Append .CreateField("Time2", dbInteger)
  78.       .Fields.Append .CreateField("Time3", dbInteger)
  79.       .Fields.Append .CreateField("Time4", dbInteger)
  80.       .Fields.Append .CreateField("Time5", dbInteger)
  81.       .Fields.Append .CreateField("Location_1", dbInteger)
  82.       .Fields.Append .CreateField("Location_2", dbInteger)
  83.       .Fields.Append .CreateField("Location_3", dbInteger)
  84.       .Fields.Append .CreateField("Location_4", dbInteger)
  85.       .Fields.Append .CreateField("Location_5", dbInteger)
  86.       .Fields.Append .CreateField("Team_Name", dbInteger)
  87.       .Fields.Append .CreateField("Head_Coach", dbInteger)
  88.       .Fields.Append .CreateField("Address", dbInteger)
  89.       .Fields.Append .CreateField("City", dbInteger)
  90.       .Fields.Append .CreateField("State", dbInteger)
  91.       .Fields.Append .CreateField("Zip", dbInteger)
  92.       .Fields.Append .CreateField("Home_Phone", dbInteger)
  93.       .Fields.Append .CreateField("Work_Phone", dbInteger)
  94.       .Fields.Append .CreateField("Cell_Phone", dbInteger)
  95.    End With
Sep 4 '08 #1
11 2802
32,311 Expert Mod 16PB
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.
Sep 5 '08 #2

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

Sep 6 '08 #3
32,311 Expert Mod 16PB
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?
Sep 6 '08 #4
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.. :-)
Sep 8 '08 #5
Stewart Ross
2,545 Expert Mod 2GB
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.

Sep 8 '08 #6
32,311 Expert Mod 16PB
...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.
Sep 8 '08 #7

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…
Sep 9 '08 #8
Stewart Ross
2,545 Expert Mod 2GB
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.

Sep 9 '08 #9
32,311 Expert Mod 16PB
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.
Sep 10 '08 #10
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!!

Sep 13 '08 #11
32,311 Expert Mod 16PB
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!
Sep 13 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by olliver.mierau | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.