Dynamic name of string variable (1 Viewer)

[Wysy]

Hi,
I try to name a string "dynamically". More precisely having a multiple variables holding strings and i would like to refer to these variables dinamically

Dim strSQL1 as string
dim strSQL2 as string
dim x as string
strSQL1="DELETE FROM table1;"
strSQL2="DELETE FROM tabel2"
for i=1 to 2
    x="strSQL" & i
    msgbox x
next i

Unfortunately this does not work so msgbox displays strSQL1 and strSQL2 instead of there content. What do i do wrong?
thanks

 

[Uncle Gizmo]

Try:-

Dim strSQL1 as string
dim strSQL2 as string
dim x as string
strSQL1="DELETE FROM table1;"
strSQL2="DELETE FROM tabel2"
for i=1 to 2
    x=strSQL & i
    msgbox x
next i

 

[Uncle Gizmo]

Oops! Sorry, that won't work. I thought strSQL was a variable, I'll have a proper look...

 

[Galaxiom]

Just a feeling, but the nature of your question strongly hints that your tables are not normalized. Dynamically addressing tables suggests you are classifying data by the table it is located in when this attribute should be denoted by a value.

A much more typical SQL in a normalized structure would be

 DELETE FROM mytable
WHERE somefield = 1

 

[arnelgp]

You arr correct, the op want to display the "content" of the var.

 

[Wysy]

thank you

 

[Wysy]

my question refers only to display that string variable content correctly

 

[Uncle Gizmo]

Try:-

Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "DELETE FROM table1;"
strSQL2 = "DELETE FROM tabel2"

Dim x As String
Dim i As Integer

        For i = 1 To 2
                Select Case i
                    Case 1
                        x = strSQL1
                    Case 2
                        x = strSQL2
                End Select
            MsgBox " >>> " & x
        Next i

 

[MajP]

Public Sub Test()
 Dim cSQL As New Collection
 Dim I As Integer
 cSQL.Add "DELETE FROM table1;", "SQL1"
 cSQL.Add "DELETE FROM table2;", "SQL2"
 For I = 1 To 2
     MsgBox cSQL("SQL" & I)
 Next I
 End Sub

 

[Uncle Gizmo]

This would be Better:-

Dim x As String
Dim i As Integer

        For i = 1 To 2
                Select Case i
                    Case 1
                        x = "DELETE FROM table1;"
                    Case 2
                        x = "DELETE FROM tabel2"
                End Select
            MsgBox " >>> " & x
        Next i

 

[Wysy]

Thank you for this. Actually this way i got iworking. Though i would like to add several, more than 2 possibility for the variables name.

 

[Uncle Gizmo]

OR:-

Dim x As String
Dim i As Integer

Dim str As String
str = "DELETE FROM table"

        For i = 1 To 2
            x = "DELETE FROM table" & i & ";"
            MsgBox " >>> " & x
        Next i

 

[Cronk]

The code in #9 is scalable to hold any practical number of SQL strings. You could also use an array.

Dim strSQL1 as string
dim strSQL2 as string
dim x(100) as string

strSQL(1)="DELETE FROM table1;"
strSQL(2)="DELETE FROM tabel2"

for i=1 to 2
   x=strSQL( i)
   msgbox x
next i

 

[isladogs]

@Cronk
I don't believe that will work. Have you tried it?

 

[Cronk]

No, I haven't tested the code. But have I missed something? I thought the idea was to store a number of SQL strings in variables that they could be referred to dynamically. If the SQL string is valid, what would be the issue with

currentdb.execute strSQL(54)

Of course, the question has not been asked of the OP, why store in variables. I expect most of us would store the SQL instances in a table.

 

[isladogs]

As I understand it, the OP just wants to print out the contents of each SQL string in a message box,..though I'm not clear why

 

[Wysy]

I am developing an application and i wanted to have a possibility to "reset" the database while testing, in other words to delete records in multiple tables. So i thought to create those DELETE FROM sql statements, name them so that the difference in the names is an integer at the end: str1, str2, str3, etc. I did not want to write the docmd.runsql str1, etc so on for each sql statement and wanted to use the for ... next loop that "changes" the last character of the sql name. Then i realized that the so created name those not represent the sql statement as variable.

 

[isladogs]

@Wysy
You have been given several solutions that do work. Have you tried any of them?
Of course, you still need to add code to actually run the delete statements
I think you will find the repeated message boxes an irritant after a while

 

[Galaxiom]

I am developing an application and i wanted to have a possibility to "reset" the database while testing, in other words to delete records in multiple tables.

In a properly designed database, this task is a lot more complex than you probably imagine. Relationships enforced with referential integrity demand that the tables be depopulated in a logical order. Otherwise you will get errors that prevent deleting the record in a parent table.

You can sit down and work out the order of all the commands for a specific database but you would be much better off using code to work through the Tabledefs Collection and analysing their Relationships. Basically you go though and empty any tables that have no children while reading about their parentage, working up through the tree and out along the branches.

Any database adhering to a set of design criteria (such a naming pattern to nominate which tables should be cleared) can be processed by exactly the same code. It takes more to set up but becomes an investment in a tool you might end up using countless times into the future by simply dropping it in.

That is what portabilty of code is about. Think about your coding this way and you won't end up doing much the same code over and over again for different databases throughout your career.

Better still, use a back end database that supports TRUNCATE.

 

[cheekybuddha]

MajP offers a solution using a Collection which is nice because you can reference its members using a string key(which can built up using concatenation), but in this instance it offers little advantage over using a simpler array (I think Cronk was on the right track, just got the code muddled up)

  Dim strSQL(50) As String, i As Integer

  strSQL(0) = "DELETE FROM table1;"
  strSQL(1) = "DELETE FROM table2;"
' ...
  strSQL(49) = "DELETE FROM AnotherTable;"

  For i = 0 To Ubound(strSQL)
'    MsgBox strSQL(i)
    CurrentDb.Execute strSQL(i), dbFailOnError
  Next i

If your tables are truly named Table1, Table2 ... (unlikely, I think) then UncleGizmo gives an even simpler solution in post#12