Provider Cannot be found

**Biplab1985** · 03-05-2017, 06:42 AM

Hi,

I am not sure where i am going wrong but everytime i run this code it gives me an Run time Error 3706: Provider Could not be Found. I have checked Activex Data Object 2.8 Reference.

Sub a()
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''Access database

strFile = "Application.Thisworkbook.path\Database.accdb"

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

'Rough idea
intMonth = Sheet1.[H2]
intYear = Sheet1.[H3]


strSQL = "SELECT * " _
       & "FROM qryMonthlyValuesSingleLine " _
       & "WHERE [Enter Month Name] = " & intMonth _
       & " AND WHERE [Enter Year Name] = " & intYear
       
rs.Open strSQL, cn, 3, 3

Worksheets("Sheet2").Cells(2, 1).CopyFromRecordset rs

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

**Biplab1985** · 03-05-2017, 08:19 AM

I fixed the above error but now i am currently getting another "No Value Given for one or more parameter"

**AB33** · 03-05-2017, 08:23 AM

You are using late binding and do not need to tick any reference, but you are using the wrong version of ADO

Provider=Microsoft.Jet.OLEDB.4.0

Should be

Provider = "Microsoft.ACE.OLEDB.12.0"

**AB33** · 03-05-2017, 08:30 AM

You might be getting errors on SQL statement.
Follow this link which shows you how to construct a date

http://www.excelforum.com/excel-prog...ml#post4597530

**xlnitwit** · 03-05-2017, 09:51 AM

Hi

Perhaps you did not declare the parameters properly in Access but merely entered them as criteria values in the SQL query. What does the SQL of your query look like?

**Biplab1985** · 03-05-2017, 10:14 AM

The Access Query looks like

SELECT qryMonthlyValues.Measures, qryMonthlyValues.Customer, qryMonthlyValues.Brewer, qryMonthlyValues.Brand, qryMonthlyValues.SubBrand, qryMonthlyValues.InnerPackage, qryMonthlyValues.ContainerVolume, qryMonthlyValues.SKU, Sum(qryMonthlyValues.M) AS M_Total, Sum(qryMonthlyValues.[M-1]) AS [M-1_Total], Sum(qryMonthlyValues.[M-2]) AS [M-2_Total], Sum(qryMonthlyValues.[M-3]) AS [M-3_Total], Sum(qryMonthlyValues.[M Y-1]) AS [M Y-1_Total], Sum(qryMonthlyValues.[M-1 Y-1]) AS [M-1_Y-1_Total], Sum(qryMonthlyValues.[M-2 Y-1]) AS [M-2 Y-1_Total], Sum(qryMonthlyValues.[M-3 Y-1]) AS [M-3 Y-1_Total], Sum(qryMonthlyValues.[YTD]) AS YTD_Total, Sum(qryMonthlyValues.[YTD Y-1]) AS [YTD Y-1_Total], Sum(qryMonthlyValues.[YTD Y-2]) AS [YTD Y-2_Total], Sum(qryMonthlyValues.[MAT]) AS MAT_Total, Sum(qryMonthlyValues.[MAT_Y-1]) AS [MAT_Y-1_Total]
FROM qryMonthlyValues
WHERE ((qryMonthlyValues.Month) Like [Enter Month Name] & "*") AND((qryMonthlyValues.Year) Like [Enter Year Number] & "*")
GROUP BY qryMonthlyValues.Measures, qryMonthlyValues.Customer, qryMonthlyValues.Brewer, qryMonthlyValues.Brand, qryMonthlyValues.SubBrand, qryMonthlyValues.InnerPackage, qryMonthlyValues.ContainerVolume, qryMonthlyValues.SKU;

**Biplab1985** · 03-05-2017, 01:06 PM

Anyone suggestions please.

**xlnitwit** · 03-05-2017, 01:25 PM

