英文:
How can I run multiple DDL statements from a recordset loop?
问题
以下是您要翻译的内容：
"Hopefully an easy one- for obvious reasons, I would like to rename several thousand field names to remove spaces and special characters across hundreds of tables. I put all the edits into a simple table, and generated the DDL statement for each field via a query.
Such as:
currentdb.TableDefs("tblA").Fields("Bad Field / Name - why_do_this").Name = "GoodFieldName"
I can run the DDL statement in the immediate window, no problem. But a dozen variations on trying to run this in a vba module via looping trough a recordset have failed.
If I try DoCmd.RunSQL (rs(0)) and get an error:
Run-time error '3129', Invalid SQL statement; expected 'DELETE','INSERT','PROCEDURE','SELECT', OR 'UPDATE'.
(I did verify rs(0) contains the DDL text).
Is there a way to run a DDL string from a recordset? Or do I need to build that string inside the VBA module? Thanks!!!
Edit: I gave up on Plan A. This code below worked.
Dim strSQL As String Dim rs As DAO.Recordset Dim db As DAO.Database, tdf As DAO.TableDef
Set db = CurrentDb
strSQL = "select TableName,FieldName,SQLFieldName from qryRemote_qryFieldsSQLOutput where TableName='" & Me.cmbTableName & "' and SQLFieldName is not null"
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs If Not .BOF And Not .EOF Then .MoveLast .MoveFirst While (Not .EOF) If VerifyFieldExists(rs("FieldName"), rs("TableName")) Then Set tdf = db.TableDefs(rs("TableName")) tdf(rs("FieldName")).Name = rs("SQLFieldName") End If
.MoveNext Wend End If End With
Set tdf = Nothing Set db = Nothing
MsgBox "Done"
End Sub ```"
<details>
<summary>英文:</summary>
Hopefully an easy one- for obvious reasons, I would like to rename several thousand field names to remove spaces and special characters across hundreds of tables. I put all the edits into a simple table, and generated the DDL statement for each field via a query.
Such as:
currentdb.TableDefs("tblA").Fields("Bad Field / Name - why_do_this").Name = "GoodFieldName"
I can run the DDL statement in the immediate window, no problem. But a dozen variations on trying to run this in a vba module via looping trough a recordset have failed.
If I try DoCmd.RunSQL (rs(0)) and get an error:
Run-time error '3129', Invalid SQL statement; expected 'DELETE','INSERT','PROCEDURE','SELECT', OR 'UPDATE'.
(I did verify rs(0) contains the DDL text).
Is there a way to run a DDL string from a recordset? Or do I need to build that string inside the VBA module? Thanks!!!
Edit: I gave up on Plan A. This code below worked.
Private Sub cmdUpdateFieldName_Click()
Dim strSQL As String
Dim rs As DAO.Recordset
Dim db As DAO.Database, tdf As DAO.TableDef
Set db = CurrentDb
strSQL = "select TableName,FieldName,SQLFieldName from qryRemote_qryFieldsSQLOutput where TableName='" & Me.cmbTableName & "' and SQLFieldName is not null"
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
If VerifyFieldExists(rs("FieldName"), rs("TableName")) Then
Set tdf = db.TableDefs(rs("TableName"))
tdf(rs("FieldName")).Name = rs("SQLFieldName")
End If
.MoveNext
Wend
End If
End With
Set tdf = Nothing
Set db = Nothing
MsgBox "Done"
End Sub
</details>
# 答案1
**得分**: 0
以下是您要翻译的内容：
You don't mention if you saved the SQL DDL "alter table" as queries, or that you have some text file or list of SQL commands that exists some place?
However, you can certainly have a text file of DDL commands, or a table of DDL commands, or even a bunch of saved queries as DDL commands.
All of the above are viable choices.
However, if we have a table say like this:
[![enter image description here][1]][1]
Then just build a form - continues or whatever, and we have this:
[![enter image description here][2]][2]
So, we can thus type in commands for each operation we want. And note in above, we terminate each command with a ";".
So, code to process the above (the above button code) is this:
Private Sub cmdSQL_Click()
If Me.Dirty Then Me.Dirty = False ' save current record
Dim rstSQL As DAO.Recordset
Dim strSQL As String
Dim SQLArray() As String
Dim vSQLrun As Variant
Set rstSQL = CurrentDb.OpenRecordset("SELECT * FROM tblSQL ORDER BY ID")
Do While rstSQL.EOF = False
SQLArray = Split(rstSQL!SQL, ";")
' Now execute each SQL command in this row
For Each vSQLrun In SQLArray
CurrentDb.Execute vSQLrun, dbFailOnError
'Debug.Print vSQLrun
Next
rstSQL.MoveNext
Loop
MsgBox "done"
End Sub
And note that to re-name a column you really can't use MS-Access DDL's commands. You have to create the new column, copy the data, drop the older column, and potentially add an index.
If that column is part of a relationship, then the above is not really a viable suggestion.
However, for some types of altering of a database, the above approach can be rather valuable.
However, due to the issue of indexing, and relationships (which will break with above code), then we could simply have a table of changes, and NOT use SQL DDL's commands, but use the DAO object model, and that will allow us to change the column name, but not have to copy nor create a new column.
However, the above does show how it is rather easy to execute multiple commands. As noted, we could also read line by line from a text file, and again a similar approach to above would allow one to "process" a text file of SQL scripting commands). So, above is a SQL script running solution.
The MS-access data engine can't execute multiple SQL commands at once, but if you split based on ";", then you achieve this ability with a small amount of VBA code.
While using DDL's commands I think is great for adding new columns?
For re-naming, then using DAO is a far better choice.
So, I suggest a table with these columns:
[![enter image description here][3]][3]
Now, again, we process each row, say like this:
Sub ChangeColumns()
Dim rstChanges As DAO.Recordset
Dim rtabledef As DAO.TableDef
Dim db As DAO.Database
Set db = CurrentDb
Set rstChanges = CurrentDb.OpenRecordset("SELECT * FROM tblRenameColumns ORDER BY ID")
Do While rstChanges.EOF = False
Set rtabledef = db.TableDefs(rstChanges!TableName)
Call MyColRename(rtabledef, rstChanges!OldColumn, rstChanges!NewColumn)
' db.TableDefs.Refresh ' optional - do at end of processing
rstChanges.MoveNext
Loop
rstChanges.Close
End Sub
Sub MyColRename(ByRef rtabledef As DAO.TableDef, sOldCol As String, sNewCol As String)
Dim fld As DAO.Field
For Each fld In rtabledef.Fields
If fld.Name = sOldCol Then
fld.Name = sNewCol
Exit For
End If
Next
End Sub
So, for new columns, I suggest the first example (using SQL DDL commands).
However, using the DAO object model is a far better choice to rename existing columns as above shows. No copy of data is required, no re-indexing, and in fact even relationships will remain intact.
However, existing SQL commands in the application, and existing forms, and VBA code all will of course require updates to reflect the new column name.
[1]: https://i.stack.imgur.com/c78Mj.png
[2]: https://i.stack.imgur.com/Tb2QX.png
[3]: https://i.stack.imgur.com/Tzv0D.png
<details>
<summary>英文:</summary>
You don't mention if you saved the SQL DDL "alter table" as queries, or that you have some text file or list of SQL commands that exists some place?
However, you can certainly have a text file of DDL commands, or a table of DDL commands, or even a bunch of saved queries as DDL commands.
All of the above are viable choices.
However, if we have a table say like this:
[![enter image description here][1]][1]
Then just build a form - continues or whatever, and we have this:
[![enter image description here][2]][2]
So, we can thus type in commands for each operation we want. And note in above, we terminate each command with a ";".
So, code to process the above (the above button code) is this:
Private Sub cmdSQL_Click()
If Me.Dirty Then Me.Dirty = False ' save current reocrd
Dim rstSQL As DAO.Recordset
Dim strSQL As String
Dim SQLArray() As String
Dim vSQLrun As Variant
Set rstSQL = CurrentDb.OpenRecordset("SELECT * FROM tblSQL ORDER BY ID")
Do While rstSQL.EOF = False
SQLArray = Split(rstSQL!SQL, ";")
' Now execute each SQL command in this row
For Each vSQLrun In SQLArray
CurrentDb.Execute vSQLrun, dbFailOnError
'Debug.Print vSQLrun
Next
rstSQL.MoveNext
Loop
MsgBox "done"
End Sub
And note that to re-name a column you really can't use MS-Access DDL's commands. You have to create the new column, copy the data, drop the older column, and potentially add an index.
If that column is part of a relationship, then the above is not really a viable suggestion.
However, for some types of altering of a database, the above approach can be rather valuable.
However, due to the issue of indexing, and relationships (which will break with above code), then we could simply have a table of changes, and NOT use SQL DDL' commands, but use the DAO object model, and that will allow us to change the column name, but not have to copy nor create a new column.
However, the above does show how it is rather easy to execute multiple commands. As noted, we could also read line by line from a text file, and again a similar approach to above would allow one to "process" a text file of SQL scripting commands). So, above is a SQL script running solution.
The MS-access data engine can't execute multiple SQL commands at once, but if you split based on ";", then you achieve this ability with a small amount of VBA code.
While using DDL's commands I think is great for adding new columns?
For re-naming, then using DAO is a far better choice.
So, I suggest a table with these columns:
[![enter image description here][3]][3]
Now, again, we process each row, say like this:
Sub ChangeColumns()
Dim rstChanges As DAO.Recordset
Dim rtabledef As DAO.TableDef
Dim db As DAO.Database
Set db = CurrentDb
Set rstChanges = CurrentDb.OpenRecordset("SELECT * FROM tblRenameColumns ORDER BY ID")
Do While rstChanges.EOF = False
Set rtabledef = db.TableDefs(rstChanges!TableName)
Call MyColRename(rtabledef, rstChanges!OldColumn, rstChanges!NewColumn)
' db.TableDefs.Refresh ' optional - do at end of processing
rstChanges.MoveNext
Loop
rstChanges.Close
End Sub
Sub MyColRename(ByRef rtabledef As DAO.TableDef, sOldCol As String, sNewCol As String)
Dim fld As DAO.Field
For Each fld In rtabledef.Fields
If fld.Name = sOldCol Then
fld.Name = sNewCol
Exit For
End If
Next
End Sub
So, for new columns, I suggest the first example (using SQL DDL commands).
However, using the DAO object model is a far better choice to rename existing columns as above shows. No copy of data is required, no re-indexing, and in fact even relationships will remain intact.
However, existing SQL commands in the application, and existing forms, and VBA code all will of course require updates to reflect the new column name.
[1]: https://i.stack.imgur.com/c78Mj.png
[2]: https://i.stack.imgur.com/Tb2QX.png
[3]: https://i.stack.imgur.com/Tzv0D.png
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论