Append records to MS Access table using VB 
Author Message
 Append records to MS Access table using VB

I'm working on an Access 2000 DB to track musical performances. The
relevant tables and fields are:

Performance - PerfID (AutoNumber), PerfDate, SongID, GroupID, MemberID
GroupMember - GM_ID (AutoNumber), GroupID (Primary Key of Groups Table),
MemberID (Primary Key of Members Table)

I have a form to enter the PerfDate, SongID and GroupID since they are
the same for each performance and I'd like to append a record to the
Performance table for each member of the group.

THe farthest I've gotten is:

Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click
Dim dbs As Database, qdf As QueryDef, strSQL As String, rst As Recordset
Dim pdate As Date group As Integer, song As Integer

Set dbs = CurrentDb
txtDate.SetFocus
pdate = txtDate.Value
cmbGroup.SetFocus
group = cmbGroup.Value
cmbSong.SetFocus
song = cmbSong.Value

strSQL = ""
dbs.QueryDefs.Refresh
For Each qdf In dbs.QueryDefs
If qdf.name = "qryGroupMembers" Then
dbs.QueryDefs.Delete qdf.name
End If
Next qdf

strSQL = "SELECT GroupID,MemberID FROM GroupMember WHERE GroupID
= " + group

Set qdf = dbs.CreateQueryDef("qryGroupMembers", strSQL)
Set rst = dbs.OpenRecordset("qryGroupMembers")

For Each Record In rst

 . .

Well, that's it. I can't seemd to find any help on the VB Append Method
and at this point I'm not even sure if that's right solution.

I'm also open to creating a new table to temporarily store the data from
my form and the data from the recordset and then using an append query
to get it all into the Performance table. Unfortunately, I can't find
the proper syntax for the the Create TableDef command.

Any help is greatly appreciated!!

--
Posted via http://www.***.com/



Wed, 13 Jul 2005 20:35:08 GMT
 Append records to MS Access table using VB

Sounds like an interesting project.

First, my preferred method of adding records is to use SQL's INSERT
command to do the dirty work. General syntax is:

INSERT INTO Performance (PerfDat, SongID, GroupID, MemberID) VALUES
('1/24/2003', 23, 81, 4671)

Execute this at the Connection level and you should have your new record
in the Performance table.

I have some concern about your table definitions though.

1. The GroupMember table has an autonumber field. I assume this is to be
the primary key for the table. However, the two other fields seem to be
all you would need for a primary key since they should / could not be
duplicates. All this table is doing is linking the Group table with the
Members table, right?

2. In the Peformance table, you reference the GroupID, MemberID and
Performance Date. I assume this is required since the Group may have
different Members at any given date. The GroupMember table should not be
linked to this at all. The GroupMember table would only be used to
select the members of a group at a given point in time. However, what if
a member plays on a performance that is not a member of the group? What
if they are not a member of any group? GroupID = Null? I suggest you
have a Performance table and another seperate PerformanceMember table.

Performance Table Structure
PerfID
MemberID
GroupID (allow nulls)

The GroupID would not necessarily be the same as the GroupMember link
table since - as I recently read - John Lennon may do a performance with
Mick Jagger at some point.

Good luck,
Dan


says...

Quote:

> I'm working on an Access 2000 DB to track musical performances. The
> relevant tables and fields are:

> Performance - PerfID (AutoNumber), PerfDate, SongID, GroupID, MemberID
> GroupMember - GM_ID (AutoNumber), GroupID (Primary Key of Groups Table),
> MemberID (Primary Key of Members Table)

> I have a form to enter the PerfDate, SongID and GroupID since they are
> the same for each performance and I'd like to append a record to the
> Performance table for each member of the group.

> THe farthest I've gotten is:

> Private Sub cmdUpdate_Click()
> On Error GoTo Err_cmdUpdate_Click
> Dim dbs As Database, qdf As QueryDef, strSQL As String, rst As Recordset
> Dim pdate As Date group As Integer, song As Integer

> Set dbs = CurrentDb
> txtDate.SetFocus
> pdate = txtDate.Value
> cmbGroup.SetFocus
> group = cmbGroup.Value
> cmbSong.SetFocus
> song = cmbSong.Value

> strSQL = ""
> dbs.QueryDefs.Refresh
> For Each qdf In dbs.QueryDefs
> If qdf.name = "qryGroupMembers" Then
> dbs.QueryDefs.Delete qdf.name
> End If
> Next qdf

> strSQL = "SELECT GroupID,MemberID FROM GroupMember WHERE GroupID
> = " + group

> Set qdf = dbs.CreateQueryDef("qryGroupMembers", strSQL)
> Set rst = dbs.OpenRecordset("qryGroupMembers")

> For Each Record In rst

>  . .

> Well, that's it. I can't seemd to find any help on the VB Append Method
> and at this point I'm not even sure if that's right solution.



Thu, 14 Jul 2005 00:54:24 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. LAN Traffic issues between using VB Frontend/MS Access Backend vs Just MS Access

2. Retriving the current Record from MS Access using VB

3. Appending records from Access 97 tables in SQL tables

4. appending records to ACCESS db with VB code

5. appending records to ACCESS db with VB code

6. Comparing 2 tables of records in Access using VB

7. Access 97/SqlServer 7: Appending Access Tables into Sql Server tables doesnt work for big tables

8. Can't append excel 97 worksheet to Access 2.0 database using VB 4.0

9. Moving Oracle records to MS SQL Server using MS Access

10. Append records from one table to another using ADO

11. Saving a previous address to a table via append query in acce ss using VB

12. Help: Using MS Access 95 w/ VB 5 for Novice VB programmer


 
Powered by phpBB® Forum Software