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
11 3112 NeoPa 32,564
Recognized Expert Moderator MVP
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.
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 32,564
Recognized Expert Moderator MVP
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?
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_Schedule s. I will then generate reports from the newly created League_Schedule s table.
What I am having problems with, is how do I get the information from the recordsets into the new table League_Schedule s? Do I need to use the INSERT INTO with VALUES?
I hope this makes more sense.. :-)
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 32,564
Recognized Expert Moderator MVP
...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.
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_Schedule s 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…
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 32,564
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: olliver.mierau |
last post by:
Hi,
I have a Access 2000 database which store in some tables about 500.000
recordsets. As the performance is not very impressive I created a table in a
MySQL database and connected via ODBC. If I try to transfer the data via
Access Append-Query an error comes up with something like "not possible to
go back > you want to cointinue?". After...
|
by: Vikrant |
last post by:
Hey friends
Can someone please suggest that how many recordset can be opened
simultaneously for a MS-Access 2000 Database.Does that make any
difference if we use ODBC or some other way of connecting to the
database.
|
by: Bob Alston |
last post by:
Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven't
had performance issues to worry about. <knock on wood>
But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the...
|
by: Greg Strong |
last post by:
Hello All,
Hello All,
What are the ToDo's / Gotchas to convert an MDB Access 2K2 database to
an Access Project (i.e. ADP) file for the front end using Microsoft
SQL Server 2000 Desktop Engine (MSDE 2000) for the back end?
Now for the background. I have a prototype MDB file that was built in
Access 2K2, and compiled in Access 2K to...
|
by: Johann Blake |
last post by:
I am looking for a good solution on how to implement data access in an
application so that there is a clean separation between the data access
layer, the business layer and the GUI layer. I am looking for a robust
solution for a major application. Almost every developer seems to come
up with a completely different solution. While many of them...
| |
by: Cy |
last post by:
Hello Fellow New Group Folks,
Here's today's problem. I was called in to help convert an Access 97
database to Access 2000. 99% of all my Access Dev. work has occurred
in 2000, so I know very little about 97, however, from everything I
read, it sounded like a conversion from 97 to 2000 should go smoothly.
Boy was I wrong.
Here's what...
|
by: TC |
last post by:
I need an automated procedure to copy data from an Access table to a
SQL Server table. Speed is important. What is the recommended
technique?
I can export the data from Access, copy it via FTP, then import it into
SQL Server. I've tried that, and the speed is acceptable. It is an ugly
solution, however, and I expect to find a better one --...
|
by: rguarnieri |
last post by:
Hi!
I'm trying to create a query with a boolean expression like this:
select (4 and 1) as Value from Table1
this query return always -1, but when I make the same calculation in
visual basic, the value returned is 0.
Can anyone tell me why the expression (4 and 1) return different value
|
by: jsacrey |
last post by:
Hello everybody,
I've got a bit of a situation that I could use some guidance with if
possible. I work for an auditing firm where my users audit electronic
shipping data for customers to see if they've been overcharged for
shipments by truck and rail carriers.
99.9% of the time, one of our auditors needs to see all data sent by a...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |