Display query in datasheet and output to txt (1 Viewer)

[VBAstarter]

Hey all.

This is my first VBA application and I was assigned this task at work. I am learning as I go

Purpose of application
The user will select two dates. Start and End date. The user will hit the run button and the application will read an excel file with employee data and only display/output the records within that date range selected (employment date).

My code:

Option Compare Database
 
Function DeleteTable()
 
'Delete old records from AllEmployeesData table
On Error GoTo DeleteTable_Err
 
DoCmd.DeleteObject acTable = acDefault, "AllEmployeesData"
Exit Function
 
DeleteTable_Err:
If Err = 7874 Then
Resume Next
Else
MsgBox Error, Err
End If
 
End Function
 
Sub ReadfromExcelFile()
'Import new records into AllEmployeesData table
 
DoCmd.TransferSpreadsheet acImport, , "AllEmployeesData", 
"C:\users\chrmjfa\desktop\AllEmps_030314.xlsx", True
 
End Sub

Option Compare Database
 
Private Sub Run_Click()
'**Delete old table and call new excel file**'
 
DeleteTable
ReadfromExcelFile
 
'--Declare my variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim tdf As TableDef, fld As DAO.field
Dim strSQL As String
Dim f As field
 
Dim start_date As Date
Dim end_date As Date
start_date = Trim(Me.txtStartDate)
end_date = Trim(Me.txtEndDate)
 
Set db = CurrentDb
 
'**Change [employ] data type to Date Field Type**'
'--Create New Field
db.TableDefs.Refresh
Set tdf = db.TableDefs("AllEmployeesData")
Set fld = tdf.CreateField("MyFieldNew", dbDate)
 
'Optional: set default value
fld.DefaultValue = "0"
 
'We set ordinal position, just after old field
fld.OrdinalPosition = 9
 
'And append
tdf.Fields.Append fld
 
'Copy values from old field to a new one
db.Execute _
 
"Update AllEmployeesData Set MyFieldNew=employ", dbFailOnError
 
'Delete old field
tdf.Fields.Delete "employ"
tdf.Fields.Refresh
'Rename new field to old
tdf.Fields("MyFieldNew").Name = "employ"
tdf.Fields.Refresh
'Done!
 
Set tdf = Nothing
'--End Change [employ] data type to Date Field Type**'
 
'--Select required fields
strSQL = "SELECT [AllEmployeesData].ssn, [AllEmployeesData].last, [AllEmployeesData].mi, [AllEmployeesData].first, [AllEmployeesData].employ FROM [AllEmployeesData] WHERE [AllEmployeesData].employ BETWEEN " & start_date & " and " & end_date & ""
 
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 
'--Display query result on Datasheet and Output query to text file
With db
 Set qdf = .CreateQueryDef("NewHireQuery", strSQL)
 DoCmd.OpenQuery "NewHireQuery"
 .QueryDefs.Delete "NewHireQuery"
End With
 db.Close
 qdf.Close
 Set qdf = Nothing
 
Open "C:\users\chrmjfa\desktop\names.txt" For Output As #1
For Each f In rs.Fields
Print #1, f.Name
Next
 
Do While Not rs.EOF
Print #1, rs![ssn], rs![last], rs![mi], rs![first], rs![employ]
rs.MoveNext
Loop
 
rs.Close
Set rs = Nothing
Set db = Nothing
Close #1
 
Debug.Print "queryname = '" & strSQL & "'"
 
End Sub
 
Private Sub CmdClose_Click()
On Error GoTo Err_CmdClose_Click
    DoCmd.Close
Exit_CmdClose_Click:
    Exit Sub
Err_CmdClose_Click:
    MsgBox Err.Description
    Resume Exit_CmdClose_Click
 
End Sub

The problem
I am not getting the query displayed on the datasheet after it is done executing. It's just a blank sheet. Also the output file is just this:

ssn
last
mi
first
employ

I did a quick query test using SQL in Access and it worked fine there. I am guessing my error is somwhere in this section:

'--Display query result on Datasheet and Output query to text file
With db
Set qdf = .CreateQueryDef("NewHireQuery", strSQL)
DoCmd.OpenQuery "NewHireQuery"
.QueryDefs.Delete "NewHireQuery"
End With
db.Close
qdf.Close
Set qdf = Nothing
 
