Access SQL vba question

maximus02

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

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

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

maximus02

Amadeus

Thanks a lot. That worked fine.

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

Thanks again

--amadeus--

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