You need to either declare the parameters properly in the Access query window, or perhaps use ? instead of the parameter names in your SQL query from Excel.

**Biplab1985** · 03-05-2017, 03:45 PM

That i have done, is there anything i am missing here, It run properly is access query without any error but not in excel

WHERE ((qryMonthlyValues.Month) Like [Enter Month Name] & "*") AND((qryMonthlyValues.Year) Like [Enter Year Number] & "*")

**xlnitwit** · 03-05-2017, 05:12 PM

If they were declared properly in Access, the SQL would begin with a PARAMETER statement.

**Biplab1985** · 03-06-2017, 05:24 AM

I added the parameters to sql but no luck

PARAMETERS [Enter Month Name] Text ( 255 ), [Enter Year Number] Text ( 255 );
SELECT qryMonthlyValues.Measures, qryMonthlyValues.Customer, qryMonthlyValues.Brewer, qryMonthlyValues.Brand, qryMonthlyValues.SubBrand, qryMonthlyValues.InnerPackage, qryMonthlyValues.ContainerVolume, qryMonthlyValues.SKU, Sum(qryMonthlyValues.M) AS M_Total, Sum(qryMonthlyValues.[M-1]) AS [M-1_Total], Sum(qryMonthlyValues.[M-2]) AS [M-2_Total], Sum(qryMonthlyValues.[M-3]) AS [M-3_Total], Sum(qryMonthlyValues.[M Y-1]) AS [M Y-1_Total], Sum(qryMonthlyValues.[M-1 Y-1]) AS [M-1_Y-1_Total], Sum(qryMonthlyValues.[M-2 Y-1]) AS [M-2 Y-1_Total], Sum(qryMonthlyValues.[M-3 Y-1]) AS [M-3 Y-1_Total], Sum(qryMonthlyValues.YTD) AS YTD_Total, Sum(qryMonthlyValues.[YTD Y-1]) AS [YTD Y-1_Total], Sum(qryMonthlyValues.[YTD Y-2]) AS [YTD Y-2_Total], Sum(qryMonthlyValues.MAT) AS MAT_Total, Sum(qryMonthlyValues.[MAT_Y-1]) AS [MAT_Y-1_Total]
FROM qryMonthlyValues
WHERE (((qryMonthlyValues.Month) Like [Enter Month Name] & "*") AND ((qryMonthlyValues.Year) Like [Enter Year Number] & "*"))
GROUP BY qryMonthlyValues.Measures, qryMonthlyValues.Customer, qryMonthlyValues.Brewer, qryMonthlyValues.Brand, qryMonthlyValues.SubBrand, qryMonthlyValues.InnerPackage, qryMonthlyValues.ContainerVolume, qryMonthlyValues.SKU;

**Biplab1985** · 03-06-2017, 05:12 AM

Could you help me re-write the SQL then?

**xlnitwit** · 03-06-2017, 05:19 AM

You should add this as the first line

PARAMETERS [Enter Month Name] Text ( 255 ), [Enter Year Number] Long;

**xlnitwit** · 03-06-2017, 05:31 AM

Is it the same error message? If so, can you provide the SQL for qryMonthlyValues as well please?

**Biplab1985** · 03-06-2017, 06:46 AM

Yes, I am getting the same error. I actually have 3 queries. So what i did is created sub queries and merged all. Below is the sql

