VBA to get data from database.

[DHolcombe]

hi,

I am trying to use an excel vba program to get data from a database and then use this data in my program. But i have no clue about where to start.

Can someone help? I know this is kinda vague but i am not sure exactly what i need to know. Not in any of the books that i have on excel vba.

 

[jfarc]

Suggestion:

Rather than try and build a routine(you've already seen how tedious & difficult this would be) to start in Excel to:

1) go out to some timeclock database; 
2) retrieve data; 
3) populate excel with this data.

As long as the 'Timeclock' database has some type of reporting functionality (I can't imagine it doesn't already), you could do the following instead:

1) take an existing report with the data you need and print to a text file;
2) import this text file into Excel.

I do this kind of stuff all the time. Depending on the programs that already control the Timeclock database, there is a likelihood that you can automate this download to a text file(even though this is a simple task, anyway), then have it open up your Excel macro and continue with your process of pasting it into Excel and doing whatever you want to do with it from there

 

[DHolcombe]

Hi,

Thanks for you post.

I thought of that but asking the operators to go out and run a report on the data base is "way over their" heads. I am writing this so that the operator does not have to input hours by hand.....which apparently was a problem.

I already have the sql script and i know it works........i just now need to know how to incorporate it into the vba code.

 

[jfarc]

...asking the operators to go out and run a report on the data base is "way over their" heads....

Wow! And I thought I had some fairly brainless folks to deal with. I guess I need to count my blessings!

How do you expect them to run your Excel macro if they can't even run a report?

Rhetorical question, no need to answer. Good luck!

(you are following the right path, though.... 'idiot-proof' every step)

 

[xenou]

When you scale up to your production database, your users will need to authenticate themselves to the server on their system. If you use Windows Authentication, then as long as they are in the users group for the database they'll get in - their dba can tell you if the users have access to the server or not. I would not recommend passwords be stored in vba plain text as its a security risk, but if they use passwords at this workplace, then at least let the user's enter it themselves (their own user login/password).

I'd put the connection string in a public constant in a standard module (called modConstants or something like that) - when you go to their system, you'll need to change the server name and the database name (catalog) - so if the connection string is in a constant you'll only need to do it in one place.

Maybe run some test of concept procedures before you've spent too many months - try it out with a simple report and see if you can successfully port from your development environment to production.

 

[xenou]

BLADE HUNTER:
I have a question about the code you sent.

sqlText = "SELECT * FROM SYSOBJECTS" '<--- Put your SQL Query in here

If your query is "SELECT Qty FROM Sales" then you just put it in:

sqlText = "SELECT * FROM SYSOBJECTS" '<--- Put your SQL Query in here
Becomes:
sqlText = "SELECT Qty FROM Sales"

 

[DHolcombe]

Hi Xenou,

Thanks for the info.

By chance you dont know what bladerunner ment about "Put sql code" here do you?

 

[DHolcombe]

Below is the sql code that someone gave me, and it works because i have ran it using Microsoft SQL server 2005 on my database.

SQL code:
DECLARE @T DATETIME
SET @T = '01/12/2009'

;WITH CTE AS
(
SELECT EmployeeID,
SUM(DATEDIFF(ss,TimeIN,TimeOut)) AS S
FROM EmployeeHours
WHERE TimeIN >= DATEADD(DAY,0, DATEDIFF(DAY,0,@T))
AND TimeIn < DATEADD(DAY,1, DATEDIFF(DAY,0,@T))

GROUP BY EmployeeID
)
SELECT
C.EmployeeID,Emp.FirstName,Emp.LastName,
CAST((s/3600) AS VARCHAR(3)) + ':' +
RIGHT('0' + CAST((s % 3600) / 60 AS VARCHAR(2)), 2) + ':' +
RIGHT('0' + CAST((s % 60) AS VARCHAR(2)), 2) AS 'Hours_worked'
FROM CTE C
JOIN EmployeeList Emp ON C.EmployeeID = Emp.EmployeeID


Several posts ago BLADERUNNER GAVE ME some code and i have tried to input the sql code per Xenou's suggestion.


Sub Macro2()
'
' Macro2 Macro
'

