SQL and VBA

[ted.martin]

I have an Append query that I need to run successively several times BUT with a field name changing each time.

My code is:

Dim strSQL As String

strSQL = "INSERT INTO MediaMail ( [e-mail1] )" & _
"SELECT Contacts.[e-mail1]" & _
"FROM [Contacts]" & _
"WHERE Contacts.[Media1]= -1;"
DoCmd.RunSQL strSQL

The only part in the SQL that needs to change is [Media1] as the next time it is called the field is Media2, then Media3 etc.

How can I use VBA code to change the name of this field in the SQL statement?

Thanks


Ted

 

[RuralGuy]

How about:

Dim strSQL As String
Dim Loop As Integer

For Loop = 1 to 10

strSQL = "INSERT INTO MediaMail ( [e-mail1] )" & _
"SELECT Contacts.[e-mail1]" & _
"FROM [Contacts]" & _
"WHERE Contacts.[Media[b]" & Loop & "[/b]]= -1;"

DoCmd.RunSQL strSQL

Next

 

[ted.martin]

Very good - simple when you know how. I won't use Loop but an integer variable that I can direct the value of - but it works. Thanks a lot.

 

[RuralGuy]

Glad I could help.

 

[ted.martin]

Rural Guy - Do you know why I need square brackets around the table name [Contacts] in the FROM line? All the references I have seen don't have these brackets. I know it is needed as the code gives an error if I don't Just wondering my the references say otherwise.

Thanks

 

[RuralGuy]

Shouldn't be needed. What error do you get?

Edit: You do need a space before WHERE or after Contacts!

 

[ted.martin]

Removing the square brackets I get an Error 3131 Syntax Error in FROM clause. But them back as per the thread above and it works. Hmmmmmm

 

[ted.martin]

My Syntax is:


strMediaMailSQL = "INSERT INTO MediaMail ( [e-mail1] )" & _
"SELECT Contacts.[e-mail1]" & _
"FROM [Contacts]" & _
"WHERE Contacts.[Media" & intMediaField & "]= -1;"

 

[RuralGuy]

Add trailing spaces to the 1st 3 lines:

strMediaMailSQL = "INSERT INTO MediaMail ( [e-mail1] ) " & _
"SELECT Contacts.[e-mail1] " & _
"FROM Contacts " & _
"WHERE Contacts.[Media" & intMediaField & "]= -1;"

 

[ted.martin]

Yep thats fixed it. So why the extra spaces? and why does the square brackets absolve the need for these space? [Just so I learn for next time I plagerise the code for something else?]

Thanks RG

 

[RuralGuy]

You get used to it after a while but when in doubt, stick a MsgBox just prior to the error to print the string or do a Debug.Print and you'll see the problem. Glad I could help.

Edit: Brackets [ ] are an internal delimiter for Access which is why you can surround [Names with spaces in them] and it works!

 

[workmad3]

the extra spaces change it from FROM ContactsWHERE to FROM Contacts WHERE.

As RuralGuy has put, the [] brackets tell access where a name is, so if you have

FROM [Contacts]WHERE, it will see the brackets around contacts, pull the entire thing out as a table name and then go on to the next bit which is now just WHERE, and the entire thing works.