Wrong path

[Kurt]

Hello Everyone,

I have the following code:

Sub get_applications()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("command")
    'Set ws = Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=CHECSQLDEV1\NEO"
        '"Data Source=C:\FromDyer.mdb;"
    Set comm.ActiveConnection = conn
    strSQL = "USE CHEC SELECT * FROM dbo.SMT_Branches"
     comm.CommandText = strSQL
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

However the information I am trying to access is on a sql serve but the workbook is on my local drive. What is the correct syntax to make the code see the sql server and the workbook that resides on my local disk drive.

TIA

Have a great day everyone!

Kurt

 

[Andrew Poulsom]

Does this help?

http://www.connectionstrings.com/

 

[Kurt]

Connection strings

Hello Andrew,

Thanks I have bookmarked your reference. Yes it will help for the future.

I know the problem in the code is here:

    'conn.Open "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=checsqldev1\neo3;"
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=checsqldev1\neo3"

Any other suggestions for me.

Thanks for looking at this and have a great day!

Kurt

 

[Andrew Poulsom]

According to this:

http://www.sqlstrings.com/SQL-Server-connection-strings.htm

you need something like:

'conn.Open "Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"

 

[Kurt]

Almost there

Hello Andrew,

Thanks I think it will work except for one thing.

I have the OLE Autmoation referenced check in the Tools References in the VBA editor.

The error message I get now is:

"Provier cannot be found, it may not be properly installed."

Which references should I be using?

Thanks,

Kurt

 

[Andrew Poulsom]

Do you have sqloledb.dll in C:\Program Files\Common Files\System\OLE DB?

 

[Kurt]

sqloledb.dll

Hello Andrew,

Yes it is there.

What should I check for next?

Thanks,

Kurt

 

[Andrew Poulsom]

See if this helps:

http://support.microsoft.com/kb/q278604/

 

[Kurt]

thanks I registered the dll ok but still errors

Hello Andrew,

Thanks I was able to register the dll.

Sub get_applications()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;Data Source=checsqldev1\neo3;Initial Catalog = dbo.SMT_Branches"
     Set comm.ActiveConnection = conn
    strSQL = "USE CHEC SELECT * FROM dbo.SMT_Branches"
    comm.CommandText = strSQL
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

This should work what am I doing wrong or what do I need to check next?

Thanks for all of your effort.

Kurt

 

[Kurt]

Query will work

Hello Andrew,

Here is a stump the commissioner. I was able to create a query and it does work!

Sub getdata_test()
'
' getdata_test Macro
' Macro recorded 10/2/2006 by Kurt
'

'
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=checsqldev1\neo3;Use Procedure for Prepare=1" _
        , _
        ";Auto Translate=True;Packet Size=4096;Workstation ID=KNICHOLS;Use Encryption for Data=False;Tag with column collation when possi" _
        , "ble=False;Initial Catalog=chec"), Destination:=Range("A1"))
        .CommandType = xlCmdTable
        .CommandText = Array("""chec"".""dbo"".""SMT_Branches""")
        .Name = "+New SQL Server Connection_2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Documents and Settings\knichols\My Documents\My Data Sources\checsqldev1_neo3 chec SMT_Branches.odc"
        .Refresh BackgroundQuery:=False
    End With
End Sub

What's up with that? And I was able to do this before without registering the dll.

thanks,

Kurt