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]

Re: 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

You are useing Jet there, not SQL.

 

[Kurt]

Jet not sql

Hello Andrew,

Yes I have ADO checked in references also.

I was trying another route.

Kurt

 

[Andrew Poulsom]

Sorry, I'm a bit lost now. What error do you get with Conn.Open using Provider=SQLOLEDB?

 

[Kurt]

error message

Hello Andrew,

I get the "Provider cannot be found it may not be properly installed on this line of code

    conn.Open "Provider=SQLOLDEDB;Data Source=checsqldev1\neo3;Initial Catalog = dbo.SMT_Branches;Integrated Security=SSPI;"

Kurt