Export Access records to Excel

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,648
Hello Everyone,

I have the following code:

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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Board Regular
Joined
Aug 9, 2006
Messages
217
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,038
Messages
5,545,672
Members
410,697
Latest member
srishtijain0708
Top