OpenDatabase Error

  • Thread starter Thread starter Steve Haack
  • Start date Start date
S

Steve Haack

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(Recordset)
End If
End Sub


This will dump the contents of the table into the worksheet. HTH.
 
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
 
Back
Top