Need VBA help with reading query/writing table

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

Can somebody give me basic VBA code to open a query, read
the data, build another table based on the data read and
close everything.
I also need to get the table I build output into an Excel
spreadsheet.

My boss sprang this Access job on me, my SQL and VBA books
are not here and I have not worked on Access for 18 months.
 
Check Access Help on the Make-Table Query.

For output to Excel, you can create a Select Query and then export the Query
to Excel or you can simply open the DatasheetView of the Query and use
"Analyze with Excel" command.
 
Rebecca,

You don't actually need to open and close anything!
If you really need the intermediate table you require two
steps as follows:

docmd.runSQL ("SELECT * INTO [Target Table] FROM
[Source Query] ORDER BY [Field x], [Field y]")
DoCmd.OutputTo acTable, "Target Table", "MicrosoftExcel
(*.xls)", "Path\FileName.xls", False, ""

The square brackets in the SQL statement are only required
if yout table/query/field names contain spaces, otherwise
you can omit them. Note that every time you run it the
Target Table will be overwritte, so you don't have to
worry about deleting it first.

Yet, if you don't really need the table, you can export
directly from the query:

DoCmd.OutputTo acQuery, "Source Query", "MicrosoftExcel
(*.xls)", "Path\FileName.xls", False, ""

I hope this helps.

Nikos Y.
 
Hello,

If example below will be not enough tell me more what You want to do.
You don't need to export data to another table (build on query). You can
export data direct from query to Excel file .

Sub Exp2Excel()
Dim zap As String, qryName As String
Dim retVal As Long

On Error GoTo Err_Exp2Excel
qryName = "tmp"
zap = "SELECT *" & _
" FROM Table1" & _
" WHERE ((Field2) Like 'Adam');"

retVal = ChangeQuery(qryName, zap) 'create or change query
'output query to Excel file, program promt user to define name of Excel file
If retVal Then DoCmd.OutputTo acOutputQuery, qryName, "Microsoft Excel
(*.xls)", "", True, ""

Exit Sub

'error handler
Err_Exp2Excel:
MsgBox Err.Description, vbExclamation, Err.Number

End Sub

Function ChangeQuery(strQueryName As String, strSQL As String) As Boolean
Dim dbs As Database
Dim qwdf As QueryDef

ChangeQuery = True

Set dbs = CurrentDb
On Error Resume Next
Set qwdf = dbs.QueryDefs(strQueryName) 'exists in collection?
If qwdf Is Nothing Then
Set qwdf = dbs.CreateQueryDef(strQueryName, strSQL) 'create new
Exit Function
End If
On Error GoTo Err_ChangeQuery
With qwdf
.SQL = strSQL
.Close
End With
Set qwdf = Nothing
Set dbs = Nothing
Exit Function

Err_ChangeQuery:
ChangeQuery = False
Set qwdf = Nothing
Set dbs = Nothing
End Function

Sorry for my language, I'm still learning
losmac
 
Back
Top