Export Access records to Excel

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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
 

Forum statistics

Threads
1,141,665
Messages
5,707,701
Members
421,524
Latest member
Bharath99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top