PARAMETERS [Enter Month Name] Text ( 255 ), [Enter Year Number] Long;
SELECT qryMonthlyValues.Measures, qryMonthlyValues.Customer, qryMonthlyValues.Brewer, qryMonthlyValues.Brand, qryMonthlyValues.SubBrand, qryMonthlyValues.InnerPackage, qryMonthlyValues.ContainerVolume, qryMonthlyValues.SKU, Sum(qryMonthlyValues.M) AS M_Total, Sum(qryMonthlyValues.[M-1]) AS [M-1_Total], Sum(qryMonthlyValues.[M-2]) AS [M-2_Total], Sum(qryMonthlyValues.[M-3]) AS [M-3_Total], Sum(qryMonthlyValues.[M Y-1]) AS [M Y-1_Total], Sum(qryMonthlyValues.[M-1 Y-1]) AS [M-1_Y-1_Total], Sum(qryMonthlyValues.[M-2 Y-1]) AS [M-2 Y-1_Total], Sum(qryMonthlyValues.[M-3 Y-1]) AS [M-3 Y-1_Total], Sum(qryMonthlyValues.YTD) AS YTD_Total, Sum(qryMonthlyValues.[YTD Y-1]) AS [YTD Y-1_Total], Sum(qryMonthlyValues.[YTD Y-2]) AS [YTD Y-2_Total], Sum(qryMonthlyValues.MAT) AS MAT_Total, Sum(qryMonthlyValues.[MAT_Y-1]) AS [MAT_Y-1_Total]
FROM (SELECT qryTransposeWithDate.Measures, qryTransposeWithDate.Customer, qryTransposeWithDate.Brewer, qryTransposeWithDate.Brand, qryTransposeWithDate.SubBrand, qryTransposeWithDate.InnerPackage, qryTransposeWithDate.ContainerVolume, qryTransposeWithDate.SKU, qryTransposeWithDate.CurrentDate, qryTransposeWithDate.DataDate, qryTransposeWithDate.Month, qryTransposeWithDate.Year, IIf([CurrentDate]=[DataDate],[qryTransposeWithDate.Sales],0) AS M, IIf(DateAdd("m",-1,[CurrentDate])=[DataDate],[qryTransposeWithDate.Sales],0) AS [M-1], IIf(DateAdd("m",-2,[CurrentDate])=[DataDate],[qryTransposeWithDate.Sales],0) AS [M-2], IIf(DateAdd("m",-3,[CurrentDate])=[DataDate],[qryTransposeWithDate.Sales],0) AS [M-3], IIf(DateAdd("m",-12,[CurrentDate])=[DataDate],[qryTransposeWithDate.Sales],0) AS [M Y-1], IIf(DateAdd("m",-13,[CurrentDate])=[DataDate],[qryTransposeWithDate.Sales],0) AS [M-1 Y-1], IIf(DateAdd("m",-14,[CurrentDate])=[DataDate],[qryTransposeWithDate.Sales],0) AS [M-2 Y-1], IIf(DateAdd("m",-15,[CurrentDate])=[DataDate],[qryTransposeWithDate.Sales],0) AS [M-3 Y-1], IIf([DataDate] Between DateSerial(Year([CurrentDate]),1,1) and [CurrentDate],[qryTransposeWithDate.Sales],0) AS YTD, IIf([DataDate] Between DateSerial(Year([CurrentDate])-1,1,1) and DateAdd("yyyy",-1,[CurrentDate]),[qryTransposeWithDate.Sales],0) AS [YTD Y-1], IIf([DataDate] Between DateSerial(Year([CurrentDate])-1,1,1) and DateAdd("yyyy",-2,[CurrentDate]),[qryTransposeWithDate.Sales],0) AS [YTD Y-2], IIf([DataDate] Between DateAdd("m",-12,[CurrentDate]) and DateAdd("m",0,[CurrentDate]),[qryTransposeWithDate.Sales],0) AS MAT, IIf([DataDate] Between DateAdd("m",-23,[CurrentDate]) and DateAdd("m",-12,[CurrentDate]),[qryTransposeWithDate.Sales],0) AS [MAT_Y-1], IIf([DataDate] Between DateAdd("m",-34,[CurrentDate]) and DateAdd("m",-23,[CurrentDate]),[qryTransposeWithDate.Sales],0) AS [MAT_Y-2] FROM (SELECT Transpose.Measures, Transpose.Customer, Transpose.Brewer, Transpose.Brand, Transpose.SubBrand, Transpose.InnerPackage, Transpose.ContainerVolume, Transpose.SKU, CDate([Enter Month Name] & "/1/" & [Enter Year Number]) AS CurrentDate, CDate([Month] & "/1/" & [Year]) AS DataDate, Transpose.Sales, Transpose.Month, Transpose.Year FROM Transpose WHERE ((Transpose.Month) Like [Enter Month Name] & "*") AND((Transpose.Year) Like [Enter Year Number] & "*") ORDER BY CDate([Month] & "/1/" & [Year]))  AS qryTransposeWithDate WHERE ((qryTransposeWithDate.Month) Like [Enter Month Name] & "*") AND((qryTransposeWithDate.Year) Like [Enter Year Number] & "*") ORDER BY qryTransposeWithDate.DataDate)  AS qryMonthlyValues
WHERE (((qryMonthlyValues.Month) Like [Enter Month Name] & "*") AND ((qryMonthlyValues.Year) Like [Enter Year Number] & "*"))
GROUP BY qryMonthlyValues.Measures, qryMonthlyValues.Customer, qryMonthlyValues.Brewer, qryMonthlyValues.Brand, qryMonthlyValues.SubBrand, qryMonthlyValues.InnerPackage, qryMonthlyValues.ContainerVolume, qryMonthlyValues.SKU;

