ADODB Connection Problem

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

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

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

/Fredrik



"spardey" <[email protected]> skrev i meddelandet
news:[email protected]...
> 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
>

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 OLEDB:Database 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 OLEDB:Database Password = """" (translates to "" when inside of
a string) unless you've secured your database

Vincent wrote:
> 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 OLEDB:Database 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 OLEDB:Database 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

spardey wrote:
> 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.


--
**** Kusleika
MVP-Excel
www.dailydoseofexcel.com