1

Topic: sql vba excel stock-taking of given by date

, I ask the help for I can not invent the code for the following situation:
There is a DB which time is a day filled with the data from registration instruments. To each date there corresponds the certain data. By means of a macro these indications get to a certain column  so that they corresponded to date in an adjacent column.
If in a DB there is data for all days of month it is filled  correctly And if, for example, for 15 there is no data, and is requested with 1 on 20 instead of cell opposite to 15th to leave empty, it is filled for 16, 16 for 17 etc.
In it all and business: how to make, that at the missing data in a DB in appropriate cells  too it was empty, or 0, or not the DATA...
My script of request:
[spoiler]

' the Steam conduit 2. Mass'
Sub LoadData_id10 ()
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cn = New ADODB.Connection
Set rst = New ADODB.Recordset
cn. ConnectionString = "Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=PowerPlant; Data Source=ROCON" ' Parameters of a line of connection
cn. Open
rst. Open "SELECT VALUE FROM SPNetArchive Where (RequestID=10) AND (DateTime BETWEEN ' 09-01-2017 0:00:00 AM 'AND ' 09-30-2017 0:00:00 AM ') Order By DateTime", cn ' a SQL query, connection, obtaining from column VALUE, on ID and on date-time
ActiveSheet. Range ("B72").CopyFromRecordset rst ' It is derived the data on sheet
rst. Close
cn. Close
Set rst = Nothing
Set cn = Nothing
End Sub

the Moderator: we Learn to use tags of design of the code - FAQ
[/spoiler]

2

Re: sql vba excel stock-taking of given by date

I so understood, what on sheet Excel, in a certain column dates statically are put down? Instead of tried to deduce through request and date and the data?

3

Re: sql vba excel stock-taking of given by date

Yes not, that you offer, partially solves, of course, a question, but to the main requirement does not satisfy - the interface not to touch - only a macro . The requirement of the chief of department. Now here achieved, that RecordCount correctly showed the content , well and everything, yet I do not know further that with it to do. Experience in programming is not present, now here on an old age of years it is necessary to investigate

4

Re: sql vba excel stock-taking of given by date

On a SQL server rarely enough there is a shortage of the data, but during such moments it would be desirable, that the program correctly fulfilled. Eventually - a question of professional ambition.

5

Re: sql vba excel stock-taking of given by date

kovax, it is necessary to you  request so that it always returned all the days long
I do not know what version at you whining, but it is possible approximately so (someone and corrects at desire)

SELECT
VALUE
FROM (
select
dateadd (dd, d.dt, ' 20170901 0:00:00 AM ') dt
from (values
(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);
(21), (22), (23), (24), (25), (26), (27), (28), (29), (30)
) d (dt)
where
dateadd (dd, d.dt, ' 20170901 0:00:00 AM ') BETWEEN ' 20170901 0:00:00 AM 'AND ' 20170930 0:00:00 AM ') x (dt)
left join (select * from SPNetArchive where RequestID=10) s on datetime = x.dt
Order By x.dt

1. It is bad to name fields reserved keywords,
2. It is better to write date in canonical form YYYMMDD HH:MM:SS
3. In request it is better to replace dates in a condition with parameters

6

Re: sql vba excel stock-taking of given by date

Long, I feel, I will "digest" your code, HandKot. Anyway, at present the debugger swears for syntax, I can not understand yet, what business. Cheekbones at me 2008R2, he "eats" a format of date such - mm-dd-yyyy, is checked up.

7

Re: sql vba excel stock-taking of given by date

HandKot, instead of you could give an example the code for number designation from a cell, for example 1, sheet "List1" in a SQL server, table SPNetArchive, column RequestID and to appropriate to this value, for example RequestID=100. A line of connection the same that in the first question.

8

Re: sql vba excel stock-taking of given by date

kovax wrote:

a code sample for number designation from a cell, for example 1, sheet "List1" in a SQL server, table SPNetArchive, column RequestID and to appropriate to this value, for example RequestID=100. A line of connection the same that in the first question.

If correctly understood, write idle time UPDATE
Approximately so (did not check)
Dim cmd As New ADODB.Command
Dim cn As New ADODB.Connection
cn. ConnectionString = ""
cn. Open
set cmd. Connection = cn
cmd. Execute "update SPNetArchive set RequestID =" and ThisWorkbiik. Worksheets ("List1"), Range ("A1").Value and "where here a condition for a specific line"
.

kovax wrote:

Anyway, at present the debugger swears for syntax, I can not understand yet, what business

What for an error? The specified request on 2008 should work

9

Re: sql vba excel stock-taking of given by date

The error jumps out the following: "It is impossible to use connection for operation performance. It is closed or not admitted in the given context". I then added in the end of your line cn, the error after that changed on "Incorrect syntax near nthe keyword ' values'"

10

Re: sql vba excel stock-taking of given by date

kovax, we now about what speak?
Concerning request about sampling. It at you in that type which I wrote, through Management Studio works?

11

Re: sql vba excel stock-taking of given by date

HandKot, the screen put as argument.

12

Re: sql vba excel stock-taking of given by date

kovax;
It is possible to deduce 2 columns

rst. Open "SELECT DateTime, VALUE FROM SPNetArchive...

to interpose missing records, being guided on date-time, to delete a column with dates, approximately so

Dim i&, d As Date
For i = Cells (Rows. Count, "B").End (xlUp).Row To 73 Step-1
d = Int (Cells (i, "B"))
While d - Int (Cells (i - 1, "B"))> 1
Cells (i, "B").Resize (2).Insert xlShiftDown
d = d - 1
Cells (i, "B") = d ' it is optional
Cells (i, "C") = "IS NOT PRESENT THE DATA"
Wend
Next
Range ("B72", Cells (Rows. Count, "B").End (xlUp)).Delete xlShiftToLeft

13

Re: sql vba excel stock-taking of given by date

kovax wrote:

HandKot, the screen put as argument.

The MS Query certainly does not understand the given syntax. It remained, I think, at level SQL 7
The request should be interposed instead of yours in line

rst. Open "SELECT VALUE

14

Re: sql vba excel stock-taking of given by date

Likely, now nonsense I will ask...
Kazan, couples I will not think - and where to interpose CopyFromRecordset rst?

15

Re: sql vba excel stock-taking of given by date

kovax;
Leave it on a place. My code interpose before End Sub, correct a line of request.
To be convinced of correctness of operation, transit the code by steps - F8.
It is required, that the column With was initially free, differently the request jams it. If it is impossible, it is necessary before CopyFromRecordset to interpose a column, or generally to preempt  on new sheet and there to process.

16

Re: sql vba excel stock-taking of given by date

Kazan, made, as you told. I spread result of performance.
In a DB there is no data for November, 1st and 9. For 11/9/2017 all is true - there is no data, and for 11/1/2017 did not fulfill algorithm.
For 10 data "moved down" for some reason in a column with dates

17

Re: sql vba excel stock-taking of given by date

And if once again to push the button...

18

Re: sql vba excel stock-taking of given by date

kovax;
Lay out sheet after an insertion of two columns (file Excel, not a picture!). Even for the correct operation of algorithm the initial and finite dates used in request are necessary - these dates whence undertake?

19

Re: sql vba excel stock-taking of given by date

Dates in a line of request to a DB.