By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 614 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

VBA SQL Error - No value given for one or more parameters

P: 7
Hi,

I am new to bytes and I was hoping you could please help me with the Error - No value given for one or more parameters that occurs on the .Open MySql line of code. I m trying to extract data from a query from an access database and output this to Excel. I think there must be something wrong with the string MySql which I have used (I took most of it from the auto-generated sql code in the access query and changed the date between area to use the DateSerial fuction). I may have missed out that I need to code something else with the code...

Any help would be greatly appreciated. thank you
Alex


Expand|Select|Wrap|Line Numbers
  1. Private Function CallHandling(ByVal x As String)
  2.     '************************************** CALL HANDLING ******************************
  3.     Dim cn As Object, rs As Object, Status As String
  4.     Dim MySql As String, dbfullname As String, myCnt As Long
  5.  
  6.     dbfullname = "H:\Operations Planning\MIU\Internal\STATS\TELE\New_Call2000.mdb"
  7.     Status = "22"
  8.  
  9.     '*******Q_SAGA HER BY DAY(B2B)*******
  10.     'Stack your SQL string
  11.  
  12.     ' SQL FOR Last 7 days DATES!
  13.  
  14.     MySql = "SELECT [SumOfCalls answered]+[SumOfAbandoned] AS Offered, Sum(T_Rockwell_Application_Data.[Calls answered]) AS [SumOfCalls answered]," _
  15.     & "IIf(Sum(T_Rockwell_Application_data.[calls offered])=0,0,Sum(T_Rockwell_Application_data.[calls offered]-([calls answered]-" _
  16.     & "T_Rockwell_Application_data.[calls answered in 20 secs])-(T_Rockwell_Application_data.abandoned-T_Rockwell_Application_data.[abandoned" _
  17.     & "in 20 secs]))/Sum(T_Rockwell_Application_data.[calls offered])) AS [Svc Level], ([SumOfTotal Talk Time]+[SumOfTotal Work Time])/" _
  18.     & "[SumOfCalls answered] AS AHT, ([SumOfAbandoned]-[SumOfAbandoned in 20 Secs]) AS [Aban calls after 20 secs], Sum(T_Rockwell_Application_" _
  19.     & "Data.Abandoned) AS SumOfAbandoned, Sum(T_Rockwell_Application_Data.[Abandoned in 20 Secs]) AS [SumOfAbandoned in 20 Secs], " _
  20.     & "Sum(T_Rockwell_Application_Data.[Total Talk Time]) AS [SumOfTotal Talk Time], Sum(T_Rockwell_Application_Data.[Total Work Time]) " _
  21.     & "AS [SumOfTotal Work Time], Max(T_Rockwell_Application_Data.Date) AS MaxOfDate, Sum([Abandoned]-[abandoned in 20 secs])/[Offered] " _
  22.     & "AS [20 Sec Abd%]FROM T_Rockwell_Application_Data INNER JOIN [T_HOME ASSIST] ON (T_Rockwell_Application_Data.Application = " _
  23.     & "[T_HOME ASSIST].[Application No]) AND (T_Rockwell_Application_Data.[Application Name] = [T_HOME ASSIST].Name) AND " _
  24.     & "(T_Rockwell_Application_Data.Site = [T_HOME ASSIST].Site)WHERE ((([T_HOME ASSIST].[Sub Category])<>'Home assist engineer') AND " _
  25.     & "((T_Rockwell_Application_Data.Date) Between DateSerial(Year(Date()-7),Month(Date()-7),Day(Date()-7)) And DateSerial(Year(Date()-1),Month(Date()-1),Day(Date()-1))) " _
  26.     & "AND ((T_Rockwell_Application_Data.[Application Group])='HO' Or " _
  27.     & "(T_Rockwell_Application_Data.[Application Group]) Like 'U*') AND (([T_HOME ASSIST].[Application No])<>954 And " _
  28.     & "([T_HOME ASSIST].[Application No])<>57));"
  29.  
  30.     'Clear SQL variable string
  31.     Status = Empty
  32.     Set cn = CreateObject("ADODB.Connection")
  33.  
  34.     'Create DB connection
  35.     cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbfullname & ";"
  36.     Set rs = CreateObject("ADODB.Recordset")
  37.     With rs
  38.         Set .ActiveConnection = cn
  39.         'Pass your SQL
  40.         .Source = MySql
  41.         .Open MySql, , 3, 1 - ERROR OCCURS HERE!
  42.  
  43.         myCnt = .RecordCount
  44.         If myCnt > 0 Then
  45.             .MoveLast: .MoveFirst
  46.              'Pull data to first sheet, cells a1:RecordestCountRow & column 3 3 fields in the sql pass
  47.             Windows(mstrHESWB).Activate
  48.             Sheets("CH input").Activate
  49.             Range(Cells(1, 3), Cells(myCnt + 1, 6)).CopyFromRecordset rs
  50.         End If
  51.         .Close
  52.     End With
  53.     cn.Close
  54.     Set rs = Nothing: Set cn = Nothing
  55.  
  56.  
  57. End Function
