用于检查SQL Server 2014表是否存在的Vba代码 [英] Vba code to check if SQL server 2014 table exist
问题描述
我有一个能够连接到我的SQL Server 2014的代码。我正在尝试检查表是否已经存在,如果没有,我正在尝试根据excel的列单元格(命名范围)添加表格
I have a code which is able to connect to my SQL server 2014. I am trying to check if a table already exist and if not i am trying to add the table based on an excel's column cell(named range)
For each Row
当我单步执行代码时,在
When I step through the code, at
Set rs = HITBTCupdate.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "table"))
我看到了数据库中的第一个表传递给rs(2).value。
代码继续,我可以根据我的excel单元更新数据库。
after HITBTCcmd.Execute在代码中
I see that the first table in the Database is passed into rs(2).value.
The code continues and i am able to update the database based on my excel cells.
after HITBTCcmd.Execute within the code
Next Row
我看到rs(2 ).value仍然有数据库中的第一个表,而不是数据库中的第二个表。我假设我没有遍历
I see rs(2).value still has the first table in the database and not the second table i have within the database. Which i assume i am not iterating through the
rs
数组。
我尝试过的:
array.
What I have tried:
Set rs = HITBTCupdate.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "table"))
HITBTCcmd.ActiveConnection = HITBTCupdate
For Each Row In rng
tblName = Row
If rs(2).Value = Row Then
HITBTCcmd.CommandText = GetinsertText
Else
HITBTCcmd.Execute "CREATE TABLE " & tblName & " ([Row] text(50) WITH Compression, " & _
"[Column1.ask] decimal(10) WITH Compression, " & _
"[Column1.bid] decimal(10) WITH Compression, " & _
"[Column1.last] decimal(10) WITH Compression, " & _
"[Column1.open] decimal(10) WITH Compression, " & _
"[Column1.low] decimal(10) WITH Compression, " & _
"[Column1.high] decimal(10) WITH Compression, " & _
"[Column1.volume] decimal(10))WITH Compression," & _
"[Column1.volumeQuote] decimal(10) WITH Compression, " & _
"[Column1.timestamp] Datetime(20) WITH Compression, " & _
"[Column1.symbol] text(10) WITH Compression,)) "
HITBTCcmd.CommandText = GetinsertText
HITBTCcmd.Execute
End If
Next Row
HITBTCupdate.Close
Set HITBTCupdate = Nothing
End Sub
推荐答案
使用VBA枚举SQL Server数据库
通过VBA比较SQl Server表中的数据 [ ^ ]
这篇关于用于检查SQL Server 2014表是否存在的Vba代码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!