Change Connection from DSN to SQL Server in Excel Macro? 1

[DBLoser]

We're replacing an old AS 400 database with a new SQL database. I've recreated the view in the SQL database but now I have an Excel spreadsheet referencing the AS 400 via DSN connection through a macro. Here is the current connection string:

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=AS 400;", _
                Destination:=Sheets("temp").Range("A1"))

What I am trying to get to work is:

With ActiveSheet.QueryTables.Add(Connection:="Provider=SQLOLEDB;Data Source=EnertiaProd;Initial Catalog=EnertiaValidation;Integrated Security=SSPI;", _
                Destination:=Sheets("temp").Range("A1"))

But when I run the macro I get a Run-time error '1004: Application-defined or object-defined error. Just for more reference here's the rest of the original connection and it's sql statement:

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=AS 400;", _
                Destination:=Sheets("temp").Range("A1"))
                .CommandText = Array( _
                "SELECT digits(ENGOPF.PRODYR)||'-'||digits(ENGOPF.PRODMO)||'-'||digits(ENGOPF.PRODDA), SUM(ENGOPF.H2OBBL) FROM S1021663.LIB003.ENGOPF ENGOPF " _
                , _
                "WHERE (ENGOPF.WELL = '" & wellnum & "') AND (ENGOPF.LEASE=" _
                , _
                leasenum & ") AND (digits(ENGOPF.PRODYR)||'-'||digits(ENGOPF.PRODMO)||'-'||digits(ENGOPF.PRODDA)>='" _
                & startdate & "') AND (digits(ENGOPF.PRODYR)||'-'||digits(ENGOPF.PRODMO)||'-'||digits(ENGOPF.PRODDA)<='" & enddate & "')  " _
                , _
                "GROUP BY (ENGOPF.LEASE),(ENGOPF.WELL),digits(ENGOPF.PRODYR)||'-'||digits(ENGOPF.PRODMO)||'-'||digits(ENGOPF.PRODDA)" _
                , _
                "ORDER BY digits(ENGOPF.PRODYR)||'-'||digits(ENGOPF.PRODMO)||'-'||digits(ENGOPF.PRODDA)" _
                )
                .Name = "download"
                .FieldNames = False
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlOverwriteCells
                .SavePassword = True
                .SaveData = False
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = False
                .Refresh BackgroundQuery:=False
            End With

I have altered the FROM statement in the query and hope it works with SQL but haven't gotten past the connection for testing:

"SELECT digits(ENGOPF.PRODYR)||'-'||digits(ENGOPF.PRODMO)||'-'||digits(ENGOPF.PRODDA), SUM(ENGOPF.H2OBBL) FROM dbo.vwExtProducedWater ENGOPF " _

Thanks for any help!

 

[dhookom]

Have you checked out SQL Server Connection Strings.

MS SQL Server doesn't like [highlight #FCE94F]||[/highlight]. You might want to try something like:

"SELECT digits(ENGOPF.PRODYR) + '-' + digits(ENGOPF.PRODMO) + '-' + digits(ENGOPF.PRODDA), SUM(ENGOPF.H2OBBL) FROM S1021663.LIB003.ENGOPF ENGOPF " _

Duane
Hook'D on Access
MS Access MVP

 

[DBLoser]

Thanks Dhookom, I think you've helped me in the past and thanks for the link, there's good info on that site. The simplest fix was to just create a new ODBC connection to the SQL server but I wanted to do a DSN-less connection. For now, this worked:

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=ODBCSQL;", _

And per your suggestion, I rewrote the SQL statement to talk to the SQL server. I'm not used to the , _ wrapping but did get it to work:

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=ODBCSQL;", _
                Destination:=Sheets("temp").Range("A1"))
                .CommandText = Array( _
                "SELECT ", _
                "Right('0000' + Cast(PRODYR as Varchar(4)),4) + '-' + Right('00' + Cast(PRODMO as Varchar(2)),2) + '-' + Right('00' + Cast(PRODDA as varchar(2)),2), ", _
                "SUM(H2OBBL) AS H2OBBL ", _
                "FROM EnertiaValidation.dbo.vwExtProducedWater ", _
                "WHERE ", _
                "Right('0000' + Cast(PRODYR as Varchar(4)),4) + '-' + Right('00' + Cast(PRODMO as Varchar(2)),2) + '-' + Right('00' + Cast(PRODDA as varchar(2)),2) ", _
                "BETWEEN '" & startdate & "' AND '" & enddate & "' AND LEASE ='" & leasenum & "' AND WELL = '" & wellnum & "' ", _
                "GROUP BY LEASE, WELL, WELLCODE ,", _
                "Right('0000' + Cast(PRODYR as Varchar(4)),4) + '-' + Right('00' + Cast(PRODMO as Varchar(2)),2) + '-' + Right('00' + Cast(PRODDA as varchar(2)),2) ", _
                "ORDER BY ", _
                "Right('0000' + Cast(PRODYR as Varchar(4)),4) + '-' + Right('00' + Cast(PRODMO as Varchar(2)),2) + '-' + Right('00' + Cast(PRODDA as varchar(2)),2)")

                .Name = "download"
                .FieldNames = False
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlOverwriteCells
                .SavePassword = True
                .SaveData = False
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = False
                .Refresh BackgroundQuery:=False

When I get some time I'm going to try to figure out the DSN-less connection.

Thanks!