How to specify a schema name in a connection string VBA

sql excel vba connection-string

1127 просмотра

1 ответ

I have a database with two schemas consisting of the same set of tables. I am connecting to the database with a connection string in VBA and using the VBA to create insert scripts. However, since there are two schemas with the same tables, my code is returning insert scripts for every table in both schemas. I need to know how to specify which schema to use. below is the code i am using..

Sub GenerateStandardBusinessRule_UpdateSQL()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rsSys As Recordset
Dim ServName As String
Dim dbName As String
Dim dbType As String
Dim TableName As String
Dim InstName As String
Dim wrk As String
Dim sUpdStart As String
Dim sUpdEnd As String
Dim i
Dim sSQL As String
Dim sUser As String
Dim sPW As String
Dim sField As String
Dim n As Integer


'Instantiate variables
ServName = Workbooks("BusinessRules_UpdatesBuilder.xlsm").Sheets("BuildStatement").Range("A2").Value
dbName = Workbooks("BusinessRules_UpdatesBuilder.xlsm").Sheets("BuildStatement").Range("B2").Value
InstName = Workbooks("BusinessRules_UpdatesBuilder.xlsm").Sheets("BuildStatement").Range("C2").Value
TableName = Workbooks("BusinessRules_UpdatesBuilder.xlsm").Sheets("BuildStatement").Range("D2").Value
dbType = Workbooks("BusinessRules_UpdatesBuilder.xlsm").Sheets("BuildStatement").Range("E2").Value
wrk = "UPDATE " & InstName & "." & TableName & vbCrLf & "SET" & Chr(10)

If dbType = "Varchar" Then
    sUpdStart = "= NULLIF(LTRIM(RTRIM(": sUpdEnd = ")),'')"
Else
    sUpdStart = "=CAST(": sUpdEnd = " as date)"
End If


'Get Data type info
sSQL = "SELECT Column_Name " & _
       "FROM Information_Schema.Columns " & _
       "WHERE Table_Name = " & Chr(39) & TableName & Chr(39) & " " & _
       "AND Data_Type = " & Chr(39) & dbType & Chr(39) & ""


'Set db Connection and open Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=" & ServName & " ; Initial Catalog=" & dbName & "; User ID=ETLUser; Password=xxxxxxxx;"
Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic

'Loop through recordset and contruct SQL Update statement
If Not rs.BOF Then
    rs.MoveLast
    i = rs.RecordCount
    n = 1
    rs.MoveFirst
    Do Until rs.EOF
        sField = rs("Column_Name")
        If n < i Then
            wrk = wrk & vbTab & sField & sUpdStart & sField & sUpdEnd & "," & Chr(10)
        Else
            wrk = wrk & vbTab & sField & sUpdStart & sField & sUpdEnd
        End If
        rs.MoveNext
        n = n + 1
    Loop

End If
'Update Excel Spreadsheet
Workbooks("BusinessRules_UpdatesBuilder.xlsm").Sheets("BuildStatement").Range("F3").Value = wrk

End Sub

Автор: Ashley Источник Размещён: 01.11.2019 06:16

Ответы (1)


0 плюса

As far as using a schema in a connection string you cannot. that is done via the queries themselves. Possible to set default schema from connection string?. You can can set a different database....

As far as finding the schema to get value and use as variable in other queries, in sql-server you can use the system views to get the schema name here is a query that based on your code I am assuming you can modify

SELECT
    s.name AS SchemaName
    ,t.name AS Tablename
    ,c.name AS ColumnName
    ,ty.name AS TypeName
FROM
    sys.columns c
    INNER JOIN sys.tables t
    ON c.object_id = t.object_id
    INNER JOIN Sys.schemas s
    ON t.schema_id = s.schema_id
    INNER JOIN sys.types ty
    ON c.system_type_id = ty.system_type_id
Автор: Matt Размещён: 20.06.2016 09:40
Вопросы из категории :
32x32