loopy question

[tempk]

Hi all,
Still new to VBA and I am trying to create a function that runs an append query like this:

Dim strSQL_1, strSQL_2, strSQL_3 As String

strSQL_1 = "INSERT INTO tblConsolidate([variable]) SELECT [variable] FROM qryResults"
strSQL_2 = "......FROM qryOtherResults"
...so on and so forth

Docmd.RunSQL strSQL_1
Docmd.RunSQL strSQL_2

Because I am such a lazy person and the number of queries might increase in the future, I am trying to get a loop done...

Dim i as Integer

For i = 1 TO 3
Docmd.RunSQL strSQL_i
Next i
End Sub


But I have yet figured out a way to substitute the integer at the end of strSQL_.

Can this be done? Thanks in advance!

 

[Mile-O]

Dim strSQL_1, strSQL_2, strSQL_3 As String

You are making two Variants and one String variable. For each variable you need to declare its data type. If no data type is supplied then it is, by default, a Variant.

The following is correct. I've also removed the underscore as it's a best practice to remove all special characters from field, object, ruotine, constant and variable names.

Dim strSQL1 As String, strSQL2 As String, strSQL3 As String

It's best to dimension each variable one line at a time. It also makes it easier to add comments to your code in this way.

i.e.

Dim strSQL1 As String ' For results query
Dim strSQL2 As String ' For other results query
Dim strSQL3 As String ' For yet another query

strSQL_1 = "INSERT INTO tblConsolidate([variable]) SELECT [variable] FROM qryResults"
strSQL_2 = "......FROM qryOtherResults"
...so on and so forth

Here, it looks like these SQL statements are not in any way dynamic and can only contribute to database bloat due to having to recreate and delete them every time this piece of code is run. I'd create them as queries and just use the DoCmd.OpenQuery QueryName method.

What is the field called Variable all about? Is it supposed to be some sort of parameter?

Docmd.RunSQL strSQL_1
Docmd.RunSQL strSQL_2

Because I am such a lazy person and the number of queries might increase in the future, I am trying to get a loop done...

Dim i as Integer

For i = 1 TO 3
Docmd.RunSQL strSQL_i
Next i
End Sub

No, you can't do this. Also, nevermind being lazy - why do you think the number of queries might increase? If you believe this then it's possible that your design is flawed.


But I have yet figured out a way to substitute the integer at the end of strSQL_.

Can this be done? Thanks in advance![/QUOTE]

 

[tempk]

Hey..thanks for the reply.

The idea to do all this in vb was because I did not want to create more queries.

The [variable] is coined because some tables contain all the fields I require while some only have some I need.

The number of sql string might increase because there might be more items added on. (But I wouldn't be around to do modify anything..)

Thanks again for the reply.

 

[Mile-O]

The idea to do all this in vb was because I did not want to create more queries.

If there wasn't a good reason for having a queries collection in the database then we would create all of our queries in VBA.

It's best only to create true dynamic queries with VBA and for those that stay the same although have different criteria these should be saved as query definitions.

Every time you a query and save it then it occupies a bit of space but you can run that query multiple times and it will only occupy that bit of space. Every time you create a query with VBA the query has to be created and, once it has executed, is deleted. The space here does not get reclaimed until you compact the database meaning that the database could grow to obscene sizes.

You never responded to my line about the design being flawed; from what you say I believe this to be true.