Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Access SQL vba question

  • 08-07-2009 05:23PM
    #1
    maximus02
    Registered Users, Registered Users 2 Posts: 455 ✭✭


    Hi.

    I hope someone can help with a question about Access SQL. To fill some of my spare time, which I have lots of at the moment, I am trying to teach myself some basic SQL with MS Access 2007.

    I'm trying to populate table with 'INSERT INTO'. At present I just have 2 fields in database which are [ID] and [CodeNumber]. ID is primary key.

    I'm trying to create 100 records with simple serial number ID field and then a code number which is created elsewhere in the code. This is what I did:

    Dim mySQL2 As String
    Dim xx As Integer

    For xx = 1 To 100

    mySQL2 = "INSERT INTO test"
    mySQL2 = mySQL2 & " (ID, [codeNumber])"
    mySQL2 = mySQL2 & " VALUES ( xx, 'Codefromelsewhere')"

    DoCmd.RunSQL mySQL2

    Next xx


    The value 'xx' won't work. I assume I need something to identify this as a variable from the loop. At present when I run the form, it asks me to enter a value for 'x'.

    Make sense?
    Thanks in advance


Comments

  • #2
    mark renton
    Closed Accounts Posts: 2,696 ✭✭✭mark renton


    maximus02 wrote: »
    Dim mySQL2 As String
    Dim xx As Integer

    For xx = 1 To 100

    mySQL2 = "INSERT INTO test"
    mySQL2 = mySQL2 & " (ID, [codeNumber])"
    mySQL2 = mySQL2 & " VALUES ( xx, 'Codefromelsewhere')"

    DoCmd.RunSQL mySQL2

    Next xx


    The value 'xx' won't work. I assume I need something to identify this as a variable from the loop. At present when I run the form, it asks me to enter a value for 'x'.

    I'm a bit lost here, not sure about using variables in SQL - however in SQL isnt the % sign used to request input from user


  • #3
    --amadeus--
    Closed Accounts Posts: 5,095 ✭✭✭--amadeus--


    I'm not able to test this at the minute (cooking dinner!) but you don't need to identify xx as a variable to the SQL as such. Essentially what you are doing is creating a text string variable that happens to be a piece of SQL code and then executing it.

    However I think your error is that it is treating xx as part of teh string rtaher than a variable in it's own right. Try:

    mySQL2 = mySQL2 & " VALUES ( " & xx & ", 'Codefromelsewhere')"

    the VBA is treating everything inside the " " as part of teh text string so by breaking it it can see the variable.


  • #4
    maximus02
    Registered Users, Registered Users 2 Posts: 455 ✭✭maximus02


    Amadeus

    Thanks a lot. That worked fine.

    Of course it all makes sense in hindsight. I should have known that.

    Thanks again


  • #5
    --amadeus--
    Closed Accounts Posts: 5,095 ✭✭✭--amadeus--


    hindsight is *always* 20:20, the obvious stuff is often teh hardest to see!


Advertisement