Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1

    Unanswered: Insert Into Join Table - sql string issue

    Hi

    Could anyone give me a steer (or a good kick) to help with an INSERT INTO problem. I have three tables in play ... A Hardware table, a UserGroups Table and a UserGroupsToServers join table. The join table allows me to have multiple servers assigned to a group and vice versa.

    I want to bulk assign a Usergroup to a bunch of servers based on the work stream that the servers are assigned to in the Hardware table.

    e.g I want to be able to assign the Migration User group to ALL servers in the Hardware table, whose Stream is Migration.

    This means writing into the two fields (Hardware_FK and UserGroup_FK) in the UserGroupsToServers join table.

    I build the sql string from a form and it writes thus:

    INSERT INTO UserGroupsToServers ( UserGroup_FK, Hardware_FK) SELECT 7 AS UserGroup_FK, Hardware_FK FROM hardware WHERE hardware.Stream = 'Migration';

    However I am getting the old 3061 too few parameters error

    I have tried this in the SQL Query writer and when I run, it prompts for the Hardware_FK value, which tells me it is not getting that.

    Any clue as to why?

    I have spent hours playing around with the single and double quotes thing, to no avail but now I need some intelligent help.

    Any steer (or kick) welcome


  2. #2
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    In the string I am building, I am proposing to SELECT HARDWARE_FK from the Hardware table and that field does not exist in that table. Howare do I tell it, to use the HARDWARE_ID from the Hardware table, as the HARDWARE_FK in the UserGroupsToServers table?

  3. #3
    Join Date
    Mar 2011
    Posts
    2
    Points I thought
    You have more fields that are mandatory to insert.
    You query is returning invalid data


    SELECT distinct 7 AS UserGroup_FK, Hardware_FK FROM hardware WHERE hardware.Stream = 'Migration' and Hardware_FK is not null

  4. #4
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Thanks Alcalina

    I have crated a loop to to work on a select * recordset but the issue with that is that Access does not like inserting into multiple records using the VALUES method. It want to insert based on a SELECT which means that the following code wont' work, even though the SQL strings is builds DO work but only when they are run individually ((


    UserGroup = Me.cbo_UserGroup
    Stream = Me.cbo_StreamList
    strSQL = "SELECT * FROM Hardware WHERE Stream = '" & Stream & "';"

    Debug.Print strSQL

    Set rstHardwareServers = dbsHardware_Current.OpenRecordset(strSQL, dbOpenDynaset)

    'If the recordset is empty, exit.
    If rstHardwareServers.EOF Then Exit Sub

    intI = 1
    With rstHardwareServers
    Do Until .EOF
    HardwareID = rstHardwareServers!HW_ID
    'MsgBox "The new hardware_ID is " & HardwareID, vbInformation
    strSQL = "INSERT INTO UserGroupsToServers (Hardware_FK, UserGroup_FK) " & _
    "Select (" & HardwareID & "," & UserGroup & ");"

    Debug.Print strSQL
    rstHardwareServers.MoveNext
    intI = intI + 1
    Loop
    End With

Posting Permissions

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