Dynamic name of string variable (1 Viewer)

Wysy

Registered User.
Local time
Yesterday, 17:31
Joined
Jul 5, 2015
Messages
333
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
Code:
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

Nifty Access Guy
Staff member
Local time
Today, 01:31
Joined
Jul 9, 2003
Messages
16,364
Try:-
Code:
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

Nifty Access Guy
Staff member
Local time
Today, 01:31
Joined
Jul 9, 2003
Messages
16,364
Oops! Sorry, that won't work. I thought strSQL was a variable, I'll have a proper look...
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 10:31
Joined
Jan 20, 2009
Messages
12,859
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
Code:
 DELETE FROM mytable
WHERE somefield = 1
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:31
Joined
May 7, 2009
Messages
19,246
You arr correct, the op want to display the "content" of the var.
 

Wysy

Registered User.
Local time
Yesterday, 17:31
Joined
Jul 5, 2015
Messages
333
my question refers only to display that string variable content correctly
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 01:31
Joined
Jul 9, 2003
Messages
16,364
Try:-
Code:
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

You've got your good things, and you've got mine.
Local time
Yesterday, 20:31
Joined
May 21, 2018
Messages
8,605
Code:
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

Nifty Access Guy
Staff member
Local time
Today, 01:31
Joined
Jul 9, 2003
Messages
16,364
This would be Better:-

Code:
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

Registered User.
Local time
Yesterday, 17:31
Joined
Jul 5, 2015
Messages
333
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

Nifty Access Guy
Staff member
Local time
Today, 01:31
Joined
Jul 9, 2003
Messages
16,364
OR:-

Code:
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

Registered User.
Local time
Today, 10:31
Joined
Jul 4, 2013
Messages
2,774
The code in #9 is scalable to hold any practical number of SQL strings. You could also use an array.

Code:
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
 

Cronk

Registered User.
Local time
Today, 10:31
Joined
Jul 4, 2013
Messages
2,774
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

MVP / VIP
Local time
Today, 01:31
Joined
Jan 14, 2017
Messages
18,258
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

Registered User.
Local time
Yesterday, 17:31
Joined
Jul 5, 2015
Messages
333
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

MVP / VIP
Local time
Today, 01:31
Joined
Jan 14, 2017
Messages
18,258
@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

Super Moderator
Staff member
Local time
Today, 10:31
Joined
Jan 20, 2009
Messages
12,859
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

AWF VIP
Local time
Today, 01:31
Joined
Jul 21, 2014
Messages
2,328
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)
Code:
  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
 
Last edited:

Users who are viewing this thread

Top Bottom