CODEWITHSUNDEEP
ms-accessvbaloops
user1290426
user1290426

Reputation: 153

Loop through Access tables

I'm creating a database in Access that will have many similar tables with identical and the names of them will all be named in sequential order, such as table_0, table_1...

I wanted to know if there is a way to make a loop in VBA to first make the tables, and them altering them, such as adding a field easier.

So if I were to make the tables, this would be the process, but in VBA

while(i=0, i<20,i++){
CREATE TABLE  table_i(
field_1 int PRIMARY KEY,
field_2 CHAR(255));
}

Upvotes: 1

Views: 1812

Answers (1)

HansUp
HansUp

Reputation: 97101

In VBA, you can loop through your numbered table names like this ...

Dim strTable As String
Dim i As Long
For i = 0 To 20
    strTable = "table_" & CStr(i)
    Debug.Print strTable
Next i

But you want to do something with each table name other than print it. So say you've already worked out your CREATE TABLE statement. Maybe it looks like this ...

CREATE TABLE table_name (
field_1 INTEGER PRIMARY KEY,
field_2 TEXT(255));

So you could load that DDL statement into a string variable, then each time through your For loop replace table_name with the current value of strTable, and execute that DDL statement.

Dim strTable As String
Dim i As Long
Dim strSql As String
strSql = "CREATE TABLE table_name (" & vbCrLf & _
    "field_1 INTEGER PRIMARY KEY," & vbCrLf & _
    "field_2 TEXT(255));"
For i = 0 To 20
    strTable = "table_" & CStr(i)
    CurrentProject.Connection.Execute Replace(strSql, _
        "table_name", strTable)
Next i

That Replace() function first became available with Access 2000. If you're using an older Access version, you'll need a different method to handle your SQL string.

If you want to execute an ALTER TABLE statement, you could do that instead.

So I think doing what you want can be easily done with VBA. However I'm uncertain whether you should do it. Multiple tables with identical structure is generally a red flag signalling a design error. There is no evidence to conclude it's a design error in your case. OTOH, there is no evidence to conclude it's not a design error. ;-)

Upvotes: 2

Related Questions