PS. I think it compiles correctly.
Jun 29 '12 #1
Share this Question
Share on Google+
13 Replies


P: 7
Hi, I have now figured out the error! However it is not posting any data to the excel sheet in the line:

Expand|Select|Wrap|Line Numbers
  1. Range(Cells(1, 3), Cells(myCnt + 1, 6)).CopyFromRecordset rs
I have modified this to be:

Expand|Select|Wrap|Line Numbers
  1. Range(Cells(3, 1), Cells(myCnt + 3, 10)).CopyFromRecordset rs
but it still doesn't output anything.

Thank you
Alex
Jun 29 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
In your VBE window, use the menu to click Debug->Compile. If the Compile is grayed out, it is because the project is allready compiled.

Its a big bit of code to try to work sense out of. If I were you I would start by adding this to line 29:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print mySql
Then Copy paste that output from the immediate window, and paste it into a empty query, and try to run it. That should tell you if the SQL is valid or not, and whether that is whats the root cause of your problem.
Jun 29 '12 #3

P: 7
Hi thank you - I found the problem with the MySql string.

However it is not outputting the info corectly... Nothing happens when I call the command:
Expand|Select|Wrap|Line Numbers
  1. Range(Cells(3, 1), Cells(myCnt + 3, 10)).CopyFromRecordset rs
However I would hope it would output the data to the spreadsheet.
Jun 29 '12 #4

NeoPa
Expert Mod 15k+
P: 31,419
Please check out Before Posting (VBA or SQL) Code. One of the more important points has already been raised by Smiley here, but you didn't seem to notice it. I suggest you read the attached set of points carefully, as it will probably effect how likely people are to want to put effort into helping you.
Jun 29 '12 #5

P: 7
Hi,

I did read those points before posting. I forgot to mention in the intial quote that it compiled correctly but I did it as the next post (as a p.s). Apologies for missing it originally, I hope this didn't cause too much confusion.

I still have the error when trying to output the data.
Thank you
Jun 29 '12 #6

NeoPa
Expert Mod 15k+
P: 31,419
OK. I hear you, and I can't fault your attitude.

However. It appears we are going to have to look at the resultant SQL if we're to be of any help. The linked article indicates that in such situations it makes best sense to get that SQL, not the code that produces it but the SQL itself, format it legibly, and post it here for us to look at. Does that make more sense?
Jun 29 '12 #7

P: 7
Hi NeoPa,

Thanks for that. I hope this is what you mean. The actual access sql code is below. However, I have changed the Dates so that it doesn't read from a form but instead reads from the DateSerial command as above because I need the same specific dates always run (i.e. last 7 days). Hope this helps.

