Export Access records to Excel

[Kurt]

Hello Everyone,

I have the following code:

Sub get_applications() 
    Dim conn As New Connection 
    Dim rec As New Recordset 
    Dim comm As New Command 
    Dim ws As Worksheet 
    Dim Month$ 
    Set ws = ThisWorkbook.Worksheets("command") 
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _ 
        "Data Source=" + ThisWorkbook.Path + "\From Dyer.mdb;" 
    Set comm.ActiveConnection = conn 
    comm.CommandText = _ 
        "SELECT Apps FROM track apps Where Month = June" 
    comm.Parameters(0) = "" 
    rec.Open comm 
    ws.[a1].CopyFromRecordset rec 
    rec.Close: conn.Close 
End Sub 
[code]

My code keeps stopping on teh comm.Parameters(0) = "" line.

What am I doing wrong?

I have posted this new snippet of code also in the Excel section.  I need some quick answers here though, so I thought someone using Access everyday would know it quicker.

TIA

Have a great day!

Kurt

 

[SydneyGeek]

Hi Kurt,

Just a quick scan as this point, but try changing the SQL to this:

"SELECT Apps FROM track apps Where Month = ""June""""

You declared Month as a String -- you will need to feed a String back to the criteria.

Also: Are you using ADO or DAO? (Looks like ADO). Although not absolutely required, it's a good idea explicitly declare that -- for example,
Set conn = ADODB.Connection

I'm assuming that you set a reference to the ADO 2.7 (or higher) library...

One more question -- do you always want June, or will that be better as a text parameter that you can pass to the query?

Denis

 

[MyBoo]

Put your table in brackets []
You have a space in the table name - "SELECT Apps FROM track apps Where Month = June" - spaces in table/field names are not nice.

"SELECT Apps FROM [track apps] WHERE Month = June"

I'm gonna disagree with SydneyGeek - I think Month is dim'd as a variant and should be fine as is.

Do you know how to debug your code? I would step through the code, trying to identify what is not defined correctly.

 

[SydneyGeek]

MyBoo,

The reason for saying that Kurt had DIM'ed Month as a String was this:

! = Single Precision
% = Integer
& = Long Integer
@ = Currency
# = Double Precision
$ = String

Standard VB type declarations -- it took a while to track down the list, but when I saw Month$ something triggered in the deep dark recesses of my memory...

Denis