'


Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim sqlText As String
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
Set Data = Sheets("Results") '<--- Change this to the sheet you want the results on
Data.Select
Cells.ClearContents
Conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=asdf;PWD=asdf1234!;Initial Catalog=Timeclock;Data Source=SAUK\SQLEXPRESS"
Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdText

sqlText = "DECLARE @T DATETIME"
sqlText = "SET @T = '01/12/2009'"
sqlText = ";WITH CTE AS"
sqlText = "("
sqlText = "SELECT EmployeeID,"
sqlText = "SUM(DateDiff(ss, TimeIn, TimeOut)) As s"
sqlText = "FROM EmployeeHours"
sqlText = "WHERE TimeIN >= DATEADD(DAY,0, DATEDIFF(DAY,0,@T))"
sqlText = "AND TimeIn < DATEADD(DAY,1, DATEDIFF(DAY,0,@T))"
sqlText = "GROUP BY EmployeeID"
sqlText = ")"
sqlText = "SELECT"
sqlText = "C.EmployeeID,Emp.FirstName,Emp.LastName,"
sqlText = "CAST((s/3600) AS VARCHAR(3)) + ':' +"
sqlText = "RIGHT('0' + CAST((s % 3600) / 60 AS VARCHAR(2)), 2) + ':' +"
sqlText = "RIGHT('0' + CAST((s % 60) AS VARCHAR(2)), 2) AS 'Hours_worked'"
sqlText = "FROM CTE C"
sqlText = "JOIN EmployeeList Emp ON C.EmployeeID = Emp.EmployeeID"

Cmd.CommandText = sqlText
Set RS = Cmd.Execute
For X = 1 To RS.Fields.Count
Data.Cells(1, X) = RS.Fields(X - 1).Name
Next

If RS.RecordCount < Rows.Count Then
Data.Range("A2").CopyFromRecordset RS
Else
Do While Not RS.EOF
Row = Row + 1
For Findex = 0 To RS.Fields.Count - 1
If Row >= Rows.Count - 50 Then
Exit For
End If
Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
Next Findex
RS.MoveNext
Loop
End If
Cells.EntireColumn.AutoFit




End Sub







I get an error at this statement: Set RS = Cmd.Execute

The error i get is Incorrect syntax near the keyword 'JOIN'


Any ideas?

 

[DHolcombe]

