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"))
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
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