CODEWITHSUNDEEP
vbams-access
DNguyen
DNguyen

Reputation: 11

Microsoft Visual Basic - Run-time error '3075: Syntax error (missing operator) in query expression "1st", 'A', 1-Jan-15', '1')'

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

Upvotes: 0

Views: 566

Answers (2)

Gustav
Gustav

Reputation: 55841

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

Upvotes: 0

dbmitch
dbmitch

Reputation: 5386

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 & "')"

Upvotes: 0

Related Questions