How to specify a schema name in a connection string VBA

Question

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

[Did you search prior to asking?](http://stackoverflow.com/questions/3282665/possible-to-set-default-schema-from-connection-string) — Kyle, Jun 20 '16 at 21:17
As mentioned in the answer posted below: you are connecting to a server and **not** a schema or a database on the server. You can specify a default database in the connection string to override anything specified for the user on the SQL server. But even that can be overridden in the query (very much like the schema). You should learn and read-up about SQL before updating your VBA. Afterwards, you will come to the conclusion that you'll have to update this line: `wrk = "UPDATE " & InstName & "." & TableName & vbCrLf & "SET" & Chr(10)` so that you get `UPDATE DataBaseName.SchemaName.TableName`. — Ralph, Jun 20 '16 at 21:56

score 0 · Answer 1 · edited May 23 '17 at 12:08

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

How to specify a schema name in a connection string VBA

1 Answers1