All,
In my spreadsheet, I have a command button that calls code to create a
recordset based on a SELECT statement which queries a table in Access.

I have tried this in Excel 2007 and Excel 2010 and get the same problem.

I have a reference to DAO 3.6 in my project. The following code snippet
fails with a message box with a Red X in the middle, with NO TEXT describing
the error, and an OK and Help button. The Help button gives me nothing.

Can somone point me in the right direction on how to query Access for some
data without importing the entire table (it is very large).

Thanks,
Steve

I forgot to inlude my code snippet:

Dim db as DAO.Database

Set db = OpenDatabase("C:\Test.accdb")



"Steve Haack" wrote:

All,
In my spreadsheet, I have a command button that calls code to create a
recordset based on a SELECT statement which queries a table in Access.

I have tried this in Excel 2007 and Excel 2010 and get the same problem.

I have a reference to DAO 3.6 in my project. The following code snippet
fails with a message box with a Red X in the middle, with NO TEXT describing
the error, and an OK and Help button. The Help button gives me nothing.

Can somone point me in the right direction on how to query Access for some
data without importing the entire table (it is very large).

Thanks,
Steve

Consider using ADO rather then DAO as it is a more recent technology.

VBE--tools--References...--Active X Data object Library 2.7

then somthing like,

Const ConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\%UserName%
\Desktop\New_Dev.mdb;Persist Security Info=False"


Sub QueryMSAccess()
Dim Recordset As ADODB.Recordset
Dim SQL As String

SQL = "SELECT * FROM yourTable"
Set Recordset = New ADODB.Recordset

Recordset.Open SQL, ConnectionString, adOpenKeyset, adLockReadOnly,
adCmdText

If Not Recordset.RecordCount = 0 Then

Call Worksheets(1).Range("A1").CopyFromRecordset(Record set)
End If
End Sub


This will dump the contents of the table into the worksheet. HTH.

"Steve Haack" wrote:

I forgot to inlude my code snippet:

Dim db as DAO.Database

Set db = OpenDatabase("C:\Test.accdb")



"Steve Haack" wrote:

All,
In my spreadsheet, I have a command button that calls code to create a
recordset based on a SELECT statement which queries a table in Access.

I have tried this in Excel 2007 and Excel 2010 and get the same problem.

I have a reference to DAO 3.6 in my project. The following code snippet
fails with a message box with a Red X in the middle, with NO TEXT describing
the error, and an OK and Help button. The Help button gives me nothing.

Can somone point me in the right direction on how to query Access for some
data without importing the entire table (it is very large).

Thanks,
Steve

Guess i should read entire post ...

Mostly the same but instead of .CopyFromRecordset use somthing like

If Not Recordset.RecordCount = 0 Then
For I = 0 To Recordset.RecordCount - 1
With Worksheets("Sheet1")
.Cells(I + 1, 1).Value = Recordset.Fields(0)
.Cells(I + 1, 2).Value = Recordset.Fields(1)
.Cells(I + 1, 3).Value = Recordset.Fields(2)
.Cells(I + 1, 4).Value = Recordset.Fields(3)
End With
Recordset.MoveNext
Next


End If



"Steve Haack" wrote:

I forgot to inlude my code snippet:

Dim db as DAO.Database

Set db = OpenDatabase("C:\Test.accdb")



"Steve Haack" wrote:

All,
In my spreadsheet, I have a command button that calls code to create a
recordset based on a SELECT statement which queries a table in Access.

I have tried this in Excel 2007 and Excel 2010 and get the same problem.

I have a reference to DAO 3.6 in my project. The following code snippet
fails with a message box with a Red X in the middle, with NO TEXT describing
the error, and an OK and Help button. The Help button gives me nothing.

Can somone point me in the right direction on how to query Access for some
data without importing the entire table (it is very large).

Thanks,
Steve