Connect to SQL Database - Use Named Ranges to Define Server/Database

[jjobrien03]

I am having trouble using named ranges to define my connection variables. The code works when I just type in the server/database names but I cannot get them to reference properly when using excel named ranges.

Database = positions
Server = MyDatabase/SQLEXPRESS

Sub SendPositionstoSQL()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim Server As String
    Server = Range("SQLServer").Value
    Dim Dbase as String
   Dbase = Range("Dbase").Value

    sConnString = "Provider=SQLOLEDB;Data Source=Server;" & _
                  "Initial Catalog=Dbase;" & _
                  "Integrated Security=SSPI;"
    
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    conn.Open sConnString

Appreciate any help - thanks.

 

[John_w]

Try:

sConnString = "Provider=SQLOLEDB;Data Source=" & Server & ";Initial Catalog=" & Dbase & ";Integrated Security=SSPI;"

 

[jjobrien03]

Try:

sConnString = "Provider=SQLOLEDB;Data Source=" & Server & ";Initial Catalog=" & Dbase & ";Integrated Security=SSPI;"

That structure worked - thank you very much