用于检查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屋！