0

I'm trying to add 50,000 records to my Tournament_Result table in Microsoft Access but I have encountered this error: Run-time error '3075: Syntax error (missing operator) in query expression "1st", 'A', 1-Jan-15', '1')'.

  Option Compare Database
  Option Explicit

  Sub arrayData()
  Dim TournamentResult() As Variant
  Dim DivisionEntered() As Variant
  Dim DateOfTournament() As Variant
  Dim num As Long, TournamentResultNo As Long, MembershipNo As Long, dbs As    Database, InsertRecord As String
  Dim num1 As Long 'we need to declare num1 as an Integer to create a loop
  Dim TournamentResultDescription As String, DivisionEnteredDescription As String, DateOfTournamentDescription As String


  Set dbs = CurrentDb()
  TournamentResultNo = 0
  MembershipNo = 0

  TournamentResultNo = TournamentResultNo + 1
  TournamentResult = Array("1st", "2nd", "3rd", "4th", "5th", "6th", "7th",       "8th", "9th", "10th")
  DivisionEntered = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J")
  DateOfTournament = Array("1-Jan-15", "2-Feb-15", "3-Mar-15", "4-Apr-15", "5-May-15", "6-Jun-15", "7-July-15", "8-Aug-15", "9-Sep-15", "10-Oct-15", "11-Nov-15", "12-Dec-15")
  MembershipNo = MembershipNo + 1

  For num1 = 0 To 50000 

  num = Int((50000 - 0 + 1) * Rnd + 0)
  TournamentResultDescription = TournamentResult(num)
  DivisionEnteredDescription = DivisionEntered(num)
  DateOfTournamentDescription = DateOfTournament(num)

  InsertRecord = "insert into TOURNAMENT_RESULT(TournamentResultNo, TournamentResult, DivisionEntered, DateOfTournament, MembershipNo) values (" & "'" & TournamentResultNo & "'" & "," & "'" & TournamentResultDescription & "'" & "'" & "," & "'" & DivisionEnteredDescription & "'" & "," & "'" & DateOfTournamentDescription & "'" & "," & "'" & MembershipNo & "'" & ")"

  dbs.Execute InsertRecord
  Debug.Print TournamentResultNo; TournamentResultDescription; DivisionEnteredDescription; DateOfTournamentDescription; MembershipNo

  Next

End Sub

EDIT: dbs.Execute InsertRecord is highlighted as the problem

DNguyen
  • 11
  • 1
    Please use parameterized query. – Niyoko Nov 02 '16 at 03:02
  • What do you mean by parameterized query? – DNguyen Nov 02 '16 at 03:03
  • 2
    Read [here](http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i) – Niyoko Nov 02 '16 at 03:06
  • Remove one of these: `& "'" & "'"`. Your query doesn't have balanced quotes: `values ('',''','','','')`. – Comintern Nov 02 '16 at 03:09
  • 1
    You have a horrific mismatch in quote characters, missing single quotes, and quotes where they shouldn't be used in the error message you've posted. Either it's not the actual message, or the code you've posted is not your actual code. If you learn to use parameterized queries, you won't have to worry about properly pairing and escaping quote characters. You'll also protect yourself against SQL injection. – Ken White Nov 02 '16 at 03:09
  • So I removed {& "'" & "'"} but now I have the problem: 'Run-time error: '9': Subscript out of range. With the {TournamentResultDescription = TournamentResult(num)} code line highlighted as the problem. – DNguyen Nov 02 '16 at 03:20
  • That's because `TournamentResult` only has 10 elements, and you're selecting random indexes between 0 and 50001. – Comintern Nov 02 '16 at 03:23
  • Oh ok, I think I understand where I'm going wrong now. Thank you Comintern! – DNguyen Nov 02 '16 at 03:31
  • @NiyokoYuliawan you mean [read here](http://stackoverflow.com/a/16571156/1188513), this is VBA in MS-Access/DAO, not VB.NET. – Mathieu Guindon Nov 02 '16 at 03:45
  • @Mat'sMug Yeah. I saw `Dim` statement and immediately thought it's VB.NET without saw the tags. – Niyoko Nov 02 '16 at 03:48

2 Answers2

0

Commenters are correct in their suggestions.

These types of dynamic statements can be difficult to debug because of the concatenated strings - and you're making it doubly worse by doing extra concatenation of delimiters and commas.

To me it looks like you just had an extra single quote ("'") in your SQL.

Try:

InsertRecord = _
    "insert into TOURNAMENT_RESULT(TournamentResultNo, TournamentResult, DivisionEntered, DateOfTournament, MembershipNo) values ('" & _
        TournamentResultNo & "','" & TournamentResultDescription & "','" & _
        DivisionEnteredDescription & "','" & DateOfTournamentDescription & _
        "','" & MembershipNo & "')"
dbmitch
  • 5,361
  • 4
  • 24
  • 38
0

This is probably what you are after:

Public Function InsertRange()

    Const Results   As Long = 50000

    Dim dbs         As DAO.Database
    Dim rst         As DAO.Recordset

    Dim Num101      As Integer
    Dim Num102      As Integer
    Dim Num12       As Integer
    Dim ResultNo    As Long

    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("Select Top 1 * From TOURNAMENT_RESULT")

    Randomize
    For ResultNo = 1 To Results
        Num101 = 1 + Int(10 * Rnd)
        Num102 = 1 + Int(10 * Rnd)
        Num12 = 1 + Int(12 * Rnd)
        rst.AddNew
            rst!TournamentResultNo.Value = ResultNo
            rst!TournamentResult.Value = CStr(Num101) & "st"
            rst!DivisionEntered.Value = Chr(64 + Num102)
            rst!DateOfTournament.Value = DateSerial(2015, Num12, Num12)
            rst!MembershipNo.Value = ResultNo
        rst.Update
    Next
    rst.Close

    Set rst = Nothing
    Set dbs = Nothing

End Function
Gustav
  • 53,498
  • 7
  • 29
  • 55