I figured it out!!!!!!!!! Well, i found a web page that talked about exactly what i was trying to do (here is the web site: http://www.dbforums.com/microsoft-excel/1633666-sql-vba-excel.html)


Basically i did exactly what they said in the above web site and IT WORKED! Very easy and very cool. My data was stored in the "Results" page of excel. Very nice.

Below is part of my code from my previous post. I am not showing all the code again just the sqlText part which is the most important. The other part of the code seems to work fine.

sqlText = ""
sqlText = sqlText & " DECLARE @T DATETIME"
sqlText = sqlText & " SET @T = '01/12/2009'"
sqlText = sqlText & " ;WITH CTE AS"
sqlText = sqlText & " ("
sqlText = sqlText & " SELECT EmployeeID,"
sqlText = sqlText & " SUM(DateDiff(ss, TimeIn, TimeOut)) As s"
sqlText = sqlText & " FROM EmployeeHours"
sqlText = sqlText & " WHERE TimeIN >= DATEADD(DAY,0, DATEDIFF(DAY,0,@T))"
sqlText = sqlText & " AND TimeIn < DATEADD(DAY,1, DATEDIFF(DAY,0,@T))"
sqlText = sqlText & " GROUP BY EmployeeID"
sqlText = sqlText & " )"
sqlText = sqlText & " SELECT"
sqlText = sqlText & " C.EmployeeID,Emp.FirstName,Emp.LastName,"
sqlText = sqlText & " CAST((s/3600) AS VARCHAR(3)) + ':' +"
sqlText = sqlText & " RIGHT('0' + CAST((s % 3600) / 60 AS VARCHAR(2)), 2) + ':' +"
sqlText = sqlText & " RIGHT('0' + CAST((s % 60) AS VARCHAR(2)), 2) AS 'Hours_worked'"
sqlText = sqlText & " FROM CTE C"
sqlText = sqlText & " JOIN EmployeeList Emp ON C.EmployeeID = Emp.EmployeeID"




I guess i will show all the code just to make sure no one gets confused.




Sub Macro2()

Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim sqlText As String
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
Set Data = Sheets("Results") '<--- Change this to the sheet you want the results on
Data.Select
Cells.ClearContents
Conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=asdf;PWD=asdf1234!;Initial Catalog=Timeclock;Data Source=SAUK\SQLEXPRESS"
Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdText

sqlText = ""
sqlText = sqlText & " DECLARE @T DATETIME"
sqlText = sqlText & " SET @T = '01/12/2009'"
sqlText = sqlText & " ;WITH CTE AS"
sqlText = sqlText & " ("
sqlText = sqlText & " SELECT EmployeeID,"
sqlText = sqlText & " SUM(DateDiff(ss, TimeIn, TimeOut)) As s"
sqlText = sqlText & " FROM EmployeeHours"
sqlText = sqlText & " WHERE TimeIN >= DATEADD(DAY,0, DATEDIFF(DAY,0,@T))"
sqlText = sqlText & " AND TimeIn < DATEADD(DAY,1, DATEDIFF(DAY,0,@T))"
sqlText = sqlText & " GROUP BY EmployeeID"
sqlText = sqlText & " )"
sqlText = sqlText & " SELECT"
sqlText = sqlText & " C.EmployeeID,Emp.FirstName,Emp.LastName,"
sqlText = sqlText & " CAST((s/3600) AS VARCHAR(3)) + ':' +"
sqlText = sqlText & " RIGHT('0' + CAST((s % 3600) / 60 AS VARCHAR(2)), 2) + ':' +"
sqlText = sqlText & " RIGHT('0' + CAST((s % 60) AS VARCHAR(2)), 2) AS 'Hours_worked'"
sqlText = sqlText & " FROM CTE C"
sqlText = sqlText & " JOIN EmployeeList Emp ON C.EmployeeID = Emp.EmployeeID"

Cmd.CommandText = sqlText
Set RS = Cmd.Execute
For X = 1 To RS.Fields.Count
Data.Cells(1, X) = RS.Fields(X - 1).Name
Next

If RS.RecordCount < Rows.Count Then
Data.Range("A2").CopyFromRecordset RS
Else
Do While Not RS.EOF
Row = Row + 1
For Findex = 0 To RS.Fields.Count - 1
If Row >= Rows.Count - 50 Then
Exit For
End If
Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
Next Findex
RS.MoveNext
Loop
End If
Cells.EntireColumn.AutoFit
End Sub

 

[obiron]

Congratulations on getting it to work and welcome to the world of database development.

Do you understand what you have done in building the sqltext string and then executing it and returning the results to Excel.

I am sure you can imagiane the major overhead of maintaining this sort of code in Excel. I would suggest that you try to get the sqlText stored in the database as a view and then simply write sqlText to "select * from myView" This moves support of the logic to the database where it is more useful because it can be re-used.

SQL2005 should have come with SSRS which is a tool for building/runing on-line reports and you should be looking at using this to create reports rather than Excel - it always has the option to export to Excel. If you need to do further manipulation of the data, I would always suggest that this is done as close to the data source as possible. I.e. in the SQL server view as this means you only ever have piece of code to maintain and can guarantee consistent results.

 

[DHolcombe]

Hi Obiron,

I am definitely not a database developer by any means. I hardly consider myself a good programmer........but i can get it done. But thanks.

From what i remember you saying........it sounds like you suggest i do as much as possible at the database rather than in excel. I would agree but i am not sure this is something i can do. I could be wrong but here is my thinking.

There is an excel vba program that my friends company uses to track information. The supervisors have to input employee times and other information. The supervisors are NOT familiar with excel that much and they are definitely not familiar with databases.

So i was asked to query the database using excel so that the times for the employees would automatically be filled in their existing excel vba program. Since the supervisors cant be expected to extract data from the database the only alternative is to put it into excel........automatically so the supervisors dont have to do anything but press a button.

If what i said does not convince you then let me know..........perhaps there is still more i can learn about what i am doing. But i must say thanks to all who helped me here .......its been frustrating and fun all at the same time.