Thank you
Expand|Select|Wrap|Line Numbers
  1. SELECT [SumOfCalls answered]+[SumOfAbandoned] AS Offered
  2.      , Sum(T_Rockwell_Application_Data.[Calls answered]) AS [SumOfCalls answered]
  3.      , IIf(Sum(T_Rockwell_Application_data.[calls offered])=0,
  4.            0,
  5.            Sum(T_Rockwell_Application_data.[calls offered]-
  6.            ([calls answered]-T_Rockwell_Application_data.[calls answered in 20 secs])-
  7.            (T_Rockwell_Application_data.abandoned-T_Rockwell_Application_data.[abandoned in 20 secs]))/
  8.            Sum(T_Rockwell_Application_data.[calls offered])) AS [Svc Level]
  9.      , ([SumOfTotal Talk Time]+[SumOfTotal Work Time])/[SumOfCalls answered] AS AHT
  10.      , ([SumOfAbandoned]-[SumOfAbandoned in 20 Secs]) AS [Aban calls after 20 secs]
  11.      , Sum(T_Rockwell_Application_Data.Abandoned) AS SumOfAbandoned
  12.      , Sum(T_Rockwell_Application_Data.[Abandoned in 20 Secs]) AS [SumOfAbandoned in 20 Secs]
  13.      , Sum(T_Rockwell_Application_Data.[Total Talk Time]) AS [SumOfTotal Talk Time]
  14.      , Sum(T_Rockwell_Application_Data.[Total Work Time]) AS [SumOfTotal Work Time]
  15.      , Max(T_Rockwell_Application_Data.Date) AS MaxOfDate
  16.      , Sum([Abandoned]-[abandoned in 20 secs])/[Offered] AS [20 Sec Abd%]
  17.  
  18. FROM   T_Rockwell_Application_Data
  19.        INNER JOIN
  20.        [T_HOME ASSIST]
  21.   ON   (T_Rockwell_Application_Data.Site=[T_HOME ASSIST].Site)
  22.  AND   (T_Rockwell_Application_Data.[Application Name]=[T_HOME ASSIST].Name)
  23.  AND   (T_Rockwell_Application_Data.Application=[T_HOME ASSIST].[Application No])
  24.  
  25. WHERE  (([T_HOME ASSIST].[Sub Category]<>'Home assist engineer')
  26.   AND  (T_Rockwell_Application_Data.Date Between Forms!F_CallHandling_Daily!MonthStart And Forms!F_CallHandling_Daily!Current)
  27.   AND  (T_Rockwell_Application_Data.[Application Group]="HO" Or T_Rockwell_Application_Data.[Application Group] Like "U*")
  28.   AND  ([T_HOME ASSIST].[Application No]<>954
  29.   AND  ([T_HOME ASSIST].[Application No]<>57))
Jun 29 '12 #8

NeoPa
Expert Mod 15k+
P: 31,419
Halex UK:
However, I have changed the Dates so that it doesn't read from a form but instead reads from the DateSerial command as above because I need the same specific dates always run (i.e. last 7 days).
If this means that your report of the problem was run with SQL different from this then that makes it all but useless for looking at. As it would make no sense to do that though, I have really very little idea what it is you're saying here. I think we need this clarified before attempting to proceed though. We don't want to spend days on something that isn't even the problem you're experiencing.
Jun 29 '12 #9

Expert Mod 2.5K+
P: 2,545
In your earlier posts you point out that the CopyFromRecordset method is not having any effect. I note that the line of code you quote does not refer to Excel through an explicit reference to an Excel application object, an approach which will fail when using Excel as an automation server from Access:

Expand|Select|Wrap|Line Numbers
  1. Range(Cells(3, 1), Cells(myCnt + 3, 10)).CopyFromRecordset rs
You must explicitly refer to the Excel instance you have defined and opened - which I cannot see anywhere in your code so far.

I would expect to see something like this elsewhere in your code:

Expand|Select|Wrap|Line Numbers
  1. Dim oExcel as Excel.Application
  2. Set oExcel = New Excel.Application
  3. oExcel.Workbooks.Open <PathOfYourWorkBook>
If you have something like this in your code then you can use the CopyFromRecordSet method like this:

Expand|Select|Wrap|Line Numbers
  1. oExcel.ActiveSheet.Range(Cells(3, 1), Cells(myCnt + 3, 10)).CopyFromRecordset rs
When working in Excel itself it is possible to leave such references out, as the Application object is implied by the code being run within Excel. When running from Access (or anyother non-Excel application) you must explictly refer to the automation server concerned - it cannot be left implied.

This is true of all calls to methods which in one way or another need to be qualified with the application object.

Note the use of the ActiveSheet method in my reference to the Range above; if it is the active worksheet of the Excel workbook you want to use then you must explicitly refer to it in your code when running in another environment like this.

-Stewart
Jun 30 '12 #10

P: 7
Hi,

Thank you for the replies.

Neopa - The sql which I have copied into the forum is exactly what I have used. I have just said that I have modified the Access Sql for date to arrive at the code. (substituting the use of a form for a predefined date range).

Stewart - I have attempted using your suggestions The workbook I want to use is the same as the one which has the macro vba code written to it. Will this affect things? It gives me a 1004 runtime error when executing
Expand|Select|Wrap|Line Numbers
  1. oExcel.ActiveSheet.Range(Cells(3, 1), Cells(myCnt + 3, 10)).CopyFromRecordset rs 
Do you have any suggestions of why this is? I have just used the path of the currently open workbook so not sure if there is a better way of doing this.

I have never used excel to communicate with an access database and so am very new to this so apolgiese if I am not being very helpful.

Thank you
Alex
Jul 2 '12 #11

NeoPa
Expert Mod 15k+
P: 31,419
Alex, I'm thoroughly confused as to why you are even talking about modified SQL (and have very little idea what you're trying to get across even). I would expect one set of SQL and a single description of the problems. I see no reason to include any reference to any other SQL, which makes me wonder what it is that you're talking about. If it's part of the problem, then it really shouldn't be. If it isn't, then it's as relevant to the discussion as my aunt's cat.

I can't work with you if I don't understand what you're trying to say. Does that make sense?
Jul 2 '12 #12

Expert Mod 2.5K+
P: 2,545
I confess to having been confused too. What I wrote applies to using Access as the primary source, linking to Excel as an automation server. You are clearly using Excel itself as the source, opening a connection to an Access DB which I had not seen whan I skimmed the code concerned.

My post therefore does not add anything to the discussion, and can be removed accordingly.

-Stewart
Jul 4 '12 #13

P: 7
Thanks for your help. I now have managed to reslove the issue now.
Thanks again
Jul 4 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.