Open "C:\users\chrmjfa\desktop\names.txt" For Output As #1
For Each f In rs.Fields
Print #1, f.Name
Next
 
Do While Not rs.EOF
Print #1, rs![ssn], rs![last], rs![mi], rs![first], rs![employ]
rs.MoveNext
Loop
 
rs.Close
Set rs = Nothing
Set db = Nothing
Close #1

thanks!!

 

[JHB]

I did a quick query test using SQL in Access and it worked fine there. I am guessing my error is somwhere in this section:

'--Display query result on Datasheet and Output query to text file
With db
Set qdf = .CreateQueryDef("NewHireQuery", strSQL)
DoCmd.OpenQuery "NewHireQuery"
.QueryDefs.Delete "NewHireQuery"
End With
...

thanks!!

You create a query, open it, and then delete it again in the next code line.

 

[VBAstarter]

You create a query, open it, and then delete it again in the next code line.

I did try removing that code line. I still get the same results though. A blank datasheet and an output file with just the field names.

 

[JHB]

Okay -do you get any data if you remove the Where statement in the below line, (did you check if there is data in the AllEmployeesData table?)?

strSQL = "SELECT [AllEmployeesData].ssn, [AllEmployeesData].last, [AllEmployeesData].mi, [AllEmployeesData].first, [AllEmployeesData].employ FROM [AllEmployeesData] WHERE [AllEmployeesData].employ BETWEEN " & start_date & " and " & end_date & ""

So it would be like that:
strSQL = "SELECT [AllEmployeesData].ssn, [AllEmployeesData].last, [AllEmployeesData].mi, [AllEmployeesData].first, [AllEmployeesData].employ FROM [AllEmployeesData]

An easy way to check if the SQL string is correct is to place a Debug.Print strSQL in your code and take the output from the Immediate window and past it into a new query.

 

[VBAstarter]

Okay -do you get any data if you remove the Where statement in the below line, (did you check if there is data in the AllEmployeesData table?)?

An easy way to check if the SQL string is correct is to place a Debug.Print strSQL in your code and take the output from the Immediate window and past it into a new query.

Thanks for the assistance so far JHB.

I actually did test my data table with a "Query Test" query that looked like this

SELECT ssn, last, mi, first, employ
FROM AllEmployeesData
WHERE [employ] Between #11/1/2013# And #11/30/2013#;

The query would work fine.

I removed the "WHERE" from my VBA strSQL and now I was getting data displayed! It seems I need the "#" in the VBA query as well.

So now my strlSQL is this:

strSQL = "SELECT [AllEmployeesData].ssn, [AllEmployeesData].last, [AllEmployeesData].mi, [AllEmployeesData].first, [AllEmployeesData].employ FROM [AllEmployeesData] WHERE [AllEmployeesData].employ BETWEEN #" & start_date & "# and #" & end_date & "#"

I am however, having trouble with my TXT file display. The field names are listed as rows instead of columns. (I replaced SSN with "#s" and listed only a couple of records since it's a long list)

ssn
last
mi
first
employ
#########     STARK JR      C             RICHARD       11/18/2013 
#########    JEANTY                      DUVINSON      11/2/2013

I am guessing my output loop is incorrect somewhere. Any alternatives to using the output as, print method?

For Each f In rs.Fields
Print #1, f.Name
Next

Do While Not rs.EOF
Print #1, rs![ssn], rs![last], rs![mi], rs![first], rs![employ]
rs.MoveNext
Loop

 

[JHB]

..
I removed the "WHERE" from my VBA strSQL and now I was getting data displayed! It seems I need the "#" in the VBA query as well.

Exactly what I thought!

..
I am however, having trouble with my TXT file display. The field names are listed as rows instead of columns. (I replaced SSN with "#s" and listed only a couple of records since it's a long list)
...
I am guessing my output loop is incorrect somewhere. Any alternatives to using the output as, print method?
..

For Each f In rs.Fields
Print #1, f.Name
Next

Do While Not rs.EOF
Print #1, rs![ssn], rs![last], rs![mi], rs![first], rs![employ]
rs.MoveNext
Loop

Did you look at the DoCmd.Transfertext?
The problem is you loop for the rs.Fields (column name), if you want them horizontally you must print them all at once. But why are you using a loop, when you know exactly the amount of the columns, (because here you print Print #1, rs![ssn], rs![last], rs![mi], rs![first], rs![employ] 5 column)?