By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,745 Members | 1,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,745 IT Pros & Developers. It's quick & easy.

Populate an Access 2000 Table with data from DAO Recordsets

P: 8
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.
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
  10.  
  11.     strLeagueIdNo = Me!cmbChoose_League.Column(0)
  12.     strLeagueName = Me!cmbChoose_League.Column(1)
  13.  
  14.     Set dbs = CurrentDb()
  15.  
  16.     Debug.Print strLeagueIdNo   
  17.     Debug.Print strLeagueName   
  18.  
  19.     strSQLL = "SELECT * FROM Leagues " & _
  20.       "Where League_Id_No = " & strLeagueIdNo & ""
  21.       Debug.Print strSQLL
  22.     Set leaguerst = dbs.OpenRecordset(strSQLL)
  23.  
  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)
  35.  
  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
  45.  
  46.     DoCmd.DeleteObject acTable, "League_Schedules"
  47.  
  48.     Set tfdnew = dbs.CreateTableDef("League_Schedules")
  49.  
  50.     Set ind = tfdnew.CreateIndex("PrimaryKey")
  51.  
  52.     With ind
  53.         .Fields.Append .CreateField("Receipt_No")
  54.         .Unique = False
  55.         .Primary = True
  56.     End With
  57.  
  58.      ' Append the new TableDef object to the Athletics Database
  59.     tfdnew.Indexes.Append ind
  60.  
  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
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,470
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

P: 8
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
Sep 6 '08 #3

NeoPa
Expert Mod 15k+
P: 31,470
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

P: 8
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

Expert Mod 2.5K+
P: 2,545
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
Sep 8 '08 #6

NeoPa
Expert Mod 15k+
P: 31,470
...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

P: 8
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…
Sep 9 '08 #8

Expert Mod 2.5K+
P: 2,545
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
Sep 9 '08 #9

NeoPa
Expert Mod 15k+
P: 31,470
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

P: 8
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
Sep 13 '08 #11

NeoPa
Expert Mod 15k+
P: 31,470
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.