SQL Query Question

[Solomon911]

This question is a little off topic but the users on this form have helped me out a lot with my VBA so I was hopeful someone could answer an SQL question.
My company uses a SQL server to log all of the events that happen on a piece of equipment every day. Each day the server creates a new table and with every event a new record is added. Needless to say there are a lot of tables. The query we use to view the events must be updated every day. Is there any way to change the “from dbo.Data_1Jan2015” to something that would equate to newest table.

use DOGHistory select distinct * from dbo.Data_1Jan2015 where type = 'Digital' order by edit_date

 

[Joe4]

That is very bad design. The database is not normalized, which is why it is so hard to work with. You should not have multiple tables with the exact same structure. It should write all the logs entries to the same table every day, and just have a date field to indicate the different dates. I am guessing though, that you probably don't have much control over the design. But maybe you might want to suggest to the powers that be that they might want to look at changing that down the road.

Back to your original question, how is the SQL code for the query being created? If it is being created using VBA, it can be programmed to use a dynamic variable for the table name.

 

[Solomon911]

You are correct I did not create the database. It is a GE design. Due to the criticality of the equipment it would take a joint act of the president and congress for my company to allow me to make changes to it (Not literally). The short code that I wrote is in a .sql file on the desktop. The users have to open the, change the date portion of the table name and click execute. However that has proven a bit difficult for some of them. They tend to not understand that the date must retain the same format even with a note at the top of the code explaining it. It is not something we have to do every day just a couple times a month and it is not the same person doing it every time.
I have considered creating an excel document with a simple button they could push but they will frown upon me running anything they consider a program that was not originally installed on the computer from GE.

 

[Joe4]

So, how do your users actually run this SQL code?
You could have Excel dynamically build the SQL code that they need (you may even be able to do it without the use of VBA).

 

[Solomon911]

There is a document on the desktop of the SQL server named ListTodaysEvents.sql that the user clicks on. The document opens in SQL Management Studio. The user must then click connect, change the date portion of the table name and then click the execute button.
I am trying to keep it as simple as possible. I have created an Excel document that when opened; hides Excel, Creates the ListTodaysEvents.sql file and then opens the file. Now all the user has to do is click connect and execute.
If there is an easier way I am still interested.

Using VBA;
Option Explicit
Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Sub Autpen()
'This sub runs when the workbook is opened
Application.Visible = False
SQLBuilder
ThisWorkbook.Saved = True
Application.Quit

End Sub
Public Function FileExists(Path As String) As Boolean
'Test if file exist

If Dir(Path, vbHidden) <> "" Then FileExists = True Else FileExists = False

End Function
Function OpenFile(FileToOpen As String)
'Opens any file using the default program
Call ShellExecute(0, "Open", FileToOpen & vbNullString, vbNullString, vbNullString, 1)

End Function
Sub SQLBuilder()
'Make the .sql query that will list all events for the day.

Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Dim str1 As String

str1 = ThisWorkbook.Path & "\TodaysEvents.sql"
If FileExists(str1) Then SetAttr str1, vbNormal
Set oFile = FSO.CreateTextFile(str1)
oFile.writeline "/* NOTE: Make no changes to this query and click the red “!” (Execute) above this window. The */"
oFile.writeline "/* window below will list all events for today. It may take up to 30 seconds to load results. */"
'oFile.writeline "/*_________________________________________________________________________________________________*/"
oFile.writeline "use DOGHistory select distinct * from dbo.Data_" & Format(Date, "ddMmmyyyy") & " where type = 'Digital' order by edit_date"
oFile.Close
SetAttr str1, vbHidden
OpenFile str1

99 'Clean up
str1 = ""
Set FSO = Nothing
Set oFile = Nothing

End Sub