ADODB Connection Problem

[spardey]

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

 

[Jim Thomlinson]

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

 

[Fredrik Wahlgren]

In the VAB Macro editor, select Tools -> References and then click on
Microsoft ADO.

/Fredrik

 

[Vincent]

Try changing your sConn string to:

SQLTableFileName = "C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb"

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
SQLTableFileName & ";User Id=admin;Jet OLEDBatabase Password="""""

This connection string is the most basic and easiest to understand. The
connection object only needs four properties to connect to an Access
database.
1) Provider - this is always Microsoft.Jet.OLEDB.4.0
2) Data Source - name of your access filename
3) User Id - Admin unless you've secured your database
4) Jet OLEDBatabase Password = """" (translates to "" when inside of
a string) unless you've secured your database

 

[spardey]

Try changing your sConn string to:

SQLTableFileName = "C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb"

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
SQLTableFileName & ";User Id=admin;Jet OLEDBatabase Password="""""

This connection string is the most basic and easiest to understand. The
connection object only needs four properties to connect to an Access
database.
1) Provider - this is always Microsoft.Jet.OLEDB.4.0
2) Data Source - name of your access filename
3) User Id - Admin unless you've secured your database
4) Jet OLEDBatabase Password = """" (translates to "" when inside of
a string) unless you've secured your database

Hi Folks,

Thank you all for your advice - but unfortunately it still doesn't want
to work for me!

Yes, I had Referenced the VBA Project to "Microsoft ActiveX Data
Objects 2.8 Library" through VBA Tools - References.

Vincent, I have copied and inserted your suggested code.Still
highlights the same piece of code ("Set adoConn = New
ADODB.Connection") when it falls over.

In the " SQLTableFileName" we are only referring to the database of
Northwind.mbd ; do we need to go one step further and refer to the
Customer table as well (eg Northwind.mdb/Customer.tbl)? Or is the
database name sufficient?

When I run it with the Locals Window on, it shows a value of "Nothing"
for both adoConn and adoRs. Of course sOutput shows a value of "". Is
this of assistance?

Again, thanks folks.

Spardey

 

[Dick Kusleika]

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;"

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

Try removing 'ODBC;' from sConn and see if that doesn't do it.