Wrong path

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Hello Everyone,

I have the following code:

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Re: thanks I registered the dll ok but still errors

Hello Andrew,

Thanks I was able to register the dll.

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")
    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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Jet not sql

Hello Andrew,

Yes I have ADO checked in references also.

I was trying another route.

Kurt
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry, I'm a bit lost now. What error do you get with Conn.Open using Provider=SQLOLEDB?
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
error message

Hello Andrew,

I get the "Provider cannot be found it may not be properly installed on this line of code
Code:
    conn.Open "Provider=SQLOLDEDB;Data Source=checsqldev1\neo3;Initial Catalog = dbo.SMT_Branches;Integrated Security=SSPI;"

Kurt
 

Forum statistics

Threads
1,147,497
Messages
5,741,501
Members
423,662
Latest member
Ajmal Khursand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top