**Biplab1985** · 03-06-2017, 12:14 PM

Hello,

Any Input, I have to close this project tomorrow. I have used similar approach in my previous projects with DAO and querydef but never came across anything like this.Any suggestions would be much appreciated.

**xlnitwit** · 03-06-2017, 12:23 PM

The only thing that immediately comes to mind if you are using ADO rather than DAO, is that you should use % instead of * as the wildcard character. Other than that, I would need to see all the queries' definitions and- preferably- a database copy.

**Arkadi** · 03-06-2017, 12:35 PM

I believe this:

strFile = "Application.Thisworkbook.path\Database.accdb"

should be this:

strFile = Application.Thisworkbook.path & "\Database.accdb"

Ans maybe start with a very simple SELECT statement to make sure the rest is working.

**Biplab1985** · 03-06-2017, 01:34 PM

DB attached with all the query. The Dataset used is a dummy but the approach is same

**Arkadi** · 03-06-2017, 02:01 PM

Hi Biplab, give this a try:

Sub a()

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim cm As Object
Dim strCon As String
'Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''Access database

strFile = Application.ThisWorkbook.Path & "\LastMonthData_Rev3.accdb"

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set cm = CreateObject("ADODB.Command")

cn.Open strCon

'Rough idea
intMonth = Sheet1.[H2]
intYear = Sheet1.[H3]

cm.activeconnection = cn
cm.CommandText = "SELECT * " _
       & "FROM qryMonthlyValuesSingleLine"

cm.Parameters(0).Value = intMonth
cm.Parameters(1).Value = intYear

rs.Open cm

Worksheets("Sheet2").Cells(2, 1).CopyFromRecordset rs

''Tidy up
Set cm = Nothing
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

**Arkadi** · 03-07-2017, 12:25 PM

Biplab, did you have any luck solving your problem? If not, did you try the code I posted above? If it still gives errors or has problems, let us know so we can help you figure it out?

**Biplab1985** · 03-07-2017, 01:52 PM

Yes, it did. Thank you so much. The code worked just fine.

\\One question: If i need to add another parameter Say customer. The below should work right?

cm.Parameters(0).Value = intMonth
cm.Parameters(1).Value = intYear
cm.parameter(2).value = intcustomer

**Arkadi** · 03-07-2017, 02:07 PM

Yes that is correct, they should be added in the order they are defined in the query, but yes, you just increment the number.... note that you forgot the "s" in parameter(2)... should be parameters(2)

If the issue is resolved please remember to mark the thread as solved? Thanks in advance!

Hybrid View

Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Re: Provider Cannot be found

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions