Have you referenced your project to the Microsoft ActiveX Data Objects 2.X
Library? Tools - Referneces -...
--
HTH...

Jim Thomlinson


"spardey" wrote:

Hi Folks,

I am another newbie to Excel VBA programming. Please bear with me!!

I am trying to bring into Excel some Microsoft Access data via an ADO
connection.

The code in the VBA module looks like this:

====================================

Sub GetRecordset()

Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim sConn As String
Dim sSql As String
Dim sOutput As String



'ODBC;DSN=MS Access Database;DBQ=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;DefaultDir=C :\Program
Files\Microsoft Office\OFFICE11\SAMPLES;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;


sConn = "ODBC;DSN=MS Access Database;" & _
"DBQ=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"DefaultDir=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"


sSql = "SELECT CustomerID, CompanyName, Address, City, Region,
PostalCode" & _
" FROM Customers" & _
" WHERE (City='London')"


Set adoConn = New ADODB.Connection
adoConn.Open sConn

Set adoRs = New ADODB.Recordset
adoRs.Open Source:=sSql, _
ActiveConnection:=adoConn

If Not (adoRs.BOF Or adoRs.EOF) Then
adoRs.MoveFirst

Do While Not adoRs.EOF
sOutput = sOutput & adoRs.Fields(0).Value & "

adoRs.MoveNext
Loop


adoRs.Close
adoConn.Close


sOutput = Left(sOutput, Len(sOutput) - 1)
Else
sOutput = "Empty Recordset"
End If


Debug.Print sOutput


Set adoRs = Nothing
Set adoConn = Nothing

End Sub

==============================


However, the following error message keeps popping up every time I run
the above code:

"Run-time error 429
ActiveX component can't create object"

This error message results in the following line of code being
high-lighted:

"Set adoConn = New ADODB.Connection"

What am I doing to stop the Access data not being pulled through to the
Excel spreadsheet?

Any assistance given would be much appreciated.


Spardey
Brisbane, Australia