Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    5

    Unanswered: Append records to table using VB. HELP!

    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!!

  2. #2
    Join Date
    Apr 2002
    Posts
    139

    Re: Append records to table using VB. HELP!

    Think you may not need to revert to VB code at first instance.

    Try to make a select query using the normal query interface, and see if you can select the records you want to append, by linking the various tables. If you have designed your tables in a normalized way, this should be possible.
    You will probably need to define your form values like SongId, GroupId, PerfDate as parameter values (query criteria).

    If you are satisfied with your Select query, than turn it into a regular Append query into your Performance table.

    Finally, set your forms update button to start this update query.

    Seems to me like a more simple way than all through VBA.

    hth

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    This is not the solution for the problem. I have Access 2000(9.0.2720) Visual basic 6.0 and I can't see to get the define word "Database" in VBA on Dim dbs as Database. Also I can't get the define word "CurrentDb" in VBA on Set dbs = CurrentDB.

    Do I need to update my Access application to get the define words?

    Also, I'm viewing the code from the first post in this thread.
    Last edited by lansing; 04-11-03 at 15:19.

  4. #4
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey lansing,

    I would recommend using ADO. First make sure you reference Microsoft ADO Ext. X.X in visual basic. Goto Tools/references then scroll down until you find it. Then use something like this:

    Dim rs as New ADODB.Recordset
    rs.Open "Enter you SQL here....", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    If rs.EOF Then
    Exit Sub
    End If
    rs.MoveFirst
    Do Until rs.EOF
    DoCmd.RunSQL "Enter you INSERT INTO Statement here..."
    rs.MoveNext
    Loop
    rs.Close
    Exit Sub

    This will scroll threw your recorset and enter the data you require where needed. You can use DAO to do this aswell, I just prefer ADO. If your using DAO make sure your reference Microsoft DAO X.X Object Library is checked. Same way as above.

    Let me know if you have any questions.

    Kal

  5. #5
    Join Date
    Apr 2003
    Posts
    280
    What does the reference for? I got my code to work on last Friday without having a check beside the Microsoft ADO ext x.x.

    Anyways, I have a question. Is there anyway to add a new record from one database to another database. Here my code now currently. Look for the arrow for my coding problem. I can use this code for one database but cannot use it between databases. Just wondering if there a syntax to link to another database within VBA

    Private Sub Command0_Click()

    Dim conndb As ADODB.Connection
    Dim strSQL As String
    Dim rsmwtrn As ADODB.Recordset
    Dim rsmwtrn2 As ADODB.Recordset

    Set conndb = CurrentProject.Connection
    Set rsmwtrn = New ADODB.Recordset
    Set rsmwtrn2 = New ADODB.Recordset

    -->rsmwtrn.Open "Select * From DatabaseName!Tables!mwtrn", conndb, adOpenKeyset, adLockPessimistic, adCmdTable

    rsmwtrn2.Open "mwtrn2", conndb, adOpenKeyset, adLockPessimistic, adCmdTable

    Do Until rsmwtrn2.EOF
    If rsmwtrn2.EOF = True Then
    MsgBox "No more record"
    Exit Sub
    Else
    rsmwtrn.AddNew
    rsmwtrn("Chart Number") = rsmwtrn2("Chart Number")
    rsmwtrn("Case Number") = rsmwtrn2("Case Number")
    rsmwtrn("Entry Number") = rsmwtrn2("Entry Number")
    rsmwtrn("Claim Number") = rsmwtrn2("Claim Number")
    rsmwtrn("Date From") = rsmwtrn2("Date From")
    rsmwtrn("Date To") = rsmwtrn2("Date To")
    rsmwtrn("Document Number") = rsmwtrn2("Document Number")
    rsmwtrn("Description") = rsmwtrn2("Description")
    'Other fields...etc...

    rsmwtrn.Update
    rsmwtrn2.MoveNext
    End If
    Loop
    End Sub
    Last edited by lansing; 04-14-03 at 11:59.

  6. #6
    Join Date
    Apr 2003
    Posts
    280
    Ok, I just found this code and I think this will work but I'm getting an error for it. The error is "Object variable or With block variable not set", Run-time error 91. What does that mean within my connection string.

    conndb2.ConnectionString = "Data source = C:\Documents and Settings\Administrator.TRITONSVR\Desktop\CHINH\Dat abaseName.mdb;"
    conndb2.open

  7. #7
    Join Date
    Feb 2004
    Posts
    47

    Dim xxx as Database does not work

    Hi Lansing,

    I have the same problem. Dim xxx as Database does not work in on mdb while it works in another!!!

    Did you find out how to resolve this?

    Regards,
    Kashi.


    Originally posted by lansing
    This is not the solution for the problem. I have Access 2000(9.0.2720) Visual basic 6.0 and I can't see to get the define word "Database" in VBA on Dim dbs as Database. Also I can't get the define word "CurrentDb" in VBA on Set dbs = CurrentDB.

    Do I need to update my Access application to get the define words?

    Also, I'm viewing the code from the first post in this thread.

  8. #8
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    make sure your Microsoft DAO or Microsoft ADO reference is checked...

    Regards

  9. #9
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147

    I am very sorry I misread your VB bit, I though it was purely Access, I wont delete this as others may look at it. - to eager to help...

    You can still use that sort of string in VB though !



    If it gets too complicated, you could always use docmd.runsql

    I always look for the simplist route, sometimes however, I have to write VBscript in Access because not everything is that easy...Damn it...

    Easy solution might be like:

    Create a query, click on the sql instead of design or run and copy the code, overtype the relevant fields.

    example of a dbase I just wrote for someone here to update a table:

    docmd.setwarnings = false

    Sql = "UPDATE table_a "
    Sql = Sql & " SET table_a.Machine_Spec1 = " & Me.N_Sp1 & ", "
    Sql = Sql & " table_a.Machine_Spec2 = " & Me.N_Sp2 & ", "
    Sql = Sql & " table_a.Machine_Spec3 = " & Me.N_Sp3 & ", "
    Sql = Sql & " table_a.Machine_Spec4 = " & Me.N_Sp4 & ""
    Sql = Sql & " WHERE (((table_a.key)=" & Me.Machines_List & "));"


    DoCmd.RunSQL Sql

    docmd.setwarnings = true

    You'll see that me.n_sp1 to n_sp4 are variables, so is machine_list, these are fields on a form.

    Didnt use any flash recordsets, or connection strings...No errors and it works..Don't get me wrong, the DAO and ADO ways are just as good.

    Alternatively I think your Connection string, if you use that route could be made simpler, like kpalmer said:

    Set Cnn = CurrentProject.Connection

    That should get rid of that error you get (I hope )

    ~Ken..
    Last edited by Ken_Hart; 02-27-04 at 08:13.

  10. #10
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Use Currentdb.Execute instead of docmd.runsql if you dont need the messages from access.

    Using docmd.runsql this would require using Set warnings as well where using Execute does not.


    REgarsd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •